Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2008-10-13
10
Medium Priority
?
7,780 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 22706517

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 1000 total points
ID: 22709319
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
ID: 22711801
Thank you!!  Testing right now!!
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:pattisp1
ID: 22711915
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 32

Expert Comment

by:awking00
ID: 22713463
D_UPD "DECODE(TRIM(:D_UPD),NULL,NULL,TO_DATE(:D_UPD,'MM/DD/YYYY HH24:MI'))"
0
 

Author Comment

by:pattisp1
ID: 22723753
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
ID: 22724077

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

Post a sample of your data:

0
 
LVL 32

Expert Comment

by:awking00
ID: 22725156
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
ID: 22727736

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
ID: 22765117
Cheers - glad i could help.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

597 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