Re: SQLLoader - Contenate and continueif

rajesh.garigipati_at_gmail.com wrote in news:1108608886.684671.282580 _at_z14g2000cwz.googlegroups.com:

> I am using 8.1.7. I checked some examples of continueif and found that
> multiple physical records are for different fields in the same logical
> record. What I need to concatenate 2 physical records into the same
> field. Do you know how to do that?
>

The table definition that is the target for the load and any TERMINATED BY clauses etc will define how the logical record is mapped on to the individual fields.

You can nearly do what you want, but there is a problem with the leading spaces at the start of each coninuation record.

The following sample control file will load the following data

LOAD DATA
INFILE *
DISCARDFILE loadtest.dsc
TRUNCATE
CONTINUEIF NEXT PRESERVE (1:2) = ' '
INTO TABLE loadtest
( cd CHAR(2),
  text CHAR(1000)
)
BEGINDATA

A1 ZAAAAAAAAAAAAAAAAAAAAAAAAAZ
B2 XBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBX
C3 WCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC

   CCCCCCCCCCCW

D4 DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
E5 EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE
F6 FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
   FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF

G7 GGGGGGGGGGGGGGGGGGGGGGGGGGG

**BUT**
The 'NEXT PRESERVE (1:2)' clause keeps the first 2 chars of the logical record so that A1, B2 etc are kept to populate the 'cd' column of the table. But this has the effect of keeping the (first two) spaces from the start of the continuation records that are included when the physical records are appended together. Result, gaps in the 'text' field that might give you a problem.

SQL> select * from loadtest;

CD

--
TEXT
-------------------------------------------------------------------------
-------

AA
ZAAAAAAAAAAAAAAAAAAAAAAAAAZ

BB
XBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBX

CC
WCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC   CCCCCCCCCCCW