Re: SQL*Loader conditions

[Quoted] "Kevin Gao" <kgao_at_iname.com> wrote in message news:27f584d1.0110181150.2f21017e_at_posting.google.com...
> I want to filter out some records during the loading process based on
> conditions like SUBSTR(column1,12,2)='CA' or
> SUBSTR(column3,40,2)='-L'. SQL*Loader allows you to add condition
> using WHEN clause. But only simple conditions like column1 = 2 (or
> >,<,!=) are allowed. Is there any workaround about this? I've tried
> using CHECK constraints, it works but slow down the whole process a
> lot ('cause I have over 3 million records need be loaded everyday and
> of that 20% will be filtered out).
>
> Thanks.
>
> -Kevin

[Quoted] Several workarounds exists, both of a fundamental nature 1 From your conditions it looks like you have concatenated several columns in one column. It is much easier to concatenate for reports and the like, than to 'parse' concatenated columns. I seriously think you should reconsider, because I think you will soon regret you set up the table in this fashion
[Quoted] 2 You should never ever directly load in the target table. Always use a staging table. In this case Sql*loader will get all records, and your sql script to copy the data over will deal with the appropiate filtering.

Hth,

Sybrand Bakker
Senior Oracle DBA Received on Thu Oct 18 2001 - 22:23:57 CEST