asked on

DB2 SYNTAX

I have the following conversions in SQL and need to convert them to DB2 syntax. Can anyone lend a hand? Thx

COUNT(DISTINCT ISNULL(CONVERT(varchar(255),A.CN),'') + isnull(CONVERT(varchar(255),A.ID) , ''))
sum(CONVERT(money,B.BILL_AMNT)/CONVERT(money,100.0)),
sum(CONVERT(money,ISNULL(B.AMT_AFTR,'0'))/CONVERT(money,100.0))

momi_sabag

COUNT(DISTINCT coalesce(varchar(A.CN),'') || coalesce(varchar(A.ID) , ''))

sum(dec(B.BILL_AMNT)/dec(100.0)),

sum(dec(coaelsce(B.AMT_AFTR,0))/dec(100.0))

k_murli_krishna

In DB2:

COUNT(DISTINCT COALESCE(CAST(A.CN AS varchar(255)),'') + COALESCE(CAST(A.ID AS varchar(255)),'')

To convert these 2, apart from whatever is already illustrated above:

sum(CAST(money,B.BILL_AMNT)/CONVERT(money,100.0)),
sum(CONVERT(money,ISNULL(B.AMT_AFTR,'0'))/CONVERT(money,100.0))

Refer CURRENCY function in DB2 for the money data type:
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.sqlref/db2z_udf_currency.htm

earngreen

ASKER

I am getting the following error on COUNT(DISTINCT COALESCE(CAST(A.CN AS varchar(255)),'') + COALESCE(CAST(A.ID AS varchar(255)),'')

A value with data type "SYSIBM.INTEGER" cannot be CAST to type "SYSIBM.VARCHAR".

The following seemed to work if I take the coaelsce out.

sum(dec(B.BILL_AMNT)/dec(100.0)),

sum(dec(coaelsce(B.AMT_AFTR,0))/dec(100.0))

k_murli_krishna

Try COUNT(DISTINCT COALESCE(CHAR(A.CN),'') + COALESCE(CHAR(A.ID),''))

earngreen

ASKER

I get the following

The data type of an operand of an arithmetic function or operation "+" is not numeric.

ASKER CERTIFIED SOLUTION

momi_sabag

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

earngreen

ASKER

That worked. Thanks