Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2006-05-24
6
Medium Priority
?
1,026 Views
Last Modified: 2013-12-11
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.



0
Comment
Question by:globalwm
  • 5
6 Comments
 
LVL 48

Accepted Solution

by:
schwertner earned 900 total points
ID: 16760661
If it is date column you can extract date and time component:
TO_CHAR(date-col,'YYYY-MM-DD')
TO_CAHR(date_col,'HH-MI-SS')
If this is character column use substrings:
SUBSTR(date_col, 1,10)
SUBSTR(date_col, 12,8)
0
 
LVL 1

Author Comment

by:globalwm
ID: 16761300
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?
0
 
LVL 1

Author Comment

by:globalwm
ID: 16762395
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?
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 1

Author Comment

by:globalwm
ID: 16762424
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.
0
 
LVL 1

Author Comment

by:globalwm
ID: 16762439
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

....

0
 
LVL 1

Author Comment

by:globalwm
ID: 16770892
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...

0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question