Converting a numeric to packed-decimal in Stored Procedure

Question

I am building a Native Stored Procedure.. where one of the field is derived by combining(concatenating) 5 values. 3 out of these 5 values are character fields and 2 are packed-decimal. I need to combine them into 1 field before I write into the table.

I am not sure on how can I convert any number to packed decimal (COMP-3 equivalent of COBOL) in a SP.

I tried DEC() function but its not giving expected results.

I'm using DB2 V11

Please let me know if there are any other methods..

Thanks..

Answer 1

with tmp as 
(
     select 
     case when 
     case when trim(ifnull(valchar1, ''))='' then '0' else trim(valchar1) end  ||
     case when trim(ifnull(valchar2, ''))='' then '0' else trim(valchar2) end  ||
     case when trim(ifnull(valchar3, ''))='' then '0' else trim(valchar3) end  ||
     valdec1 || valdec2 as NewVal
     from your table
)
select 
cast(case NewVal ='' then null else NewVal end as dec(15, 2))  as NewvvalDec 
from tmp