convert decimal to display 0.00 as '######.##'

[karanbikash@gmail.com]

Hi ,

I have a requirement where in I need to display the decimal value of
0.00 as '######.00'

SELECT case when DECIMAL(AMT_CHE QUE,31,2) = 0.00 THEN
9999999999999.9 999999
ELSE DECIMAL(AMT_CHE QUE,31,2)
END AS AMT_CHEQUE
FROM DB2ADMIN.RPT_BA _PADC161

I dont want to display 9999999999.9999 rather I want some values
like '#########.00 '
to be displayed .

Can u please help .
I encounter error ...saying that final result is not compatiable . Is
it possible to convert the above .

Regards
Bikash

[jefftyzzer]

Re: convert decimal to display 0.00 as '######.##'

On Apr 9, 1:28 am, karanbik...@gma il.com wrote:

Hi ,
>
I have a requirement where in I need to display the decimal value of
0.00 as '######.00'
>
SELECT case when DECIMAL(AMT_CHE QUE,31,2) = 0.00 THEN
9999999999999.9 999999
ELSE DECIMAL(AMT_CHE QUE,31,2)
END AS AMT_CHEQUE
FROM DB2ADMIN.RPT_BA _PADC161
>
I dont want to display 9999999999.9999 rather I want some values
like '#########.00 '
to be displayed .
>
Can u please help .
I encounter error ...saying that final result is not compatiable . Is
it possible to convert the above .
>
Regards
Bikash

It's an issue of data-type compatibility. All of the attributes
evaluated in the CASE statement must be "compatible ," and yours
aren't: two are decimal and one is a string. See if this query gets
you farther along:

SELECT
CASE
WHEN
RTRIM(CAST(AMT_ CHEQUE AS CHAR(32))) = '0'
THEN
'######.9999999 '
ELSE
RTRIM(CAST(DECI MAL(AMT_CHEQUE, 31,2) AS CHAR(32)))
END AS AMT_CHEQUE
FROM
TABLE(VALUES(2) ) T(AMT_CHEQUE);

AMT_CHEQUE
--------------------------------
000000000000000 00000000000002. 00

SELECT
CASE
WHEN
RTRIM(CAST(AMT_ CHEQUE AS CHAR(32))) = '0'
THEN
'######.9999999 '
ELSE
RTRIM(CAST(DECI MAL(AMT_CHEQUE, 31,2) AS CHAR(32)))
END AS AMT_CHEQUE
FROM
TABLE(VALUES(0) ) T(AMT_CHEQUE)

AMT_CHEQUE
--------------------------------
######.9999999

--Jeff