Solved

Oracle 10g SQL Loader Using TO_date need to set value to NULL if date is blank

Posted on 2008-10-13
10
7,405 Views
Last Modified: 2013-12-18
Oracle 10g SQL Loader with comma delimited file.  I can get the TO_DATE to work but I need to also incorporate NULL if the field is blank.  I saw examples on this site but when I tried it in the comma delimited file.  It fails.  
D_UPD        "TO_DATE(nullif(D_UPD,BLANKS),'MM/DD/YYYY HH24:MI')"
 

Works if I remove the nullif to handle blanks but some of my records have blank dates so I want to replace it will nulls
 
 
 

Record 1: Rejected - Error on table ILAP_026, column D_UPD.

ORA-00984: column not allowed here

Open in new window

0
Comment
Question by:pattisp1
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
Comment Utility

Try this:

-- etc --

D_UPD   DATE 'MM/DD/YYYY HH24:MI' NULLIF D_UDT = BLANKS,

-- etc --

Open in new window

0
 
LVL 1

Accepted Solution

by:
asksigh earned 250 total points
Comment Utility
Hey here,

I have used something very similar to Mike in the past.
...
 D_UPD                     DATE "MM/DD/YYYY HH24:MI" NULLIF (D_UPD=BLANKS),
...

Hope it helps.
Cheers
Asksigh
0
 

Author Comment

by:pattisp1
Comment Utility
Thank you!!  Testing right now!!
0
 

Author Comment

by:pattisp1
Comment Utility
These options didn't work for me.  I get the following error:
Record 1: Rejected - Error on table ILAP_026, column D_UPD.
ORA-01858: a non-numeric character was found where a numeric was expected

I think I left an important requirement out of my first question.  I believe I have to use the TO_DATE because my date field in the comma delimited file is of the format MM/DD/YYY HH24:MI and I want to load it into Oracle as a DATE type field.   Please let me know if this thinking is correct.

Thank you!!!
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
D_UPD "DECODE(TRIM(:D_UPD),NULL,NULL,TO_DATE(:D_UPD,'MM/DD/YYYY HH24:MI'))"
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:pattisp1
Comment Utility
I tried this solution but still I'm getting the error that a numeric was expected.

D_UPD                                NEXT     *   ,  O(") CHARACTER            
    SQL string for column : "DECODE(TRIM(:D_UPD),NULL,NULL,TO_DATE(:D_UPD,'MM/DD/YYYY HH24:MI'))"
SSA_RIC                              NEXT     *   ,  O(") CHARACTER            
DON                                  NEXT     *   ,  O(") CHARACTER            
DON_QTY                              NEXT     *   ,  O(") CHARACTER            
DON_STAT                             NEXT     *   ,  O(") CHARACTER            
DON_DATE                             NEXT     *   ,  O(") CHARACTER            
    SQL string for column : "TO_DATE(:DON_DATE, 'MM/DD/YYYY HH24:MI')"
DON_IPD                              NEXT     *   ,  O(") CHARACTER            
DON_ESD                              NEXT     *   ,  O(") CHARACTER            
    NULL if DON_ESD = BLANKS
ARFORGEN_IND                         NEXT     *   ,  O(") CHARACTER            
ITV_QTY                              NEXT     *   ,  O(") CHARACTER            
SHIP_TO                              NEXT     *   ,  O(") CHARACTER            
SHIP_FROM                            NEXT     *   ,  O(") CHARACTER            
LAST_STATUS                          NEXT     *   ,  O(") CHARACTER            
TAG_ID                               NEXT     *   ,  O(") CHARACTER            
D_TAG                                NEXT     *   ,  O(") CHARACTER            
    SQL string for column : "TO_DATE(:D_TAG,'MM/DD/YYYY HH24:MI')"
CUMUL_AGE                            NEXT     *   ,  O(") CHARACTER            
D_ARRIVAL                            NEXT     *   ,  O(") CHARACTER            
    SQL string for column : "TO_DATE(:D_ARRIVAL,'MM/DD/YYYY HH24:MI')"
LAST_KNOWN_LOCATION                  NEXT     *   ,  O(") CHARACTER            
ILAP_ID                                                   SEQUENCE (MAX, 1)

value used for ROWS parameter changed from 64 to 17
Record 1: Rejected - Error on table ILAP_026, column D_UPD.
ORA-01858: a non-numeric character was found where a numeric was expected


Table :
  0 Rows successfully loaded.
  1 Row not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
Comment Utility

This should have worked:
D_UPD   DATE 'MM/DD/YYYY HH24:MI' NULLIF D_UDT = BLANKS.

Post a sample of your data:

0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
Then you must have alpha or special characters in your data. If you're in a test environment, try loading the date fields into varchar datatypes then select where translate(datefield,'#0123456789/','#') is not null to find the problem data.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
Comment Utility

Another issue may be that you have to code your control file fields to EXACTLY match the order of the source file fields!

Check it out...
0
 
LVL 1

Expert Comment

by:asksigh
Comment Utility
Cheers - glad i could help.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now