Set Column Length? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Set Column Length?

Author

Topic

plawrenz
Starting Member

15 Posts

Posted - 2007-11-14 : 14:57:25

I need to set a data length for a computed field, the field is clos_cntract_price right now it is coming out as 23 (248.3375166000000000000) long and I need it to be 13 (248.337516000) How do I do this?

select a.portfolio_id,
a.transaction_code,
a.trade_date,
b.num_of_contracts,
a.exec_trade_number,
b.purchs_trde_number,
case
when (a.RATE_CONV_CODE = '1' and a.trans_currency = 'USD') then b.clos_cntract_price
when (a.RATE_CONV_CODE = '0' and a.trans_currency = 'USD') then b.clos_cntract_price
when (a.RATE_CONV_CODE = '0' and a.trans_currency <> 'USD') then (b.clos_cntract_price/c.TRADE_EXCHANGE_RT)
when (a.RATE_CONV_CODE = '1' and a.trans_currency <> 'USD') then (b.clos_cntract_price*c.TRADE_EXCHANGE_RT)
else null end as clos_cntract_price,
b.commission_amount
from pfanml.tnn a, nml.ib4 b, pfanml.tnn c
where a.process_date = '9/18/2007' and
a.transaction_code = '286' and
a.portfolio_id = b.portfolio_id and
a.portfolio_id = c.portfolio_id and
a.exec_trade_number = b.exec_trade_number and
b.purchs_trde_number = c.exec_trade_number and
(a.portfolio_id = '55055099');

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-14 : 15:01:52

If you just want to truncate the characters, then you can use SUBSTRING or LEFT functions. If you instead want to round it, then you'll need to use the ROUND function.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

plawrenz
Starting Member

15 Posts

Posted - 2007-11-14 : 15:09:21

I am having issues with the syntax since it is in a case statement and I can't seem to get round or cast to work. Please help!

georgev
Posting Yak Master

122 Posts

Posted - 2007-11-15 : 05:03:26

Looks like you'd have to wrap the entire CASE statement in your Cast/Convert/Round function.
I wouldn't suggest using string truncation, as we shouldn't be performing calculations on text columns in the first place!

If you still can't get it to work post the case statement that you've tried and someone can look in to it :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources