i have been trying to load some data from a csv file based on certain conditions and i am restricted by the use of "OR" control in when clause (Oracle ctl) the data loads for the first 11 conditions but does not loads any data after the first 11 condition
following is the code sample
create table t1 (
c1 varchar(10),
c2 varchar(10),
c3 varchar(10)
)
----------dataloader-----------
options(errors=20000)
LOAD DATA
INFILE 'D:\Scripts\abc.csv'
BADFILE 'D:\Scripts\abc.BAD'
DISCARDFILE 'D:\Scripts\abc.DIS'
DISCARDMAX 20000
insert
INTO TABLE t1
when c1='James'
FIELDS TERMINATED BY "," optionally enclosed by '"'
Trailing NULLCOLS
(
c1,
c2,
c3
)
INTO TABLE t1
when c1='John'
FIELDS TERMINATED BY "," optionally enclosed by '"'
Trailing NULLCOLS
(
c1 POSITION(1),
c2,
c3
)
INTO TABLE t1
when c1='Sam'
FIELDS TERMINATED BY "," optionally enclosed by '"'
Trailing NULLCOLS
(
c1 POSITION(2),
c2,
c3
)
.
.
.
INTO TABLE t1
when c1='Mark'
FIELDS TERMINATED BY "," optionally enclosed by '"'
Trailing NULLCOLS
(
c1 POSITION(12),
c2,
c3
)
--------------dataloader execution-----
sqlldr scott/tiger direct=true control='filepath'
for executing each and every condition we need to place POSITION(1) till POSITION(9) tag for each and every condition. otherwise the ctl file doesnot reads the tagging and ignores the condition