Re: Concatenation in SQL Loader

From: Henk de Wilde <dewildeh_at_xs4all.nl>
Date: 1998/04/13
Message-ID: <3531ba11.4252387_at_news.xs4all.nl>#1/1


On Tue, 07 Apr 1998 11:00:55 -0600, michael_pusateri_at_i2.com wrote:

>I am trying to read an ASCII file and concatenate the following fields!
>
>ASCII FILE
>----------
>
>John, Smith,1234 Anywhere Rd, Suite 10, Dallas, TX , 75248
>
>
>DATABASE TABLE
>--------------
>Name - John Smith
>Address - 1234 Anywhere Rd Suite 10
>CityStZip - Dallas, TX 75248
>
>Any suggestion on how I would do this in SQL Loader?
>
>Michael
>

The problem here seems to be the fact that the fields are comma separated. In this case you _have_ to use a dummy table.

Now the easiest way is to define a dummy table with columns Firstname, LastName , Street, Room, City, State and Zip.

On this table you then define a database trigger :

CREATE TRIGGER Concatenate_Adress
AFTER INSERT ON Dummy_Adress
FOR EACH ROW
BEGIN

	INSERT INTO   True_Adress
		( Name
		, Adress
		, CityStZip )
	VALUES
		( :new.Firstname || ' ' || :new.LastName
		, :new.Adress || ' ' || :new.Room etc...

If you use a REPLACE in your control file you don't even have to bother cleaning up your dummy table, Sql*Loader will do it for you.

HTH Received on Mon Apr 13 1998 - 00:00:00 CEST

Original text of this message