All Forums Database
nila 3 posts Joined 03/14
31 Mar 2014
Decimal and zeroes removal

Hi all,
 
I need to change this simple Teradate query but being new to this world not able to figure out why is it giving me error?
Some body please help.
I want my column as 20 instead of 20.00
TRANS_CNT as  CAST(TRANS_CNT AS DECIMAL(18,2) 
 
Thanks all

YouriD 9 posts Joined 01/14
01 Apr 2014

TRANS_CNT as  CAST(TRANS_CNT AS DECIMAL(18,2) 
should be
TRANS_CNT as  CAST(TRANS_CNT AS DECIMAL(18,0) 
the 2 in your example defines the number of decimals
what did you have in mind as max-value of trans_cnt ?
in most cases an INTEGER will do the job
 

nila 3 posts Joined 03/14
01 Apr 2014

I had tried (18,0) earlier.
However, INTEGER works fine.
Many Thanks!
 

Adharssh 36 posts Joined 08/13
01 Apr 2014

Hi Nila,
The Above query will not result in error instead it will not give you the desired result without the zeros.

SEL CAST(TRANS_CNT as DECIMAL(18,0));

OR

SEL CAST(TRANS_CNT as INTEGER);

OR

SEL SUBSTR(CAST(TRANS_CNT as DECIMAL(18,2)),1, 21-2);

Thanks & Regards,
Adharssh.

Share the Knowledge. Feel the Happiness, When you share/Teach it.

nila 3 posts Joined 03/14
01 Apr 2014

Thank You!
I appriciate your detailed answer and option3 works best.
 

You must sign in to leave a comment.