| SQL Loader with WHEN and DISCARD [message #567238] |
Tue, 25 September 2012 09:20  |
 |
Milenna
Messages: 4
Registered: September 2012
Location: USA
|
Junior Member |
|
|
Hello!
My data file contains records with RECORD_TYPE '01', '02', '03' and '04' in position (30:31)
I use sql loader to load this data into a table. I need to load ONLY rows with RECORD_TYPE ='04'. I have to output all the other rows into a file, so I decided to use DISCARD file.
Now, those with RECORD_TYPE ='4' have to be loaded into different columns depending on the value in position (267:268).
So, my ctl file should look something like:
WHEN (30:31) = '04'
into table MYDATA
WHEN (267:268) != 'O '
into table MYDATA
WHEN (267:268) = 'O '
and whatever is not '04' goes to discard file.
I tried to use
into table MYDATA
WHEN (30:31) = '04' and (267:268) != 'O '
into table MYDATA
WHEN (30:31) = '04' and (267:268) = 'O '
but I don't get the right result in terms of the discard file.
Is there any way to put together all these conditions?
Please advice
THanks!
|
|
|
|
|
|
|
|
| Re: SQL Loader with WHEN and DISCARD [message #567332 is a reply to message #567247] |
Wed, 26 September 2012 13:53  |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
In the following demonstration, I used (10:11) and (17:18) instead of your positions to limit the length of test data for easier reading. It appears that the discard file is as expected. Please indicate what you expect instead.
-- data_file.dat:
abcdefghi04lmnopqrstuvwxyz
abcdefghi04lmnopO stuvwxyz
abcdefghi01lmnopqrstuvwxyz
abcdefghi02lmnopO stuvwxyz
-- test.ctl:
load data
infile data_file.dat
into table a_table
when (10:11) = '04' and (17:18) != 'O '
fields
(record_type position (10:11),
not_o position (17:18))
into table a_table
when (10:11) = '04' and (17:18) = 'O '
fields
(record_type position (10:11),
is_o position (17:18))
-- table, load, and resulting data in table:
SCOTT@orcl_11gR2> create table a_table
2 (record_type number,
3 not_o varchar2(2),
4 is_o varchar2(2))
5 /
Table created.
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log discard=test.dsc
SCOTT@orcl_11gR2> select * from a_table
2 /
RECORD_TYPE NO IS
----------- -- --
4 qr
4 O
2 rows selected.
-- test.dsc:
abcdefghi01lmnopqrstuvwxyz
abcdefghi02lmnopO stuvwxyz
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
