Search code examples
oracle-databasesql-loadercontrolfile

How can I use a '>' or '<' in the when clause of a control file?

LOAD DATA
INFILE 'Sample2.dat'
APPEND INTO TABLE EMP_LEAVE
WHEN REQUEST_DATE > SYSDATE  --The problem lies here
FIELDS TERMINATED BY ","
(REQUEST_NO,
EMPNO,
REQUEST_DATE    DATE    "DD-MM-YYYY",
START_DATE  DATE    "DD-MM-YYYY",
END_DATE        DATE    "DD-MM-YYYY",
REASON,
LEAVE_TYPE,
NO_OF_DAYS,
APPROVAL
)

I'm trying to insert only those rows where the REQUEST_DATE is higher than the current date. Any idea how I could do that?

Solution

  • As far as I can tell, you can't directly from SQL*Loader.

    WHEN clause, which is used to conditionally load records, accepts only "equal" or "not equal" operators, i.e. =, <> or !=, i.e. you can't use "greater than" > and similar.

    For more info, see this:

    A field condition is a statement about a field in a logical record that evaluates as true or false. It is used in the WHEN, NULLIF, and DEFAULTIF clauses.

    (...)

    operator: A comparison operator for either equal or not equal.

    So, what to do?

    • load data into a temporary table and then extract rows you want
    • instead of SQL*Loader, use external tables feature which lets you write a query against it (i.e. you're directly accessing the file as if it were an "ordinary" table, so you can use any WHERE clause you want, including where request_date > sysdate)