Home » RDBMS Server » Server Utilities » SQL Loader WHEN clause with NULL Values
SQL Loader WHEN clause with NULL Values [message #638854] Tue, 23 June 2015 09:23 Go to next message
agarwalnidhi3008
Messages: 3
Registered: June 2015
Location: India
Junior Member
Hi All,

I am facing issue with loading data through sql loader.
I have a column for which we receive mutliple values as well as NULL
for exmple col1 values NULL,0000,00001,00002,00003
My control file looks like

LOAD DATA
INTo TABLE abc
APPEND
WHEN col1 <> 0003
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
col2
col1 "NVL(:col1,'0000')"
)

in my data file for col1 ,all values are NULL.. then above condition does not work.
Please let me know how can I handle NULL case in WHEN Clause.

I tried WHEN COl1= BLANKS--it is also not working.

Thanks in advance.
Re: SQL Loader WHEN clause with NULL Values [message #638855 is a reply to message #638854] Tue, 23 June 2015 09:28 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

are you saying the the input data file actually contains the 4 character string "NULL"?
Re: SQL Loader WHEN clause with NULL Values [message #638856 is a reply to message #638855] Tue, 23 June 2015 09:35 Go to previous messageGo to next message
agarwalnidhi3008
Messages: 3
Registered: June 2015
Location: India
Junior Member
no its not contain 4 character string NULL..
for example data file
1|
2|0000
3|
4|0003
5|

I dont want to process data with 0003..But WHEN clause rejects all records.
Re: SQL Loader WHEN clause with NULL Values [message #638862 is a reply to message #638856] Tue, 23 June 2015 13:47 Go to previous messageGo to next message
Littlefoot
Messages: 21505
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I spent some time trying to solve the problem using SQL*Loader only, but I failed.

Normally, I'd put it as
when col1 <> '0003' and col1 = ''
but it causes all records to be discarded as WHEN clauses failed. It seems that WHEN clause can't check whether a column equals NULL or '' (two single quotes) or BLANKS. On the other hand, you can use
when col1 <> ''
(which causes 0000 and 0003 records to be loaded, but that's not what we are looking for).

I tried different options (such as PRESERVE BLANKS) but none of them helped.

Therefore, my suggestion is to
  • load all records into a staging table and - using SQL (INSERT INTO) insert only required records into the target table, or
  • use external tables feature which, basically, skips the first part of the previous suggestion and enables you to directly INSERT records you want

Finally, I hope Barbara (Boehmer) will see this question and provide a solution. I'm really interested in seeing it.
Re: SQL Loader WHEN clause with NULL Values [message #638864 is a reply to message #638862] Tue, 23 June 2015 17:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
I also did some experimenting and can't come up with any better idea that using a staging or external table, then inserting from that.
Re: SQL Loader WHEN clause with NULL Values [message #638868 is a reply to message #638864] Tue, 23 June 2015 23:35 Go to previous messageGo to next message
agarwalnidhi3008
Messages: 3
Registered: June 2015
Location: India
Junior Member
Hi All...Great thanks for all suggested solutions..I think I need to go with staging table suggestion only..
Re: SQL Loader WHEN clause with NULL Values [message #638873 is a reply to message #638864] Wed, 24 June 2015 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 66641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Barbara Boehmer wrote on Wed, 24 June 2015 00:24
I also did some experimenting and can't come up with any better idea that using a staging or external table, then inserting from that.


I'm disappointed, I tried several things and found no way so I thought "wait a little bit Barbara will come with a neat solution", nope. Sad

Re: SQL Loader WHEN clause with NULL Values [message #638874 is a reply to message #638868] Wed, 24 June 2015 00:56 Go to previous message
Michel Cadot
Messages: 66641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

agarwalnidhi3008 wrote on Wed, 24 June 2015 06:35
Hi All...Great thanks for all suggested solutions..I think I need to go with staging table suggestion only..


Investigate external table, you then don't need to store the data you don't want and it is as fast as SQL*Loader if you could do it.
The drawback is that the file must reside on or be accessible (through NFS like way) from the database server.

Previous Topic: Deploying parts of the DB including users
Next Topic: Datapump Import ORA-01555
Goto Forum:
  


Current Time: Thu Oct 17 00:49:19 CDT 2019