OraFAQ Forum: Server Utilities » SQL Loader WHEN clause with NULL Values

Home » RDBMS Server » Server Utilities » SQL Loader WHEN clause with NULL Values

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Loader WHEN clause with NULL Values [message #638854]

Tue, 23 June 2015 09:23

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.

Report message to a moderator

Re: SQL Loader WHEN clause with NULL Values [message #638855 is a reply to message #638854]

Tue, 23 June 2015 09:28

BlackSwan
Messages: 26766
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"?

Report message to a moderator

Re: SQL Loader WHEN clause with NULL Values [message #638856 is a reply to message #638855]

Tue, 23 June 2015 09:35

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.

Report message to a moderator

Re: SQL Loader WHEN clause with NULL Values [message #638862 is a reply to message #638856]

Tue, 23 June 2015 13:47

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: SQL Loader WHEN clause with NULL Values [message #638864 is a reply to message #638862]

Tue, 23 June 2015 17:24

Barbara Boehmer
Messages: 9104
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.

Report message to a moderator

Re: SQL Loader WHEN clause with NULL Values [message #638868 is a reply to message #638864]

Tue, 23 June 2015 23:35

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..

Report message to a moderator

Re: SQL Loader WHEN clause with NULL Values [message #638873 is a reply to message #638864]

Wed, 24 June 2015 00:54

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: SQL Loader WHEN clause with NULL Values [message #638874 is a reply to message #638868]

Wed, 24 June 2015 00:56

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Feb 07 01:03:14 CST 2025