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.
converting null string to date format
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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.
raj