SQL*Loader-350

[cisco999]

The following error displays when loading flat file data into Oracle using SQL*Loader:
SQL*Loader-350: Syntax error at line 5.Illegal combination of non-alphanumeric characters when
(ncm_date > to_char(sysdate-45 "MM/DD/YYYY&quot)
^

It doesn't seem to like the greater than symbol. = and <> symbols are accepted. Any one have an explanation and/or a way to load data from the past 45 days?

Thanks!

 

[dbtoo2001]

If the line (ncm_date > to_char(sysdate-45 &quot;MM/DD/YYYY&quot) is actually what is being attempted, then I believe the syntax for the to_char is incorrect (to_char(sysdate-45,'MM/DD/YYYY'))

 

[cisco999]

I've tried that syntax too with the same results. It may be that SQL*Loader only accepts '=', '!=', or '<>' with the WHEN parameter but the documentation doesn't specify that.

 

[ddrillich]

The documentation at:

http://rainbow.mimuw.edu.pl/oracle9i/server.901/a90192/ch05.htm#1005658

says:

Loading Records Based on a Condition
You can choose to load or discard a logical record by using the WHEN clause to test a condition in the record.
The WHEN clause appears after the table name and is followed by one or more field conditions. The syntax for field_condition is as follows:
fld_cond ::=

[(] {full_fieldname | pos_spec} operator {'char_string' | X'hex_string' | BLANKS} [)] AND

For example, the following clause indicates that any record with the value &quot;q&quot; in the fifth column position should be loaded:
WHEN (5) = 'q'

A WHEN clause can contain several comparisons, provided each is preceded by AND. Parentheses are optional, but should be used for clarity with multiple comparisons joined by AND, for example:
WHEN (deptno = '10') AND (job = 'SALES')


So, it’s pretty clear that you can compare only against 'char_string', which is not useful in your case, since you want to compare dates.

Another way to achieve what you want is load all your data into a temporary table and move from there only the desired rows to the permanent table.

You might also consider forcing the 'older' rows not be loaded so these rows will end up in the bad file.

Let me explain myself -

http://rainbow.mimuw.edu.pl/oracle9i/server.901/a90192/ch06.htm#1008153

says:
-- Applying SQL Operators to Fields --
A wide variety of SQL operators can be applied to field data with the SQL string. This string can contain any combination of SQL expressions that are recognized by the Oracle database server as valid for the VALUES clause of an INSERT statement.

I tried the following:
create table tst (
x date
)
insert into tst values ( case when sysdate < sysdate-45 then sysdate
else to_date('12012003','MMDDYYYY') end )

So, in the else clause you can use incorrect format mask (like 'MMDYYYY') and the row won't be loaded.

The control file from this page has this example:

LOAD DATA
INFILE *
APPEND INTO TABLE XXX
( &quot;Last&quot; position(1:7) char &quot;UPPER\&quot;Last\&quot&quot;
FIRST position(8:15) char &quot;UPPERFIRST)&quot;
)

In your case the line about this date column should roughly look like:

date_column &quot;case when :ncm_date > sysdate-45 then :ncm_date
else to_date('12012003','MMDYYYY') end &quot; ,


Regards,
Dan