SQLLDR, Continueif and when clauses

Will try that and see if it fixes it.  Will reply with the result.

sjl

isshando
The continueif is now working where it did not before, because now it is showing only 22 records instead of the 40 it showed previously.  Still failing the when clauses, though:

/home_shr/levine/pr06_006.d$ cat pochi.log

SQL*Loader: Release 8.1.5.0.0 - Production on Fri Feb 24 11:24:40 2006

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Control File:   pochi.ctl
Data File:      pochi.dat
  Bad File:     pochi.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 65536 bytes
Continuation:   1:9 = 0X312c52455344415445(character '1,RESDATE'), in next physi
cal record
Path used:      Conventional

Table POCHI_SAMPLE, loaded when PRIMARY_ID = 0X30(character '0')
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
PRIMARY_ID                          FIRST     *   ,       CHARACTER
  (FILLER FIELD)
INSTRUMENT_ID                        NEXT     *   ,       CHARACTER
STATION_ID                           NEXT     *   ,       CHARACTER
TECHID                               NEXT     *   ,       CHARACTER
IMEXPDATE                            NEXT     *   ,       CHARACTER
IMEXPTIME                            NEXT     *   ,       CHARACTER
UNITNO                               NEXT     *   ,       CHARACTER
SAMPLE_CODE                          NEXT     *   ,       CHARACTER

Table POCHI_RESULT, loaded when PRIMARY_ID = 0X31(character '1')
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
PRIMARY_ID                           NEXT     *   ,       CHARACTER
  (FILLER FIELD)
TESTNAME                             NEXT     *   ,       CHARACTER
TESTFLAGS                            NEXT     *   ,       CHARACTER
TESTRESULT                           NEXT     *   ,       CHARACTER
RECCODE                              NEXT     *   ,       CHARACTER
  (FILLER FIELD)
MOREFLAGS                            NEXT     *   ,       CHARACTER
  (FILLER FIELD)
TESTDATE                             NEXT     *   ,       CHARACTER
    SQL string for column : "to_date(substr(TESTDATE,1,8), 'YYYYMMDD')"

Record 1: Discarded - failed all WHEN clauses.
Record 2: Discarded - failed all WHEN clauses.
Record 3: Discarded - failed all WHEN clauses.
Record 4: Discarded - failed all WHEN clauses.
Record 5: Discarded - failed all WHEN clauses.
Record 6: Discarded - failed all WHEN clauses.
Record 7: Discarded - failed all WHEN clauses.
Record 8: Discarded - failed all WHEN clauses.
Record 9: Discarded - failed all WHEN clauses.
Record 10: Discarded - failed all WHEN clauses.
Record 11: Discarded - failed all WHEN clauses.
Record 12: Discarded - failed all WHEN clauses.
Record 13: Discarded - failed all WHEN clauses.
Record 14: Discarded - failed all WHEN clauses.
Record 15: Discarded - failed all WHEN clauses.
Record 16: Discarded - failed all WHEN clauses.
Record 17: Discarded - failed all WHEN clauses.
Record 18: Discarded - failed all WHEN clauses.
Record 19: Discarded - failed all WHEN clauses.
Record 20: Discarded - failed all WHEN clauses.
Record 21: Discarded - failed all WHEN clauses.
Record 22: Discarded - failed all WHEN clauses.

Table POCHI_SAMPLE:
  0 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  22 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Table POCHI_RESULT:
  0 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  22 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                  63726 bytes(19 rows)
Space allocated for memory besides bind array:        0 bytes

Total logical records skipped:          0
Total logical records read:            22
Total logical records rejected:         0
Total logical records discarded:       22

Run began on Fri Feb 24 11:24:40 2006
Run ended on Fri Feb 24 11:24:46 2006

Elapsed time was:     00:00:05.84
CPU time was:         00:00:00.06

sjl

I forgot to include the table structure in my original post:

SQL> desc POCHI_SAMPLE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 UNITNO                                    NOT NULL VARCHAR2(15)
 IMEXPDATETIM                              NOT NULL DATE
 INSTRUMENT_ID                                      NUMBER
 STATION_ID                                         VARCHAR2(16)
 SAMPLE_CODE                                        VARCHAR2(30)
 TECHID                                             VARCHAR2(30)
 DATERCVDDB                                         DATE
 IMEXPDATE                                          VARCHAR2(10)
 IMEXPTIME                                          VARCHAR2(10)

SQL> desc POCHI_RESULT
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 UNITNO                                    NOT NULL VARCHAR2(15)
 IMEXPDATETIM                              NOT NULL DATE
 TESTNAME                                  NOT NULL VARCHAR2(30)
 TESTDATE                                  NOT NULL DATE
 TESTFLAGS                                          NUMBER
 TESTRESULT                                         NUMBER
 TESTCOMMENT                                        VARCHAR2(50)
 TESTRESRCVD                                        DATE

SQL>

It might be because the field you are using in the when clause is a filler and not a real field that is being loaded.
You can try using positions in the WHEN clause:
when (1:1) = '0'

Hope that helps!

When I use the position instead of the name, like you suggest, I get the following result:

obidrm:levine:devl:
Fri Feb 24 11:21:22 CST 2006
/home_shr/levine/pr06_006.d$ sqlldr control=pochi log=pochi bad=pochi
Username:levine
Password:

SQL*Loader: Release 8.1.5.0.0 - Production on Fri Feb 24 16:40:36 2006

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 21

obidrm:levine:devl:
Fri Feb 24 11:21:22 CST 2006
/home_shr/levine/pr06_006.d$ cat pochi.log

SQL*Loader: Release 8.1.5.0.0 - Production on Fri Feb 24 16:40:36 2006

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Control File:   pochi.ctl
Data File:      pochi.dat
  Bad File:     pochi.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 65536 bytes
Continuation:   1:9 = 0X312c52455344415445(character '1,RESDATE'), in next physi
cal record
Path used:      Conventional

Table POCHI_SAMPLE, loaded when 1:1 = 0X30(character '0')
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
PRIMARY_ID                              1     *   ,       CHARACTER
  (FILLER FIELD)
INSTRUMENT_ID                        NEXT     *   ,       CHARACTER
STATION_ID                           NEXT     *   ,       CHARACTER
TECHID                               NEXT     *   ,       CHARACTER
IMEXPDATE                            NEXT     *   ,       CHARACTER
IMEXPTIME                            NEXT     *   ,       CHARACTER
UNITNO                               NEXT     *   ,       CHARACTER
SAMPLE_CODE                          NEXT     *   ,       CHARACTER
FILLER2                              NEXT     *   ,       CHARACTER
  (FILLER FIELD)
FILLER3                              NEXT     *   ,       CHARACTER
  (FILLER FIELD)

Table POCHI_RESULT, loaded when 1:1 = 0X31(character '1')
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
PRIMARY_ID                              1     *   ,       CHARACTER
  (FILLER FIELD)
TESTNAME                             NEXT     *   ,       CHARACTER
TESTFLAGS                            NEXT     *   ,       CHARACTER
TESTRESULT                           NEXT     *   ,       CHARACTER
MOREFLAGS                            NEXT     *   ,       CHARACTER
  (FILLER FIELD)
TESTDATE                             NEXT     *   ,       CHARACTER
    SQL string for column : "to_date(substr(TESTDATE,1,8), 'YYYYMMDD')"

Record 1: Discarded - failed all WHEN clauses.
Record 2: Discarded - failed all WHEN clauses.
Record 21: Discarded - failed all WHEN clauses.
Record 3: Rejected - Error on table POCHI_SAMPLE.
ORA-01401: inserted value too large for column

Record 4: Rejected - Error on table POCHI_SAMPLE.
ORA-01401: inserted value too large for column

Record 5: Rejected - Error on table POCHI_SAMPLE.
ORA-01401: inserted value too large for column

Record 6: Rejected - Error on table POCHI_SAMPLE.
ORA-01401: inserted value too large for column

Record 7: Rejected - Error on table POCHI_SAMPLE.
ORA-01401: inserted value too large for column

Record 8: Rejected - Error on table POCHI_SAMPLE.
ORA-01401: inserted value too large for column

Record 9: Rejected - Error on table POCHI_SAMPLE.
ORA-01401: inserted value too large for column

Record 10: Rejected - Error on table POCHI_SAMPLE.
ORA-01401: inserted value too large for column

Record 11: Rejected - Error on table POCHI_SAMPLE.
ORA-01401: inserted value too large for column

Record 12: Rejected - Error on table POCHI_SAMPLE.
ORA-01401: inserted value too large for column

Record 13: Rejected - Error on table POCHI_SAMPLE.
ORA-01401: inserted value too large for column

Record 14: Rejected - Error on table POCHI_SAMPLE.
ORA-01401: inserted value too large for column

Record 15: Rejected - Error on table POCHI_SAMPLE.
ORA-01401: inserted value too large for column

Record 16: Rejected - Error on table POCHI_SAMPLE.
ORA-01401: inserted value too large for column

Record 17: Rejected - Error on table POCHI_SAMPLE.
ORA-01401: inserted value too large for column

Record 18: Rejected - Error on table POCHI_SAMPLE.
ORA-01401: inserted value too large for column

Record 19: Rejected - Error on table POCHI_SAMPLE.
ORA-01401: inserted value too large for column

Record 20: Rejected - Error on table POCHI_SAMPLE.
ORA-01401: inserted value too large for column

Record 22: Discarded - failed all WHEN clauses.

Table POCHI_SAMPLE:
  0 Rows successfully loaded.
  18 Rows not loaded due to data errors.
  4 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Table POCHI_RESULT:
  0 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  22 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                  65274 bytes(23 rows)
Space allocated for memory besides bind array:        0 bytes

Total logical records skipped:          0
Total logical records read:            22
Total logical records rejected:        18
Total logical records discarded:        4

Run began on Fri Feb 24 16:40:36 2006
Run ended on Fri Feb 24 16:40:41 2006

Elapsed time was:     00:00:04.81
CPU time was:         00:00:00.03

obidrm:levine:devl:
Fri Feb 24 11:21:22 CST 2006
/home_shr/levine/pr06_006.d$

What is wrong with this is that record 1 is not read at all and attempt is made to load the other records in POCHI_SAMPLE instead of POCHI_RESULT.

I am now trying to do the same thing with the UTL_FILE package using the PAQ When Clause in sqlloader:
https://www.experts-exchange.com/questions/10326879/When-clause-in-sqlloader.html

If that does not work, I will probably sqlload into a staging table although I would like to avoid it if I can.

sjl

sjl

Okay

I have solved my problem with loading multiple tables and have been successful doing it with the above data using sql loader.

My solution:
1.  As the Oracle documentation states (on page 5-37 of the Oracle 8i Utilities Manual for v.8.1.5):

"For CONTINUEIF THIS and CONTINUEIF NEXT, the continuation field is
removed from all physical records before the logical record is assembled."

What I did not realize is that this applied to all records, not just the ones I wanted to concatenate.

To handle this, I used awk to place a character in front of the primary_id for each record in the pochi.dat file, making the character "C" if the physical record was to be a continuation of the previous one or an "R" if it was not.

2.  The continueif condition determines when the logical record being read is appended to the previous one.  Thus CONTINUEIF NEXT was not what I wanted to use, because the concatenation would be incorrect, and it was, as I found out.  Using CONTINUEIF THIS solved that problem.

3.  I did have problems loading TESTDATE as a date, so I created a new column TESTDATESTR into which I loaded the data, using a trigger to populate the TESTDATE field from it.

My final control file was:

LOAD DATA
INFILE 'pochi.dat'
APPEND
CONTINUEIF THIS (1:1) = 'R'
INTO TABLE POCHI_SAMPLE
when primary_id = '0'
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
   (primary_id filler position(1) char
  , INSTRUMENT_ID
  , STATION_ID
  , TECHID
  , IMEXPDATE
  , IMEXPTIME
  , UNITNO
  , SAMPLE_CODE
  , filler2    filler
  , filler3    filler)
INTO TABLE POCHI_RESULT
when primary_id = '1'
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
   (primary_id filler position(1) char
  , TESTNAME
  , TESTFLAGS      integer external
  , TESTRESULT     float   external
  , filler4   filler
  , filler5   filler
  , moreflags filler
  , TESTDATESTR
  , filler6   filler)

My pochi.dat file, after operation with awk, looks like this:

/home_shr/levine/pr06_006.d$ cat pochi.dat
R0,000,LAB,,20060217,102819,5878778,,,36,
R5,,,,
R1,WBC,00000,4.7,
C1,RESDATE,00000,19160602073906,
R1,RBC,00000,4.19,
C1,RESDATE,00000,19160602073906,
R1,HGB,00000,12.6,
C1,RESDATE,00000,19160602073906,
R1,HCT,00000,38.2,
C1,RESDATE,00000,19160602073906,
R1,MCV,00000,91.2,
C1,RESDATE,00000,19160602073906,
R1,MCH,00000,30.1,
C1,RESDATE,00000,19160602073906,
R1,MCHC,00000,33.0,
C1,RESDATE,00000,19160602073906,
R1,PLT,00000,272,
C1,RESDATE,00000,19160602073906,
R1,W-SCR,00000,17.2,
C1,RESDATE,00000,19160602073906,
R1,W-MCR,00000,18.7,
C1,RESDATE,00000,19160602073906,
R1,W-LCR,00000,64.1,
C1,RESDATE,00000,19160602073906,
R1,W-SCC,00000,0.8,
C1,RESDATE,00000,19160602073906,
R1,W-MCC,00000,0.9,
C1,RESDATE,00000,19160602073906,
R1,W-LCC,00000,3.0,
C1,RESDATE,00000,19160602073906,
R1,RDW-CV,00000,49.5,
C1,RESDATE,00000,19160602073906,
R1,PDW,00000,0.0,
C1,RESDATE,00000,19160602073906,
R1,MPV,00000,11.0,
C1,RESDATE,00000,19160602073906,
R1,P-LCR,00000,0.0,
C1,RESDATE,00000,19160602073906,
R9,5878778,36,

Last, I do want to thank ishando for his help with the syntax of the CONTINUEIF statement.

sjl