SQL Loader - Detecting blank fields using WHEN

[stevie]

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

[Daniel Morgan]

stevie wrote:
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

What third column ... I only see a single comma
Fields terminated by comma ... take a look at your data

--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

[Curtis]

steviehaston@hotmail.com (stevie) wrote in message news:<4092f6be.0406080132.54ca062c@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

[stevie]

Daniel Morgan <damorgan@x.washington.edu> wrote in message news:<1086757894.142997@yasure>... stevie wrote:

<snip>
BEGINDATA 04A01,11 76301,13 76301,13,A 76301,13 79601,13 79601,14,034 79601,13,174 79601,13,314 What third column ... I only see a single comma Fields terminated by comma ... take a look at your data

The rows of data may have 2 or 3 columns as you can see. I need to
detect whether the row is a 2col or 3col row. That is why the control
file has a WHEN FIELD3=''. I tried WHEN FIELD3 IS NULL but SQLLDR
rejected this syntax.
Even if I imply that there is a 3rd col, by changing the first data
row to '04A01,11,' this has no impact and the row is still ignored.
Stevie