CODEWITHSUNDEEP
sqloracle-databaseoracle10gsql-loader
Girish R Acharya
Girish R Acharya

Reputation: 140

If Clause in SQL Loader

It is possible to use IF CLAUSE in sql loader? I had written a code but it is giving me error. 

INTO TABLE TF_RTB9_DTL 
WHEN ((01) = 'D') OR (if 
(POA_COU_VAL = 0) 
then MAST_POA_ID = null 
end if) 
FIELDS TERMINATED BY "~" OPTIONALLY ENCLOSED BY '"' 
TRAILING NULLCOLS 
( 

TRANS_TYP POSITION(3), 
BO_ID , 
NAME , 
MID_NAM , 
LAS_NAM , 
PAN , 
NAM_FIR_JOINT , 
MIDD_NAM_FIR_JOINT, 
LAS_NAM_FIR_JOINT , 
PAN_FIR_JOINT , 
NAM_SEC_JOINT , 
MIDD_NAM_SEC_JOINT, 
LAS_NAM_SEC_JOINT , 
PAN_SEC_JOINT , 
ADD_LIN_1 , 
ADD_LIN_2 , 
ADD_LIN_3, 
CITY , 
STATE , 
COUNTRY , 
PIN_COD , 
PERM_ADD_LIN_1 , 
PERM_ADD_LIN_2 , 
PERM_ADD_LIN_3 , 
PERM_CITY , 
PERM_STATE , 
PERM_COUNTRY , 
PERM_PIN_COD , 
POA_COU_VAL , 
MAST_POA_ID , 
TRANS_ID 
)

If POA_COU_VAL is 0 than POA_COU_VAL must be null and next value should be enter in TRANS_ID. How can I achieve this?

Upvotes: 1

Views: 8732

Answers (2)

GWu
GWu

Reputation: 2787

SQL*Loader WHEN clause has only limited boolean operators, especially OR is missing ( see http://docs.oracle.com/cd/B19306_01/server.102/b14215/ldr_control_file.htm#i1005657).

If you have to use SQL*Loader (and can't switch to external tables as APC suggested), you need to multiply the INTO sections for each part of your OR clause.

For example:

INTO TABLE TF_RTB9_DTL 
WHEN (01) = 'D'
FIELDS TERMINATED BY "~" OPTIONALLY ENCLOSED BY '"' 
TRAILING NULLCOLS 
( 
TRANS_TYP POSITION(3),
...      
...      
MAST_POA_ID
)
INTO TABLE TF_RTB9_DTL 
WHEN POA_COU_VAL = '0'
FIELDS TERMINATED BY "~" OPTIONALLY ENCLOSED BY '"' 
TRAILING NULLCOLS 
( 
TRANS_TYP POSITION(3),
...      
...      
MAST_POA_ID "NULL"
)

Or consider using the NULLIF function ( http://docs.oracle.com/cd/B19306_01/server.102/b14215/ldr_field_list.htm#i1009345 )

...
MAST_POA_ID NULLIF POA_COU_VAL = '0'
...

SQL*Loader is a a tricky tool, you'd most probably get quicker results with external tables, if you are able to use them (file has to be on the Oracle Server machine whereas SQL*Loader is able connect to any Oracle instance).

Upvotes: 2

APC
APC

Reputation: 146179

SQL*Loader is a program for loading large batches of data. It has only limited facilities for manipulating the data during the load process.

If you want to transform the data you should be using external tables instead. These are very similar to SQL*Loader (the definition uses virtually the same syntax but they present the data file to the database as a table. That means you can use SQL on it. So instead on invoking SQL*Loader from the command line you woudl run an insert statement like this:

insert into target_table
select * from external table;

Only in your situation you need to explode the project of the SELECT clause so you can include a CASE() statement to implement your additional logic.

Find out more about external tables here.

Upvotes: 4

Related Questions