sqlldr in between date

[bvo]

Hi,

I have a huge text file and it loaded so long and lack of the tablespace, it couldn't finish loading.

Is there a way, I can load my text file into Oracle table using where clause in between the date I want to load?

I couldn't find the right syntax to load can you all help? Thanks.

Mine controlfile is:

Load DATA
INFILE './cat_data.txt'
INTO TABLE tm_data
when (datetime between to_date('20070116','yyyymmdd') and to_date('20070122','yyyymmdd'))
fields terminated by "|"
(id, localid, facility, datetime DATE "yyyy-mm-dd hh24:mi:ss", code, cid, acid, type, alt, oldsector, newsector, depart, arrapt, heading, beacon, lat, lon, rules)

[PAVB]

As far as I remember SQLLoader WHEN clause only accept = and <> conditions... you might want to research "external tables" if you want to process your file using a > or < condition

[bvo]

I have tried that and still didn't work

LOAD DATA
INFILE './catherine_data.txt'
INTO TABLE catherine_data
WHEN (to_date(datetime,"yyyy-mm-dd") > to_date("2007-01-15", "yyyy-mm-dd'" AND to_date(datetime,"yyyy-mm-dd") < to_date("2007-01-22","yyyy-mm-dd"))
fields terminated by "|"
(id, localid, facility, datetime DATE "YYYY-MM-DD HH24:MI:SS", code, cid, acid, type, alt, oldsector, newsector, depapt, arrapt, heading, beacon, lat, lon, rules)

syntax error at line 4
Expecting = or "<>" found "("
WHEN (to_date(datetime,"yyyy-mm-dd") > to_date("2007-01-15", "yyyy-mmdd"

[PAVB]

Sorry for not been clear.
You cannot use conditions other than equality (=) and inequality (<>) in a SQLLoader WHEN clause.

[bvo]

I tried
LOAD DATA
INFILE './catherine_data.txt'
INTO TABLE catherine_data
WHEN datetime = '2007-01-16'
fields terminated by "|"
(id, localid, facility, datetime DATE "YYYY-MM-DD HH24:MI:SS", code, cid, acid, type, alt, oldsector, newsector, depapt, arrapt, heading, beacon, lat, lon, rules)

and run the sqlldr... it seems like working but it bombs out and nothing load no error message

[PAVB]

Check your log looking for rejected rows...
or,
if you want to capture whatever doesn't meet your criteria add a second INTO TABLE bad_catherine_data clause followed by WHEN datetime <> '2007-01016"

[bvo]

I have tried and nothing wrong... and no error... still not loading. I just load the whole data into the table then it's ok. Still didn't figure out how to load data range

[PAVB]

You cannot load a range of data, you can ask for "equal to" or "not equal to" there is not "greather than" or "less than" in the SQLLOADER world ;-)

[bvo]

I did put = and it runs for awhile and get back to the prompt sign.
I checked the table and it's empty.

[waitecj]

It means that the data doesn't meet the criteria you've set:

WHEN datetime = '2007-01016'

Is that a valid date format for your data? It doesn't look right to me...