Adding Logic to SQL*Loader control file for loading a specific column

Question

I have a control file. I am trying to add logic for column "QUALIFYING":

when the value is NULL, it needs to be set to ‘F’.
when the value is ‘x’ it needs to be set to ‘T’

options (ERRORS=1000, SKIP=1)
load data 
infile '/clients/vca2/data/API/ADHOC_LOAD/MASTER_VCA_Reminder_setup_references.csv'
replace
into table VCABARK_CDI_DV.MASTER_REMINDER_SETUP
fields terminated by '|'
OPTIONALLY ENCLOSED BY '"' AND '"'
trailing nullcols
(
VI_Reminder_Id FILLER,
 WW_Id,
  Ranking,
  WW_Desc , 
  VS_Desc FILLER, 
  VCA_Client_Desc, 
  Do_Not_Display ,
  Qualifying ,
  IsNull,
  Batch_Date expression "(SELECT RUNTIME FROM VCABARK_CDI_DV.RUNTIME_API)",
  Region CONSTANT "US"
 )

Littlefoot · Accepted Answer · 2019-03-25 15:52:25Z

2

You could use CASE, such as in this example.

First, a sample table:

SQL> create table test (id number, qualifying varchar2(10), isnull varchar2(1));

Table created.

Control file:

load data 
infile *
replace
into table test
fields terminated by "|" TRAILING NULLCOLS 
(
id,
qualifying "case when :qualifying is null then 'F'
                 when :qualifying = 'x' then 'T'
                 else :qualifying
            end",
isnull
)

begindata
1||y
2|x|y
3|225|n

Testing:

SQL> $sqlldr scott/tiger@xe control=test05.ctl log=test05.log

SQL*Loader: Release 11.2.0.2.0 - Production on Pon O×u 25 16:49:56 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12545: Connect failed because target host or object does not exist

SQL> $sqlldr scott/tiger control=test05.ctl log=test05.log

SQL*Loader: Release 11.2.0.2.0 - Production on Pon O×u 25 16:50:57 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 2
Commit point reached - logical record count 3

SQL> select * from test;

        ID QUALIFYING I
---------- ---------- -
         1 F          y
         2 T          y
         3 225        n

SQL>

answered Mar 25, 2019 at 15:52

Littlefoot

144k15 gold badges42 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

okla Over a year ago

Thanks for your help, solution above worked perfectly

Collectives™ on Stack Overflow

Adding Logic to SQL*Loader control file for loading a specific column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related