Converting data types

[JDionne]

I have a custom SQL statment that I have to run to get data to use to update a reference table. Because of my database I have to convert an int into a char before I can do the substring command.
My code

Code: Select all

select d.direction_code
, f.Direction_Id
, substr(char(MAX(vdate_Month_Id)),5,2)
, SUBSTR(CHAR(MAX(vdate_Month_Id)),1,4)
, max(vdate_Month_Id)-100
, max(f.update_date)
from db2admin.DW_JOC_ALL_DATA_TBL f, db2admin.REF_DIRECTION_LU_TBL d
where f.direction_id = d.direction_id
group by d.direction_code
, f.Direction_Id

The problem is that the destination table values need to be intigers not char. Whay dirivation would I use to convert a char into an int?
Jim

[chulett]

Just do math on it and DataStage will "convert" it to a number. Fastest thing seems to be to add 0 to it, or you could multiply it by 1. Anything that doesn't change the value.