Home » RDBMS Server » Server Administration » Formatting numerical data
Formatting numerical data [message #370250] Thu, 17 June 1999 13:10 Go to next message
Sarah
Messages: 15
Registered: June 1999
Junior Member
I want to display numeric data from one column with varying decimal places based on a field in the table containing a format code. This is what I'm trying to do but the if statement does not recognize table column:

SQL= " SELECT "
IF VAL_DSPL_FRMT_I = 0 THEN
SQL=SQL + "TO_CHAR(FCTR_VAL_I, 999) "
ELSEIF VAL_DSPL_FRMT = 1 THEN
SQL=SQL + "TO_CHAR(FCTR_VAL_I, 999.9) "
ELSEIF VAL_DSPL_FRMT = 2 THEN
SQL=SQL + "TO_CHAR(FCTR_VAL_I, 999.99) MONTH1 "
END IF

Does anyone know how to do this?
Re: Formatting numerical data [message #370257 is a reply to message #370250] Wed, 14 July 1999 11:21 Go to previous message
Chris Hunt
Messages: 27
Registered: March 1999
Junior Member
Try something like...

SELECT TO_CHAR(fctr_val_i,
DECODE(val_disp_fmt_i,0,'999',
1,'999.9',
2,'999.99'))
FROM my_table...

If val_disp_fmt_i is always the number of decimal places, you could use a construct like:

TO_CHAR(fctr_val_i,RTRIM('999.'||RPAD('9',val_disp_fmt_i,'9'),'.'))

to make it totally flexible.

Previous Topic: within a trigger, trying to select values into variables based on the trigger statement
Next Topic: Re: within a trigger, trying to select values into variables based on the trigger statement
Goto Forum:
  


Current Time: Wed Oct 28 07:20:44 CDT 2020