Re: SQL loader - SKIP FIELD + ADD DATA

From: Daniel St-Jacques <daniel.st-jacques_at_ca.kontron.com>
Date: 19 Nov 2001 08:54:33 -0800
Message-ID: <2355c0e9.0111190854.5e6b2878@posting.google.com>

Thank you for the tips Thomas.

I do have an other one for you.

If a have a ASCII file with 3 fields:
code|long description|short description

I do have a table with 2 fields:
Code and description

I want to put the long description in to the table description if the length of it is smaller then 25 characters. If the ascii file long description is longer then 24 characters, I want to have the ascii file short description loaded into the description table.

I don't know how to make 'decode' working for that. table x: Code varchar2(3)

         description varchar2(24)

INTO TABLE X
( code "UPPER( :code )",

  description "IF LENGTH( :description ) > 24 THEN NULL ELSE UPPER(
:description )",
  description "IF description = NULL THEN UPPER( :description ) ELSE

:description"
)

In fact is : IF the long description from the ascii file is smaller then 25 characters, we should take that one else if the length of the ascii file long description is greater than 24 we have to take the ascii file short description.

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:<9t4d0h01bav_at_drn.newsguy.com>...
> In article <2355c0e9.0111161341.129f16db_at_posting.google.com>,
> daniel.st-jacques_at_ca.kontron.com says...
> >
> >Hello everyone
> >
> >I am using Oracle 7.3.2.2
> >
> >I do have an ascii file to load that is look like:
> >
> >number|name|address1|address2|city|zip
> >
> >In the Oracle table I have several field that are mandatory but not
> >present into the ascii file.
> >Oracle table: cust_id
> > name
> > address
> > city
> > zip
> > can-phone
> > can-write
> >
> >1. Is it possible with SQL loader 7.3.4.4.1 to skip a field from the
> >ascii file?
> > in this example "address2"
> >
> >2. Is it possible to force data into some field even if the data is
> >not part of the ascii file?
> > example : can-phone = "Y"
> > can-write = "N"
> >
> >Here is my control file
> >LOAD DATA
> >INFILE 'asciifile' BADFILE 'asciifile.bad'
> >INTO TABLE customer
> > FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
> > ( cust_num,
> > name "UPPER( :name )",
> > address "UPPER( :address )"
> >----> Something missing to skip address2. FILLER doesn't work
> > city "UPPER( :city )"
> > zip "UPPER( :zip )"
> > )
> >
> >There is 2 things missing in the control file.
> > 1. How to skip a field
> > 2. How to add fields with fix data that is not part of the ASCII
> >file.
> > If the fields that are not part of the ascii file can be filled
> >up
> > by condition it would be great.
> > example: if zip begins by 1
> > then can-phone = "Y"
> > can-write = "N"
> > else can-phone = "N"
> > can-write = "Y"
> >
> >Is anyone can find an EASY way to do this.
> >
> >That has to by done each week.
> >
> >I am looking forward for the answers
> >
> >Daniel St-Jacques
> >daniel.st-jacques_at_ca.kontron.com
>
>
> LOAD DATA
> INFILE * BADFILE 'asciifile.bad'
> INTO TABLE t
> replace
> FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
> trailing nullcols
> ( cust_id,
> name "UPPER( :name )",
> address "UPPER( :address )",
> can_phone "decode( substr(:zip,1,1), '1', 'Y', 'N' )",
> city "UPPER( :city )",
> zip "UPPER( :zip )",
> can_write "decode( substr(:zip,1,1), '1', 'N', 'Y' )"
> )
> begindata
> 1234|name|address1|address2|city|12345
> 5678|name|address1|address2|city|23456
>
>
> We can use the fact that you want to skip address2 to stuff "can_phone" in
> there. can_phone will get address2 but -- we'll apply a sql function to
> override that fact and populate can_phone with a decode on zip.
>
> Later, since we added trailing nullcols to the spec -- we'll load can_write
> using the same technique. Instead of loading NULLS as trailing nullcols would
> normally, we'll apply a function to zip again and get the data.
>
> After loading, we find:
>
> ops$ora734_at_ORA734.WORLD> l
> 1 select cust_id, zip, can_phone, can_write
> 2* from t
> ops$ora734_at_ORA734.WORLD> /
>
> CUST_ID ZIP CAN_PHONE CAN_WRITE
> ---------- ----- ------------------------- -------------------------
> 1234 12345 Y N
> 5678 23456 N Y
>
> as you want.
Received on Mon Nov 19 2001 - 10:54:33 CST