SQL Loader Control file question 1

[AlStl]

Hi there, Little help needed. I am dealing with a pipe delimited file ex:

Transaction|Place|RDATE|Type
Manual|Chicago|1986-07-14 00:00:00.000|Failed
Manual|NULL|NULL|Failed

My control file look like this:

Fields terminated by '|' optionally enclosed by '"'

{

Transaction,
Place NULLIF Place='NULL'),
RDATE "To_DATE RDATE, 'YYYY-MM-DD HH24:MI:SS') NULLIF RDATE='NULL')",
TYPE
)

For some reason SQL loader is not liking this line:

RDATE "To_DATE RDATE, 'YYYY-MM-DD HH24:MI:SS') NULLIF RDATE='NULL')"

I am getting error:
Record 1: Rejected - Error on table XXX, column RDATE

How can I handle date conversion (mind this data file is from SQL server) and possible string 'NULL' values for same data element i.e. RDATE. In other words I can receive string 'NULL' or DATE as shown above in RDATE field.

Thx

Al

 

[Dagon]

You have to apply a single SQL function to the column, whereas you are attempting to apply two. You will have to combine it together using either a DECODE or CASE e.g.

DECODERDATE, 'NULL', to_date(null), TO_DATERDATE, 'YYYY-MM-DD HH24:MI:SS'))

For Oracle-related work, contact me through Linked-In.

 

[AlStl]

Dagon,

Worked like a charm. I really appreciate it.

Al

 

[SantaMufasa]

Al,

Be sure to award a

to Dagon for his helpful post.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[AlStl]

SM, Done !!