Format and Covert Data Type in DB2

n_srikanth4
n_srikanth4 used Ask the Experts™
on
Hi Experts,

      Here the Data type is Decimal(data base) and is of type varchar( in report) . There are two conditions defined in the code below ( Case Stmt)  for the report column "ACM" which has to be derived dynamically based on the conditions below .

I am unable to achieve this at report end as the data type is varchar and I want to achieve this only at the back end.

 1st condition : "R_INT_ACM"  should  format and  display  it as %  (ex: 1.50%)
 2nd Condition: "A_LMT" should convert to Currency format (comma seperated) and display it as (ex: $20,000.00)

Please Note: After Converting to the required format , both the conditions below should be converted to 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 correct my code to required format and please help me with the solution.

Regards,

Sreekanth.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Sreekanth

There is no built in function in db2 that perform these conversions
you will have to implement them yourself using a user defined function if you want it to be on the database side

Author

Commented:
Hi Experts,

   Could you please provide me the User defined Function to format to the  required data type and then finally convert to varchar as mentioned in the above comment.

Regards,

Sreekanth.

Author

Commented:
Hi Experts,

              Please provide me the custom code if you can please ? Will appreciate your inputs.

Regards,

Sreekanth.

Author

Commented:
good

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