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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

karanbikash@gmail.com - 09 Apr 2008 09:28 GMT

Hi ,

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

SELECT case when DECIMAL(AMT_CHEQUE,31,2) = 0.00 THEN
9999999999999.9999999
ELSE DECIMAL(AMT_CHEQUE,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

Reply to this Message

jefftyzzer - 09 Apr 2008 17:46 GMT

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

> Hi ,
>
[quoted text clipped - 17 lines]
> 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(DECIMAL(AMT_CHEQUE,31,2) AS CHAR(32)))
END AS AMT_CHEQUE
FROM
TABLE(VALUES(2)) T(AMT_CHEQUE);

AMT_CHEQUE
--------------------------------
00000000000000000000000000002.00

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

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

--Jeff

Reply to this Message