Re: replace function in SQL*LOADER control file ?

In article <94asct$khj$1_at_nnrp1.deja.com>,   Don't Bother <davidknollhoff_at_netscape.net> wrote:
>
>
> I've created a control file and I've replaced all chr(13) and chr(10)
> with a '][' in the data portion. I've modified the control file to
 try
> and import this way but get the following error. The tables contain
> several chr(13)||chr(10) which get replaced fine in the begindata (
> portion but can import. ;
>
> SQL*Loader-350: Syntax error at line 12.
> Expecting "," or ")", found "(".
> , replace(ADDRESS1,'][',chr(13))||chr(10)
>
> David Knollhoff
>
> My control file looks like;
>
> UNRECOVERABLE
> LOAD DATA
> INFILE *
> INTO TABLE address
> REPLACE
> FIELDS TERMINATED BY '|' TRAILING NULLCOLS
> (
> LLSID
> , COUNTER
> , ADDRESSTYPE
> , COUNTRYID
> , replace(ADDRESS1,'][',chr(13))||chr(10)
> , replace(ADDRESS2,'][',chr(13))||chr(10)
> , replace(ADDRESS3,'][',chr(13))||chr(10)
> , replace(ADDRESS4,'][',chr(13))||chr(10)
> , replace(CITY,'][',chr(13))||chr(10)
> , replace(DESCRIPTION,'][',chr(13))||chr(10)
> , replace(POSTALCODE,'][',chr(13))||chr(10)
> , PRIMARY
> , replace(ZIP4,'][',chr(13))||chr(10)
> , replace(STATEORPROVINCE,'][',chr(13))||chr(10)
> , replace(LASTCHANGEDBY,'][',chr(13))||chr(10)
> , LASTCHANGEDDATE date "DD-MON-YY:HH24MISS"
> , replace(BILLNAME,'][',chr(13))||chr(10)
> , BADADDRESS
> , replace(BILLATTENTION,'][',chr(13))||chr(10)
> , OMITFROMMAILINGS
> , replace(BILLFORMATID,'][',chr(13))||chr(10)
> , NUMBEROFCOPIES
> , replace(PICARDUSERNAME,'][',chr(13))||chr(10)
> )
> BEGINDATA
> 4|1|H|262|245 Union Ave. #1082||||Campbell||95008|P||CA|RUTHANNE|01-
 JUL-
> 00:152324||F||F|||
> 5|1|H|262|P.O.Box 22265||||San Francisco||94122|P||CA|RUTHANNE|01-JUL-
> 00:161347||F||F|||
> 6|1|H|262|314 Watson Street||||Monterey||93940|P||CA|RUTHANNE|01-JUL-
> 00:155337||F||F|||
>
> Sent via Deja.com
> http://www.deja.com/
>

The correct format is:

FIELDS TERMINATED BY '|' TRAILING NULLCOLS (
LLSID

, COUNTER
, ADDRESSTYPE
, COUNTRYID
, ADDRESS1              "replace(:ADDRESS1,'][',chr(13))||chr(10)"
, ADDRESS2              "replace(:ADDRESS2,'][',chr(13))||chr(10)"

There are good examples in the manual on the syntax in Case examples and of course in chapter 5 in section "Applying SQL Operators to Fields". Reading is a good thing.

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rii.com
              Usual disclaimers


Sent via Deja.com
http://www.deja.com/