Re: sql*loader delemited fields , if length(field1)>20 then field1=null

It works great!
Thank you very much!

Soazig
Thomas Kyte <tkyte_at_oracle.com> wrote in message news:<a80hlt0hve_at_drn.newsguy.com>...
> In article <8fa56fe5.0203280227.17ede8ea_at_posting.google.com>,
> soazig.forterre_at_laposte.net says...
>
> ...
> LOCATION_PHONE_NUMBER
> "decode( sign(length(:LOCATION_PHONE_NUMBER)-20),
> 1, NULL, :LOCATION_PHONE_NUMBER)",
> ....
>
> says:
> if (length(phone-number) > 20 ) then null
> else phone-number
>
> put it on one line
>
>
>
>
>
> >
> >Hello,
> >I have the following problem, I want to load data from a file using
> >sql*loader.
> >The file is tab delimited.
> >There is a field LOCATION_PHONE_NUMBER, which in the file could exceed
> >20 characters, in my table it is 20 characters long VARCHAR2(20). I
> >want to put null on the field when in the file the values exceed 20
> >characters long, and leave the field value otherwise.
> >I don't know how to do that, can you help me, please.
> >
> >I try this on the control file but it doesn't work
> >OPTIONS (skip = 2)
> >load data
> >infile '$HOME/rit/mind.in'
> >truncate
> >into table IMPORT_MIND
> >fields terminated by ' '
> >(
> >LOCATION_ID,
> >COMPANY_ID,
> >COMPANY_FULL_NAME,
> >COMPANY_SHORT_NAME,
> >LOCATION_FULL_NAME,
> >LOCATION_SHORT_NAME,
> >LOCATION_ADDRESS1,
> >LOCATION_ADDRESS2,
> >LOCATION_ADDRESS3,
> >LOCATION_POSTAL_CODE,
> >LOCATION_TOWN,
> >LOCATION_COUNTRY_CODE,
> >LOCATION_COUNTRY_NAME,
> >LOCATION_SO2_CODE,
> >LOCATION_SO2_NAME,
> >CHANNEL_SEGMENT_CODE,
> >CHANNEL_SEGMENT_DESCRIPTION,
> >CHANNEL_SUBSEGMENT_CODE,
> >CHANNEL_SUBSEGMENT_DESCRIPTION,
> >LOCATION_PHONE_NUMBER NULLIF (length(:LOCATION_PHONE_NUMBER) > 20),
> >LOCATION_PHONE_AREA_CODE,
> >LOCATION_FAX_NUMBER ,
> >LOCATION_FAX_AREA_CODE,
> >LOCATION_EMAIL,
> >HEADQUARTER_FLAG,
> >LOCATION_URL,
> >LOCATION_TAXID,
> >CREATION_DATE,
> >LAST_UPDATE_DATE,
> >RECORD_STATUS
> >)
> >
> >I would be grateful for anyhelp.
> >
> >thank you in advance
> >best regards
> >Soazig
Received on Sat Mar 30 2002 - 19:50:44 CET