Format to the Currency Field

n_srikanth4 used Ask the Experts™
Hi Experts,

 Requirement is simple.  Format the decimal field to the Currency Field  as mentioned in the example below and then convert to varchar.

(example: $20,000.00)

DB2 value of A_LMT : decimal
SSRS report value of A_LMT : varchar

SQL Conditions:

     when ID_SYS_MTCHD like 'ACM%' AND DS_TAG_ISS_FEE = 'Limit does not match' then CAST(A_LMT AS varchar(14))  
   else 'N/A'
END as ACMValue

Please correct my code above  to required format and please help me with the solution.


Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Theo KouwenhovenApplication Consultant

Hi Sreekanth,

There isn't (afaik) any option that will do that including thousand separation. Depending on the platform DB2  is running, you can define that on table creation time.
The formatting can be done with substring and concatination, but that is not a real nice option I think.

Other option is an external defined function/procedure
I have no good help to offer; only a general opinion.

It can, of course, be done. It could be done within the statement (with a lot of messy code) and it could be done with external function/proc.

But I'm not at all sure that it should be done.

This simply doesn't seem to be a "database" kind of problem. It seems to be a "reporting" problem. It's not a problem of the data but of presentation of the data for human consumption.

Personally, I would not be looking at any SQL to handle this. I would be looking at whatever tool that does my reporting. Technically, I would write some simple programming for this. It might be some java code or VB or COBOL; it doesn't really matter. It just isn't "database" to me.

A solution in SQL sometimes makes things worse. (That's why not everything is done with SQL.)

Just an opinion.

Theo KouwenhovenApplication Consultant

Hi Sreekanth,

Just a question, why should it be converted to VarChar ?
and where in the CASE structure are these fields used?



Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial