| When clause in Sql*Loader [message #120344] |
Thu, 19 May 2005 03:53  |
srisaravankumar
Messages: 10
Registered: May 2005
|
Junior Member |
|
|
Hi,
How to specify multiple criteria in gthe when clause of sql*loader
i want something like
WHEN (sno='2') OR (sno='6') OR (sno='3')
I am getting a syntax error. It looks like I can't put OR operator in my where clause. What is the alternative for doing achiving this result??
|
|
|
|
|
|
| Re: When clause in Sql*Loader [message #120432 is a reply to message #120344] |
Thu, 19 May 2005 13:23  |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You can use a separate into clause for each when clause. You can copy the following all-in-one test script to your system and start it from the scott/tiger schema, to see what I mean. If I recall correctly, this only works with fixed format data files. If you are using a delimited data file, then I belive you will need to do three separate runs of SQL*Loader with three separate control files.
store set saved_settings replace
set echo off feedback off heading off pagesize 0 verify off
spool test.dat
prompt 2 a
prompt 6 b
prompt 3 c
prompt 10d
spool off
spool test.ctl
prompt load data
prompt infile 'test.dat'
prompt append
prompt into table test_tab
prompt when sno = '2'
prompt (sno position (1:2),
prompt other_col position (3:3))
prompt into table test_tab
prompt when sno = '6'
prompt (sno position (1:2),
prompt other_col position (3:3))
prompt into table test_tab
prompt when sno = '3'
prompt (sno position (1:2),
prompt other_col position (3:3))
spool off
start saved_settings
create table test_tab (sno number, other_col varchar2(1))
/
host sqlldr scott/tiger control=test.ctl log=test.log
select * from test_tab
/
drop table test_tab
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
