-
SQL Loader - WHEN Clause
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...
Cheers!
OraKid.
-
Originally Posted by balajiyes
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Thank You jmodic.
Let me think over.
Just share how to handle this. thru SQL Loader.
Cheers!
OraKid.
-
Will this work:
AND (RECORD_TYPE IN ( '1', '3'))
Tamil
-
Originally Posted by tamilselvan
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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.
Cheers!
OraKid.
-
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...
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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?
Cheers!
OraKid.
-
Originally Posted by balajiyes
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|