• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1100
  • Last Modified:

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))
0
earngreen
Asked:
earngreen
  • 3
  • 2
  • 2
1 Solution
 
momi_sabagCommented:
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))
0
 
k_murli_krishnaCommented:
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

0
 
earngreenAuthor Commented:
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))
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
k_murli_krishnaCommented:
Try COUNT(DISTINCT COALESCE(CHAR(A.CN),'') + COALESCE(CHAR(A.ID),''))
0
 
earngreenAuthor Commented:
I get the following

The data type of an operand of an arithmetic function or operation "+" is not numeric.
0
 
momi_sabagCommented:
COUNT(DISTINCT COALESCE(CHAR(A.CN),'') || COALESCE(CHAR(A.ID),''))
0
 
earngreenAuthor Commented:
That  worked. Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now