SQL LOADER - WHEN CLAUSE

[dayankoven]

Hi there fellow gurus,

I have a situation here in which i seek some expert opinion/solution from the fellow forumers. Basically what i'm trying to do is :-

I have a fixed length flat which i want to load into an ORACLE database using SQLLOADER. But the problem is that i DO NOT want to load the entire flat file but only selected records. I intend to utilise the WHEN clause in the control file but the problem is my WHEN clause needs to encompass a string of conditions. FOR eg
I need to load the records in which POSITION(23:25) = 'AB' OR 'BC' OR 'DE'. Can i perform what i just mentioned with SQLLOADER? If i could, would be grateful if anyone could highlight to me the syntax of doing so...

Thanks in advance.

 

[flstffatboy]

Dayankoven,

The when clause can be used for several comparisons provided each is preceded by AND.

The when clause should be right after the table name in the control file.

example:

WHEN (23:25) = 'AB' AND (23:25) = 'BC' and (23:25) = 'DE'


Hope this helps,
flstf

 

[flstffatboy]

Dayankovan,

I wasn't thinking right when I posted that reply too early in the morning brain must not be working. You can't use OR with the WHEN clause (only AND). My guess is you will have to run this three times with each when clause.

flstf

 

[THoey]

Or just load it to a staging table and then run a APPEND query from the staging table to the production table... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[teado]

Not sure if this would work, please correct me if it won't:
(same control file)
...
INTO TABLE (table)
WHEN (23:25) = 'AB'
(
...
)
INTO TABLE (table)
WHEN (23:25) = 'BC'
(
...
)

and so on.

We use something similar to create multiple rows from one line.