Column Widths - Rajeevnandmishra!!!!

[ADW]

How do, thank you for help, here is the statement: -


Select product AS 'PRODUCT', total = count(all transaction_type), Quantity=sum(movement_quantity), warehouse AS 'WAREHOUSE' from strip
where warehouse = 'S1'
and transaction_type != 'ADJ'
and transaction_type != 'TRAN'
and transaction_type != 'SCRP'
and transaction_type != 'BINT'
and transaction_type != 'SRET'
and transaction_type != 'DESP'
group by product, warehouse
order by total

And below is an example of what I am getting returned. It is the quantity column that I need trimming! Cheers.

Quantity Warehouse
-----------------------------------------------
45 S1
12 S1
12
12
For example! Any Ideas

 

[rajeevnandanmishra]

Hi ADW,

What is the data type of your movement_quantity column. SQL always send back the result with maximum possible length of data column +1.
You can check this by putting the maximum value in movement_quantity column and then do a simple select query. It will return you the same length as it is returning now.

 

[tlbroadbent]

You can explicitely state the column width by converting to a character string. In the following example, I use tht STR function to set the length of the returned data to 8.

... Quantity=str(sum(movement_quantity), 8), ...

Remember the column width can be no smaller than the largest of the data length or the width of the column heading. If you want the quantity column width to be less than 8, you'll need a shorter column heading such as

Qty=str(sum(movement_quantity), 5).

Don't make the column too narrow or you will lose data.
Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes