Blanks/Null Values in the date field

[Amit_111]

Hi All,

My source data contains date in the format "20060101" which I convert and insert into the target table using the following functions:

Oconv(Iconv(InLink.TheDate,"DYMD"),"D-YMD[4,2,2]") : " 00:00:00"

as suggested in another thread some days back.

This is working fine in all the jobs except One where the source column contains Blank/Nulls in the date column. I have to default the date to "99991231" for which I modified the above function and used the following function in the column derivation in a transformer just before insering in the target table :

If (IsNull(Trim(Link.Column)) Or Link.Column= 0 )

Then Oconv(Iconv('99991231', "DYMD"),"D-YMD[4,2,2]") : "00:00:00"

Else Oconv(Iconv(Link.Column, "DYMD"),"D-YMD[4,2,2]") : " 00:00:00"

But its not working, the Trim function is also not workin and all data is rejected giving the following Warning message " ORA-01843: not a valid month". what should I do to handle these blanks and Nulls so that I can insert the default date instead of these blanks.

Any one with any ideas Please help.
Thanks in Advance.

[suresh.narasimha]

Hi Amit,

Try this ...

If Len(Trim(Link.Column))=0 Then

Then Oconv(Iconv('99991231', "DYMD"),"D-YMD[4,2,2]") : "00:00:00"

Else Oconv(Iconv(Link.Column, "DYMD"),"D-YMD[4,2,2]") : " 00:00:00"

Regards,
Suresh N

[rafik2k]

If the target field is nullable then you can use @NULL

Code: Select all

If Len(Trim(Link.Column))=0 Then 

Then [b]@NULL[/b]

Else Oconv(Iconv(Link.Column, "DYMD"),"D-YMD[4,2,2]") : " 00:00:00" 

[Amit_111]

Heyyy Guys,

I have got the solution for this problem :D . Following is the code which worked :

If (IsNull(Link.Column) Or Link.Column= 0 Or Trim(Link.Column," ","A")="")
Then Oconv(Iconv('99991231',"DYMD"),"D-YMD[4,2,2]") : " 00:00:00"
Else Oconv(Iconv(Link.Column, "DYMD"),"D-YMD[4,2,2]") : " 00:00:00"

I think the problem was because NULL and "" are not considered same by Datastage so had to write two different conditions to handle them.

[chulett]

I think the problem was because NULL and "" are not considered same by Datastage

That would be because they are not the same - and not just as far as DataStage is concerned. The former is an unknown value and the latter is a known, empty value. Hence the need for two checks when both conditions are a possibility.

[ray.wurlod]

Trim(Link.Column," ","A")="" can never be true unless the string contains no internal spaces. Trim() never removes the last of any set of internal spaces. Perhaps Trim(Link.Column," ","A")<" " would be a better formulation.

[Amit_111]

I didn't get your point.
you said

Trim() never removes the last of any set of internal spaces.

But Trim(Link.Column," ","A")="" can removes the internal spaces right???......Thats the reason I used it in my Jobs.....
If not Please explain.
Thanks in advance.

[ray.wurlod]

Read the help on Trim. Particularly the phrase reduces multiple occurrences to a single occurrence

[satya99]

I didn't get your point.
you said

Trim() never removes the last of any set of internal spaces.

But Trim(Link.Column," ","A")="" can removes the internal spaces right???......Thats the reason I used it in my Jobs.....
If not Please explain.
Thanks in advance.

If ISNULL(Trim(Link.Column)) OR TRIM(Link.Column) = ''

Then Oconv(Iconv('99991231', "DYMD"),"D-YMD[4,2,2]") : "00:00:00"

Else Oconv(Iconv(Link.Column, "DYMD"),"D-YMD[4,2,2]") : " 00:00:00"

[pradeepleon]

Then Oconv(Iconv('99991231', "DYMD"),"D-YMD[4,2,2]") : "00:00:00"

If you know how the target date format should be (in case of nulls and blanks)why do you want to use Oconv and Iconv functions.just hardcode it
like "9999-12-31"