Converting Data Types in DB2
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
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
good.
why don't you do them in the report itself?