sqlldr control file WHEN clause--how to achieve LIKE '%'
427941Jul 25 2008 — edited Jul 30 2008Hi all,
I'm on 10g and trying to exclude some records during a sqlldr call. So I turn to the WHEN clause and find out that is pretty limited. I cannot use functions in it and I cannot use the LIKE operator with it. Here is a sample of what i'm trying to do:
LOAD DATA
INFILE *
INTO TABLE vc_t
WHEN account_name like '%ONE'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
ACCOUNT_NAME,
TYPE ,
AMOUNT
)
BEGINDATA
"ACCOUNT_ONE", "T", 1.23
"ACCOUNT_TWO", "T", 1.24
"ACCOUNT_THREE", "T", 1.25
"ACCOUNT_FOR", "T", 1.26
"ACCOUNT_ONE", "T", 1.27
"ACCOUNT_SIX", "T", 1.28
Of course this is only a test because in real life the data will not look exactly like this and the prefix will not always begin with "ACCOUNT". it can be anything. But the WHEN clause is key and it doesn't work. Please help!
Thanks,
Piyush