Re: SQL Loader - Detecting blank fields using WHEN

steviehaston_at_hotmail.com (stevie) wrote in message news:<4092f6be.0406080132.54ca062c_at_posting.google.com>...
> I'm trying to load a simple table using SQLLDR under oracle 8.1.7.4
> which only loads records that have a specific blank field. I'm using
> the WHEN clause to detect these records but they are ignored since all
> records fail with "Discarded - failed all WHEN clauses".
>
> The command is sqlldr userid=abc/xyz control=ldrtest.ctl
>
> The control file is as follows:
>
> LOAD DATA
> INFILE *
> REPLACE
> INTO TABLE ldrtest
> WHEN FIELD3 = ''
> FIELDS TERMINATED BY ','
> TRAILING NULLCOLS
> (
> FIELD1,
> FIELD2,
> FIELD3
> )
> BEGINDATA
> 04A01,11
> 76301,13
> 76301,13,A
> 76301,13
> 79601,13
> 79601,14,034
> 79601,13,174
> 79601,13,314

I may be wrong, the way I understand this is that your comparing null value with '' value. In oracle if you want use the following example:

The where clause should read as follow

where nvl(FIELD3,'') = ''

This will replace all null values with the second nvl parameter same as

where nvl(field3,' ') = ' '

when field3 is null then field3 = ' '

I hope this helps you!

Curtis Received on Wed Jun 09 2004 - 14:27:57 CEST