We help IT Professionals succeed at work.

Converting Data Types in DB2

n_srikanth4
n_srikanth4 used Ask the Experts™
on
Hi Experts,

I am using DB2 AND  SSRS 2005 (for reporting )

Example  values:
1. R_INT_ACM to be displayed in report : 3.50% (data base value is decimal(7))

Formatting reqd: decimal precision of 2 , followed by %

2. A_LMT  to be displayed in report : $100,000.00  (data base value is decimal(13))

Formatting reqd: Format to Currency Field  as shown above with comma seperation and decimal precision and then finally convert to varchar as the field "ACMValue" is of type varchar

SQl Conditions:

CASE
    when ID_SYS_MTCHD like '%ACM' AND DS_TAG_ISS_FEE = 'Interest Rate Type does not match' then CAST(R_INT_ACM AS varchar(13))
    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 convert my above sql to the required format as mentioned above and help me with the query.


Regards,

Sreekanth
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
why do you try to do those conversions in the sql query?
why don't you do them in the report itself?

Author

Commented:
Hi momi_sabag,

   Good question , I wish i could do it at report end . But the problem is Report Column "ACM" is of type varchar,  as the value will be dynamically derived based on the Error Message and the Matching System (For example, I have to display "N/A" when none of the conditions satisy, so I am forced to set the data type to varchar).  I understand that I can do the Currency Format (or) format  to display the % value if the data type of the column is Numeric.  I think that I cannot do this conversion if the data type is of varchar.

So I am trying to achieve the same at the back end and display the result at the front end(Report).  

Please help me with the solution.

Regards,

Sreekanth.
Example  values:
1. R_INT_ACM to be displayed in report : 3.50% (data base value is decimal(7))
I don't quite understand the example. How do you intend to get the fractional portion ('.50') when the database definition is DECIMAL(7)? If there is no fractional portion in the database, how do you intend to get it for your report?

What exactly provides the "3.50" portion of the value?

Tom

Author

Commented:
Tom,  

The data base value for "R_INT_ACM" is Decimal(7,2) and for the other one "A_LMT"  is Decimal(13,2).

Regards,

Sreekanth.

Author

Commented:
good.