simple formatting question

Analytics
Enthusiast

simple formatting question

How do I force Teradata to give me decimal values? I've tried a number of other FORMAT variations beyond the example below and I can't get the mantissa to display other than my "123.45" calculation.

TIA,
NMadson

Select emp_id,
NumAccts,
123.45/NumAccts as NewNum,
15/NumAccts as NewNum2,
15/NumAccts (FORMAT 'ZZ9.99') as NewNum3
From t_emp
Where emp_id In ('26',
'00000683422');

EMP_ID NumAccts NewNum NewNum2 NewNum3
26 37 3.34 0 0
4 REPLIES
Enthusiast

Re: simple formatting question

if you want to see a number in decimal format cast the number
cast(10 as decimal(5,2))
if you have divisions then
cast(15 as decimal(5,2))/NumAccts as NewNum2
this should display decimal values.

Select emp_id,
NumAccts,
123.45/NumAccts as NewNum,
cast(15 as decimal(5,2))/NumAccts as NewNum2,
15/NumAccts (FORMAT 'ZZ9.99') as NewNum3
From t_emp
Where emp_id In ('26',
'00000683422');
Enthusiast

Re: simple formatting question

Hi

The CAST works best but you can also use the FORMAT option as follows:

Select emp_id,
NumAccts,
123.45/NumAccts (decimal(6,4))as NewNum,
15/NumAccts (decimal(6,4))as NewNum2,
15/NumAccts (decimal(6,4)) as NewNum3
From t_emp
Where emp_id In ('26',
'00000683422');

Enthusiast

Re: simple formatting question

Hi,

Use cast it will work fine.

Select emp_id,
NumAccts,
123.45/NumAccts(decimal(10,2)) as NewNum,
15/NumAccts(decimal(10,2)) as NewNum2,
15/NumAccts(decimal(10,2)) as NewNum3
From t_emp
Where emp_id In ('26',
'00000683422');
Enthusiast

Re: simple formatting question

Hi,

Use cast it will work fine.

Select emp_id,
NumAccts,
123.45/NumAccts(decimal(10,2)) as NewNum,
15/NumAccts(decimal(10,2)) as NewNum2,
15/NumAccts(decimal(10,2)) as NewNum3
From t_emp
Where emp_id In ('26',
'00000683422');

Regds,
sachin