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,582 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 250 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create Index on a Materialized View 5 47
Oracle - Query link database loop 8 54
Function to return one result based on data in first query 11 54
Read XML values 8 56
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
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

749 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