SQL Loader - WHEN Clause

[balajiyes]

I have to validate 2 columns while loading my data.

I tried using this logic.

Need to load RECORD_TYPE in (1, 3).

Send me the syntax for the same.

I tried something like this...
LOAD DATA
APPEND
INTO TABLE IPDR_3G_REL2_TMP
WHEN VENDOR != 'TRA' AND VENDOR != 'HEA'
AND (RECORD_TYPE = '1' OR RECORD_TYPE = '3')
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS

I am getting syntax error.

Please revert.

Cheers...

[jmodic]

I tried something like this...
LOAD DATA
APPEND
INTO TABLE IPDR_3G_REL2_TMP
WHEN VENDOR != 'TRA' AND VENDOR != 'HEA'
AND (RECORD_TYPE = '1' OR RECORD_TYPE = '3')
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS

I am getting syntax error.

The problem is OR operator between the two field conditions in the WHEN clause.

Unfortunately SQL*Loader support only AND operator between different expressions in the WHEN clause.

Maybe it will be easier for you to create a simple check constraint on the table before starting your load.

[balajiyes]

Thank You jmodic.

Let me think over.

Just share how to handle this. thru SQL Loader.

[tamilselvan]

Will this work:
AND (RECORD_TYPE IN ( '1', '3'))

Tamil

[jmodic]

Will this work:
AND (RECORD_TYPE IN ( '1', '3'))

No, it will not. You can't use IN operator, only equality operators ('=', '!=' or '<>') are permited in the WHEN clause expressions.

[balajiyes]

Thanks guys.

For the time being I am using RECORD_TYPE <> 2.

But we will face this issue from Feb'06. coz we are goin to have 2 more types coming in.

Before that we need to find a solution or we need to change our design.

[jmodic]

And what is stopping you from creating a simple CHECK constraint on that table, where you can easily check that only RECORD TYPE IN ('1', '3') are allowed?

That way, your loader session will try to load records with any record_type, but all that violate that check criteria will be rejected by the constraint. So instead of letting SQL*Loader to do the checking you let the database to do it instead...

[balajiyes]

Thanks jmodic.

I will be loading around 3500 file per day in my temp table. each file having 40,000 records.

Around mid night I will swap the partition.

Having a CHECK constraint, will it impact my temp table. If yes, how much?

[jmodic]

Having a CHECK constraint, will it impact my temp table. If yes, how much?

Will it impact performance? Certanly.
How severely? Hard to say, but I would speculate that it will be barely noticable.

Instead of guessing, why don't you simply try it? Get a sample of your data and load it - once with a constraint in place and once without it.