sql loader - SQLLDR - problem with WHEN clauses -

i have multiple when clauses in control file, data loading in half of them satisfies when clauses , gets inserted desired table. other half arent (which expect) expecting data doesnt meet when conditions placed discard file there none created.

any ideas?

load data infile '/u04/app/vpht_app/flat_files/icr_load/marc/sqlldr_load/csso_ccrbscredentials_comsumer23062010160322.txt' badfile '/u04/app/vpht_app/flat_files/icr_load/marc/sqlldr_load/csso_ccrbscredentials_comsumer23062010160322.bad' discardfile '/u04/app/vpht_app/flat_files/icr_load/marc/sqlldr_load/csso_ccrbscredentials_comsumer23062010160322.dsc' insert  table "dcvpapp"."rbs_cc_customerinfo" insert fields terminated ',' trailing nullcols (cc_user_name position(24:73), accountid position(1:12), customerid position(14:22))  table "dcvpapp"."rbs_cc_securitydetails" when (481:481) = 'n' , (477:479) ='0' fields terminated ',' trailing nullcols ( cc_user_name position(24:73), rbspin position(75:274), rbspassword position(276:475), fill1 filler, fill2 filler, fill3 filler, fill4 filler, failcodecount position(477:479), failpasswordcount position(477:479) )  table "dcvpapp"."rbs_cc_securitydetails" when (481:481) = 'n' , (477:479) ='1' fields terminated ',' trailing nullcols ( cc_user_name position(24:73), rbspin position(75:274), rbspassword position(276:475), fill1 filler, fill2 filler, fill3 filler, fill4 filler, failcodecount position(477:479), failpasswordcount position(477:479) ) 

my table structure is:

create table rbs_cc_customerinfo ( cc_user_name varchar2(50), accountid varchar2(12) not null, customerid varchar2(9) not null, cust_migration_status varchar2(1) default 'n' not null, constraint pk_01 primary key (cc_user_name) );  create table rbs_cc_securitydetails ( cc_user_name varchar2(50), rbspin varchar2(200) not null, rbspassword varchar2(200) not null, failcodecount number (9) not null, failpasswordcount number (9) not null, constraint pk_secur foreign key (cc_user_name) references rbs_cc_customerinfo(cc_user_name) ) 

and sample data below( these have been right padded since these fixed fields) last record should discarded , placed in side discard file since doesnt meet of when clause conditions, no discard file created. have tried 1 when clause , discard file created,seems using more 1 table discard file isnt created.

 acc000000001,custid213,marc_vaf ,1234 ,pet ,0 ,n,n,full acc000000002,custid214,toby_123 ,1352 ,bailey ,1 ,y,n,full acc000000003,custid215,kevin_vaf81 ,yy33of ,water ,2 ,y,n,full acc000000015,custid227,sam_egd ,carry42 ,some password ,-3 ,y,n,full 

thanks

i used sql*loader on sample data, , found following in log file sql*loader left behind:

 table "dcvpapp"."rbs_cc_customerinfo":   4 rows loaded.         0 rows not loaded due data errors.   0 rows not loaded because when clauses failed.   0 rows not loaded because fields null.  table "dcvpapp"."rbs_cc_securitydetails":   0 rows loaded.   0 rows not loaded due data errors.   4 rows not loaded because when clauses failed.   0 rows not loaded because fields null.  table "dcvpapp"."rbs_cc_securitydetails":   0 rows loaded.   0 rows not loaded due data errors.   4 rows not loaded because when clauses failed.   0 rows not loaded because fields null. 

in first block, data loaded because there no when clauses fail. other two, rows failed when clauses. since first block loaded 4 rows, there nothing write discard file, sql*loader didn't create one.

the when clauses in second 2 blocks seem reference parts of data long way off end of sample data. both appear use data positions 477 onwards, whereas longest line in sample data 68 characters long. since each field has @ 1 trailing space, i'll assume sample data has somehow got mangled , there should many more spaces above.

anyway, commented out section of controlfile inserts rbs_cc_customerinfo, emptied tables , reran sql*loader. time, 4 rows written discard file.

if want data matches neither of 2 when clauses written discard file, how splitting controlfile 2 separate controlfiles, 1 loads data rbs_cc_customerinfo using first block, , 1 loads data rbs_cc_securitydetails using other 2 blocks?