converting null string to date format

nrsreddy · Post by **nrsreddy** » Mon May 21, 2007 5:09 am

I have a scinario where I want to convert string to date.
the problem is if I get null value in string it is droping that record.
If I take a variable port date datatype, and converting all notnull strings to date and else taking variable port. At that time it is outputting 01/01/2001 in null's position.

My Target table is oracle.

And I need Null string to load as Null date only.

ray.wurlod · Post by **ray.wurlod** » Mon May 21, 2007 5:44 am

Welcome aboard.

Read the section about null handling in the Parallel Job Developer's Guide - in particular the difference between in-band and out-of-band nulls. This should make it clear what you need to do.

nrsreddy · Post by **nrsreddy** » Tue May 22, 2007 1:30 am

Hi ray
I have gone through the null handling in the Parallel Job Developer's Guide and had a look into those functions but still i had the problem of passing the hardcoded null to date field.

If my question is wrong plz rectify me...

ray.wurlod · Post by **ray.wurlod** » Tue May 22, 2007 1:37 am

If you are using a Transformer stage specify SetNull() as the column derivation where you need null. If you are using a Modify stage use make_null() function. You will have to figure out what an out-of-band null for a date field looks like if you use the Modify stage.

nrsreddy · Post by **nrsreddy** » Tue May 22, 2007 3:23 am

Yes ray
following is my code in the transformation
If IsNotNull(VALUE_DATE) Then StringToDate(VALUE_DATE,"%yyyy%mm%dd"):'-00.00.00.000000' Else
SetNull()

this is working fine if the target is a dataset
where as my target is relational here it is droping the null records..

mallick · Post by **mallick** » Tue May 22, 2007 3:53 am

Instead of passing the records directly to table place a seq.file take the data to file and then from seq.file pass an output link to target table to insert records.

This may solve your problem.

nrsreddy · Post by **nrsreddy** » Tue May 22, 2007 4:40 am

mallick after trying your method

Still having the same problem droping the null records.
These are the messages that I am getting when ever a null is trying to load in Date format field

1)Oracle_Enterprise_17,0: The system(sqlldr test_abb/test_abb@EFDWDVY CONTROL=ora.594072.450364.0.ctl LOG=ora.594072.450364.0.log BAD=ora.594072.450364.0.log.bad SILENT=header PARFILE=ora.594072.450364.0.par) failed; see the log file for the Oracle specific message.
2)Oracle_Enterprise_17,0: The runLocally() of the operator failed.
3)Oracle_Enterprise_17,0: Operator terminated abnormally: runLocally did not return APT_StatusOk
4)main_program: Step execution finished with status = FAILED.

DSguru2B · Post by **DSguru2B** » Tue May 22, 2007 7:13 am

Is that particular column nullable in the table?
Also look at the .bad file for oracle error message.