Re: Concatenation in SQL Loader

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/04/13
Message-ID: <35327f07.3954177_at_www.sigov.si>#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?

You can do this without loading your data first into inrermediate table, if you load your table with SQL Loader *through a view*. First you must create a view, based on your database table, consisting of as many view columns as there is fields in your ASCII data. In your example, I made the following table and view:

SQL> CREATE TABLE database_table
  2 (name VARCHAR2(12), address VARCHAR2(25),   3 citystzip VARCHAR2(18));

Table created.

SQL> CREATE OR REPLACE VIEW database_view AS   2 (SELECT name AS name1, name AS name2,

  3          address AS address1, address AS address2,
  4          citystzip AS csz1, citystzip AS csz2,
  5          citystzip AS csz3

  6 FROM database_table);

View created.

The following control file will load your sample line into the table through view in the way you wanted it:

LOAD DATA INFILE *
INSERT INTO TABLE database_view
FIELDS TERMINATED BY ','
 (name1, name2 ":name1 || :name2",
  address1, address2 ":address1 || :address2",   csz1, csz2, csz3 ":csz1 || :csz2 || :csz3") BEGINDATA
John, Smith,1234 Anywhere Rd, Suite 10, Dallas, TX , 75248

Here is a result:

SQL> select * from database_table;

NAME         ADDRESS                   CITYSTZIP
------------ ------------------------- ------------------

John Smith 1234 Anywhere Rd Suite 10 Dallas TX 75248

>Michael

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Mon Apr 13 1998 - 00:00:00 CEST