Hello,
I am trying to use sqlldr to load a data file which contains 6 data fields, w/ ',' as a field delimiter, to a 6-column table only if the first two characters of field #3 <> 'UB'. Each of the 6 input fields should correspond to each column of the table except for the third input field which should load only the 3rd digit of the field to Column #3 of the table. After several tries, I kept receiving the following error message:
ORA-00917: missing comma
Here is my control card file without specified condition which load my input file successfully:
options (skip=1)
load data infile '$HOME/input_file.csv' discardfile 'in_File.dsc'
replace
into table Test_TAB
fields terminated by ',' optionally enclosed by '"' trailing nullcols
(mjr_prog_cd filler,
iris_prog_cd, phase_cd "substr(:phase_cd, 3,1)",
curr_bud_am, enc_am, actu_exp_am)
Here is my control card file for loading data to a table only if the first two characters of field #3 <> 'UB':
options (skip=1)
load data infile '$HOME/irismrs/DOTMRS.IRISFIN.MRS1_T.csv' discardfile 'iris_T.dsc'
replace
into table staging_iris_T
fields terminated by ',' optionally enclosed by '"' trailing nullcols
(mjr_prog_cd filler,
iris_prog_cd, phase_cd "substr(:phase_cd, 3,1) AND (UPPER(substr(:phase_cd,1,2)) <> 'UB')",
curr_bud_am, enc_am, actu_exp_am)
The input file is as follows:
AMHS,SAMHS00015,T04017,578034.27,0,60
FHWY,Z618720000,TC1000,4290300,0,0
FHWY,Z618720000,TC2000,962198.76,0,0
FHWY,Z618720000,TC3000,3147315.76,0,0
FHWY,Z618720000,TC7000,1000000,0,0
FHWY,Z618720000,UB1748,228.95,0,228.95
FHWY,Z618720000,UB1749,2306.53,0,2306.53
FHWY,Z618720000,UB1750,1.06,0,1.06
FHWY,Z618720000,UB1751,10.69,0,10.69
FHWY,Z618880000,TC4000,103292.94,0,0
I think the error is probably very obvious to those "sqlldr" guru, but I just cannot figure it out after spending only days...
Any/all the help on this would be greatly appreciated.
Thanks.