asked on

SQL*Loader (9.2): Divide up a Date/Time field into separate columns?

I'm writting a Sql*Loader script and have one field that is a Date/Time field. An example of the date/time raw field looks like this:

2006-05-18 04:12:06.000000

I wish to load into seperate columns for Date and Time (and maybe drop the last 6 zeros). For example:

Date = 2006-05-18
Time = 04:12:06

Can this be done on the load or do I have to load "as is" to a Staging table and then do in SQL*Plus and load from there? Please provide the cmds necessary to accomplish.

ASKER CERTIFIED SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

globalwm

ASKER

Your example implies running SQL*Loader "as is" to a Staging table and then using the SQL above to "transform" the "date_col" into two columns?

So is there a way to do this in the SQL*Loader control file?

globalwm

ASKER

Here is my control file:

LOAD DATA
INFILE 'H:\SQL\RFID\LOAD.CSV'
INTO TABLE RFID_STAGE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
SGTIN CHAR NULLIF (SGTIN=BLANKS)
, ITMNO CHAR NULLIF (ITMNO=BLANKS)
, ITMDESC CHAR NULLIF (ITMDESC=BLANKS)
, UPC CHAR NULLIF (UPC=BLANKS)
, GTIN CHAR NULLIF (GTIN=BLANKS)
, EPCDATE DATE "TO_CHAR(:EPCDATE,'YYYY-MM-DD')"
, EPCTIME CHAR "TO_CHAR(:EPCDATE,'HH-MI-SS'')"
, STRNBR CHAR NULLIF (STRNBR=BLANKS)
, LOCCDE CHAR NULLIF (LOCCDE=BLANKS)
, LOCDSC CHAR NULLIF (LOCDSC=BLANKS)
, EPCSTAT CHAR NULLIF (EPCSTAT=BLANKS)
)

and I on the right track?

globalwm

ASKER

or using your SUBSTR method:

(
SGTIN CHAR NULLIF (SGTIN=BLANKS)
, ITMNO CHAR NULLIF (ITMNO=BLANKS)
, ITMDESC CHAR NULLIF (ITMDESC=BLANKS)
, UPC CHAR NULLIF (UPC=BLANKS)
, GTIN CHAR NULLIF (GTIN=BLANKS)
, EPCDATE DATE "SUBSTR(:EPCDATE, 1,10)"
, EPCTIME CHAR "SUBSTR(:EPCDATE, 12,8)"
, STRNBR CHAR NULLIF (STRNBR=BLANKS)
, LOCCDE CHAR NULLIF (LOCCDE=BLANKS)
, LOCDSC CHAR NULLIF (LOCDSC=BLANKS)
, EPCSTAT CHAR NULLIF (EPCSTAT=BLANKS)
)

??

I'll try this one out.

globalwm

ASKER

Table RFID_STAGE, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
SGTIN FIRST * , CHARACTER
NULL if SGTIN = BLANKS
ITMNO NEXT * , CHARACTER
NULL if ITMNO = BLANKS
ITMDESC NEXT * , CHARACTER
NULL if ITMDESC = BLANKS
UPC NEXT * , CHARACTER
NULL if UPC = BLANKS
GTIN NEXT * , CHARACTER
NULL if GTIN = BLANKS
EPCDATE NEXT * , CHARACTER
SQL string for column : "SUBSTR(:EPCDATE, 1,10)"
EPCTIME NEXT * , CHARACTER
SQL string for column : "SUBSTR(:EPCDATE, 12,8)"
STRNBR NEXT * , CHARACTER
NULL if STRNBR = BLANKS
LOCCDE NEXT * , CHARACTER
NULL if LOCCDE = BLANKS
LOCDSC NEXT * , CHARACTER
NULL if LOCDSC = BLANKS
EPCSTAT NEXT * , CHARACTER
NULL if EPCSTAT = BLANKS

Record 1: Rejected - Error on table RFID_STAGE, column EPCDATE.
ORA-01861: literal does not match format string

....

globalwm

ASKER

Figured it out myself.

Obsure fact: Calculated fields must be at the bottom of the control file.

Here's an example:

Re: SQL*LOAD - manipulating data [message #164064 is a reply to message
#164021 ] Tue, 21 March 2006 10:27
Barbara Boehmer
Messages: 3006
Registered: November 2002
Location: California, USA Senior Member

Columns in the control file need to be in the same order as values in the csv file. Any columns that are calculated based on other columns need to go at the bottom. You also need to include trailing nullcols, so that it does not stop looking for column definitions after the end of the physical record. I also added date in front of your date format.

load data
infile aa.csv
into table abc
fields terminated by "," optionally enclosed by '"'
trailing nullcols
(A,
C DATE 'DD/MON/YYYY HH24:MI',
E,
B "SUBSTR(:A,1,INSTR(:A,'/')-1)",
D "SUBSTR(:A,INSTR(:A,'/')+1)")

I simply moved my TIME field to the bottom and everything worked:

(
SGTIN,
ITMNO,
ITMDESC,
UPC,
GTIN,
EPCDATE "SUBSTR(:EPCDATE,1,10)",
STRNBR,
LOCCDE,
LOCDSC,
EPCSTAT,
EPCTIME "SUBSTR(:EPCDATE,12,8)"
)

Done deal...