wildcard for Sql*Loader??? [message #72544] |
Mon, 14 July 2003 10:25 |
sharon foley
Messages: 1 Registered: July 2003
|
Junior Member |
|
|
The following wildcard does not work in SQL*Loader. Does anyone know of a way to load only the data like N1xxxx?
APPEND INTO TABLE UNION_DED_PRELIM
WHEN (DEDCD LIKE 'N1%')
|
|
|
|
Re: wildcard for Sql*Loader??? [message #72574 is a reply to message #72544] |
Tue, 22 July 2003 16:13 |
|
Barbara Boehmer
Messages: 9101 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
I received your e-mail saying that it didn't solve your problem. Here is an example to demonstrate how to do it. I have used a fixed position text file, however it can also be done with a delimited text file. If this still doesn't solve your problem, please post more specifics, such as your table structure, text file structure, and control file.
SQL> -- test table:
SQL> DESC union_ded_prelim
Name Null? Type
----------------------------------------- -------- -------------------------
DEDCD VARCHAR2(10)
OTHER_COLUMN VARCHAR2(10)
.
.
contents of test_data.txt:
N123456789 SOMETHING
N198765432 WHATEVER
N234567890 NOT N1
.
.
-- beginning of test.ctl
LOAD DATA
INFILE 'test_data.txt'
APPEND
INTO TABLE union_ded_prelim
WHEN dedcd2 = 'N1'
TRAILING NULLCOLS
(dedcd2 FILLER POSITION (1:2),
dedcd POSITION ( 1:10),
other_column POSITION (12:21))
-- end of test.ctl
.
.
SQL> HOST sqlldr scott/tiger control=test.ctl log=test.log bad=test.bad
.
.
SQL> -- results:
SQL> SELECT * FROM union_ded_prelim
2 /
DEDCD OTHER_COLU
---------- ----------
N123456789 SOMETHING
N198765432 WHATEVER
|
|
|