sqlldr with conditional insertion? 1

[lowbk]

is it possible to write a ctl file such that if a certain criteria is met, sqlldr will insert into a certain table else it will not insert into that table?
eg
in the ctl file

load data
infile 'data.txt'
append into table table1
(
...
someflag postition(...) char,
...
)

append into table table2 only if :someflag is of a certain value
(
..
)

possible to achieve?

 

[rcurva]

Yes, you can use the WHEN clause.

For example;

load data
infile 'data.txt'
append into table table2
WHEN someflag = 'Y')
(
...
someflag postition(...) char,
...
)
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[rcurva]

Some modification:

load data
infile 'data.txt'
append into table table1
(
...
someflag postition(...) char,
...
)

into table table2
when someflag = 'Y'
(
..
)
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[Turkbear]

Hi, it is a very powerful ability that I use to load several tables ( with criminal history info) form 1 large flat file:

LOAD DATA
INFILE "PUBLIC.DAT"
INSERT
INTO TABLE bca_personal
when record_type = '01'
 ( RECORD_TYPE POSITION(1:2),
 PERSON_NUM POSITION(3:8),
 LAST_NAME  POSITION(9:38),
 FIRST_NAME  POSITION(39:68) ,
 MIDDLE_NAME POSITION(69:98) ,
 BIRTH_DT  POSITION(99:106) DATE 'mmddyyyy' NULLIF BIRTH_DT = BLANKS,
 SEX POSITION(107:107),
 RACE POSITION(108:108))
 
INTO TABLE bca_alias
when record_type = '02'
 ( RECORD_TYPE POSITION(1:2),
 PERSON_NUM POSITION(3:8),
 LAST_NAME  POSITION(9:38),
 FIRST_NAME  POSITION(39:68) ,
 MIDDLE_NAME POSITION(69:98) )

INTO TABLE bca_otherdob
when record_type = '03'
 ( RECORD_TYPE POSITION(1:2),
 PERSON_NUM POSITION(3:8),
 BIRTH_DT POSITION(9:16) DATE 'mmddyyyy' NULLIF BIRTH_DT = BLANKS)
 
INTO TABLE bca_conv_head
when record_type = '04'
 ( RECORD_TYPE POSITION(1:2),
 PERSON_NUM POSITION(3:8),
 CONVICTION_NUM POSITION(9:11),
 CONTROLLING_AGENCY_CODE POSITION(12:20),
 CONTROLLING_AGENCY POSITION(21:85),
 CASE_NUMBER POSITION(86:97),
 COURT_DISPOSITION_DT  POSITION(98:105),
 COURT_AGENCY_CODE POSITION(106:114),
 COURT_AGENCY POSITION(115:179),
 ASSIGNED_CUSTODIAL_AGENCY POSITION(180:244),
 ASSIGNED_PROBATION_AGENCY POSITION(245:309))

INTO TABLE bca_conv_data
when record_type = '05'
 ( RECORD_TYPE POSITION(1:2),
 PERSON_NUM POSITION(3:8),
 CONVICTION_NUM POSITION(9:11),
 COUNT_NUM POSITION(12:14),
 STATUTE_NUMBER POSITION(15:39),
 STATUTE_DESCRIPTION POSITION(40:139),
 DISPOSITION POSITION(140:189),
 COURT_FILE_NUMBER POSITION(190:199),
 GENERAL_OFFENCE POSITION(200:249),
 PRONOUNCED_FINE POSITION(250:259),
 STAYED_FINE POSITION(260:269),
 COURT_COST_AMOUNT POSITION(270:279),
 RESTITUTION_AMOUNT POSITION(280:289),
 ASSESSMENT_AMOUNT POSITION(290:299),
 PRONOUNCED_SENTENCE POSITION(300:309),
 PROBATIONAL_SENTENCE POSITION(310:319),
 CONDITIONAL_CONFINEMENT POSITION(320:329),
CONVICTION_LEVEL POSITION(330:330))
[code]
Any records that fail all the when clauses go into the DISCARD file.
It makes life easier when tools really can do most of the work..

[profile]

 

[lowbk]

suppose it is to insert into a certain table when the flag has this value or that value, how will the when change?

i am trying

load data
infile 'data.txt'
append into table table1
(
...
someflag postition(...) char,
...
)

into table table2
when someflag = 'Y' or someflag='1'
(
..
)

but keeps getting syntax error.