Home » RDBMS Server » Server Utilities » Multiple WHEN conditions in SQL*Loader
Multiple WHEN conditions in SQL*Loader [message #301423] Wed, 20 February 2008 09:53 Go to next message
amcghie
Messages: 35
Registered: March 2005
Location: Sunny Dubai
Member
Hello all

I appear to be having some issues with SQL*Loader and was hoping someone may be able to tell me what is wrong with my control file. Basically I only want to load data where the 'status' is equal to 'SUCCESS' or'FAILURE'.

I have made a simple example to demonstrate my issue:

CREATE TABLE sqlldr_test
  ( trans_id   NUMBER       NOT NULL
  , trans_dtm  DATE         NOT NULL
  , status     VARCHAR2(10) NOT NULL );

My Control is as follows:

LOAD DATA
  APPEND
  INTO TABLE sqlldr_test
  WHEN status = 'SUCCESS'
  FIELDS TERMINATED BY ","
  TRAILING NULLCOLS
    ( trans_id    INTEGER EXTERNAL
    , trans_dtm   TIMESTAMP "YYYY-MM-DD HH24MISS"
    , status      CHAR )
  INTO TABLE sqlldr_test
  WHEN status = 'FAILURE'
  FIELDS TERMINATED BY ","
  TRAILING NULLCOLS
    ( trans_id    INTEGER EXTERNAL
    , trans_dtm   TIMESTAMP "YYYY-MM-DD HH24MISS"
    , status      CHAR )

The contents of my datafile is as follows:

1,2008-02-20 070000,SUCCESS
2,2008-02-20 070000,SUCCESS
3,2008-02-20 070001,PENDING
4,2008-02-20 070005,FAILURE

Given the control file above I would have expected lines 1,2 and 4 of the datafile to be loaded however as the query (and log file entry) below shows, only lines 1 and 2 have been loaded:

SELECT * FROM sqlldr_test;

  TRANS_ID TRANS_DTM            STATUS
---------- -------------------- ----------
         1 20-FEB-2008 07:00:00 SUCCESS
         2 20-FEB-2008 07:00:00 SUCCESS

The output of the sqlldr log file is as follows:

Quote:
SQL*Loader: Release 11.1.0.6.0 - Production on Wed Feb 20 15:13:13 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Control File: sqlldr.ctl
Data File: datafile.dat
Bad File: datafile.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 256000 bytes
Continuation: none specified
Path used: Conventional

Table SQLLDR_TEST, loaded when STATUS = 0X53554343455353(character 'SUCCESS')
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
TRANS_ID FIRST * , CHARACTER
TRANS_DTM NEXT * , DATETIME YYYY-MM-DD HH24MISS
STATUS NEXT * , CHARACTER

Table SQLLDR_TEST, loaded when STATUS = 0X4641494c555245(character 'FAILURE')
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
TRANS_ID NEXT * , CHARACTER
TRANS_DTM NEXT * , DATETIME YYYY-MM-DD HH24MISS
STATUS NEXT * , CHARACTER

Record 3: Discarded - failed all WHEN clauses.
Record 4: Discarded - failed all WHEN clauses.

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


Table SQLLDR_TEST:
0 Rows successfully loaded.
0 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.

Space allocated for bind array: 99072 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 4
Total logical records rejected: 0
Total logical records discarded: 2

Run began on Wed Feb 20 15:13:13 2008
Run ended on Wed Feb 20 15:13:17 2008

Elapsed time was: 00:00:03.75
CPU time was: 00:00:00.03



Any help would be greatly appreciated.

Kind Regards

Andy
Re: Multiple WHEN conditions in SQL*Loader [message #301462 is a reply to message #301423] Wed, 20 February 2008 13:23 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Hmmm, when you use comma delimited as opposed to fixed format, can you use
column_name='{value}'

with multiple when clauses?
Re: Multiple WHEN conditions in SQL*Loader [message #301465 is a reply to message #301462] Wed, 20 February 2008 13:59 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
When you have multiple when clauses, you have to reset the position for the first character of the first field for each when clause after the first one, so that it knows where to start:

LOAD DATA
APPEND
INTO TABLE sqlldr_test
WHEN status = 'SUCCESS'
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
( trans_id INTEGER EXTERNAL
, trans_dtm TIMESTAMP "YYYY-MM-DD HH24MISS"
, status CHAR )
INTO TABLE sqlldr_test
WHEN status = 'FAILURE'
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
( trans_id POSITION (1) INTEGER EXTERNAL
, trans_dtm TIMESTAMP "YYYY-MM-DD HH24MISS"
, status CHAR )
Re: Multiple WHEN conditions in SQL*Loader [message #301466 is a reply to message #301465] Wed, 20 February 2008 14:02 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Shoot, I'm sorry I didn't remember that one. I think Barbara answered this same type of question before.
Re: Multiple WHEN conditions in SQL*Loader [message #301469 is a reply to message #301466] Wed, 20 February 2008 15:19 Go to previous message
amcghie
Messages: 35
Registered: March 2005
Location: Sunny Dubai
Member
Barbara you are an absolute star!

Thank-you so much, not sure how I missed that in the Doco
Previous Topic: Loadin filename in a table
Next Topic: SqlLoader Substring in Control File
Goto Forum:
  


Current Time: Fri Dec 02 20:28:34 CST 2016

Total time taken to generate the page: 0.09437 seconds