Decode function

[srini.dw]

Hi,

DECODE(job.empl_status, 'D', TO_CHAR(NVL(pers.dt_of_death,job.effdt),'MMDDYYYY') ) Descease_Date,

Iam supposed to do the above function in datastage, plz let me know if it is correct or not which is shown below?? Iam doing this in transformer

If job.empl_status < > 'D' Then
NULL
ElsIf job.empl_status = 'D' then
pers.dt_of_death
ElsIf job.empl_status Isnull then
job.effdt
End if

Thanks

[ray.wurlod]

Check for null first. There is no ElsIf and there is no End If. Generate null using the SetNull() function - there is no "NULL constant".

Code: Select all

If IsNull(job.empl_status) Then job.effdt Else If job.empl_status = 'D' Then pers.dt_of_death Else SetNull()

[sudhakar_viswa]

Hi,

If IsNull(job.empl_status) Then
SetNull()
Else If job.empl_status="D" Then
If IsNull(pers.dt_of_death) Then
DateToString(job.effdt,"MMDDYYYY")
Else DateToString(pers.dt_of_death,"MMDDYYYY")
Else SetNull()

If job and pers are not link names then you need to add the link names for all the columns specified above with "." seperator.

Regards,
-- sudhakar

[sudhakar_viswa]

Hi,

I forgot to specify % in date format.

If IsNull(job.empl_status) Then
SetNull()
Else If job.empl_status="D" Then
If IsNull(pers.dt_of_death) Then
DateToString(job.effdt,"%mm%dd%yyyy")
Else DateToString(pers.dt_of_death,"%mm%dd%yyyy")
Else SetNull()

Regards,
-- sudhakar

[ray.wurlod]

Why DateToString()? There's nothing in the (implied) original specification that specifies a string type to be output - everything there suggests that the output column should be of type Date.

[us1aslam1us]

He might have got confused with the "TO_CHAR" mask.