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

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,869 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
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 

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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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 …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…
Suggested Courses
Course of the Month13 days, 23 hours left to enroll

580 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