Home » RDBMS Server » Server Utilities » Where clause in control file (Oracle 8i UNIX)
 Where clause in control file [message #479595] |
Mon, 18 October 2010 08:53  |
heyit
Messages: 14
Registered: May 2010
Location: US
|
Junior Member |
|
|
Hi All,
This is for SQL Loader experts.
Is it possible to use where clause to discard one of the item which is not able to fit into column because of the length constraint.
Example:
1. Remove first digit from the item_ID where ITEM_ID IN (12345)
2. Do not load data WHERE ITEM_ID IN (12345)
An example control file would be really appreciated.
Thanks!!
|
|
|
|
| Re: Where clause in control file [message #479617 is a reply to message #479595] |
Mon, 18 October 2010 09:38  |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Any values that do not fit will automatically be rejected. Alternatively, you can create a function to check the length and use only the 2nd, 3rd, 4th, and 5th numbers from those that are too large, then use that function in your control file, as demonstrated below. You should also upgrade from 8i as it is outdated.
-- test.ctl:
load data
infile *
into table test
fields terminated by ','
trailing nullcols
(item_id char "fix_data (:item_id)")
begindata:
12345,
54321,
6789,
9876,
SCOTT@orcl_11gR2> create table test
2 (item_id number (4))
3 /
Table created.
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log
SCOTT@orcl_11gR2> create or replace function fix_data
2 (p_number in varchar2)
3 return number
4 as
5 begin
6 if length (p_number) > 4 then
7 return to_number (substr (p_number, 2, 4));
8 else
9 return to_number (p_number);
10 end if;
11 end fix_data;
12 /
Function created.
SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> select * from test
2 /
ITEM_ID
----------
2345
4321
6789
9876
4 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
| Re: Where clause in control file [message #479643 is a reply to message #479617] |
Mon, 18 October 2010 10:14 |
heyit
Messages: 14
Registered: May 2010
Location: US
|
Junior Member |
|
|
Thank you so much for answer with an example.
You mentioned that "Any values that do not fit will automatically be rejected." In order to acheive that, do we need to specify anything in control file (any parameter SKIP etc.) or it is the way SQL Loader works? Because my script failed because of that.
My control files looks like:
$ more smaple.ctl
load data
insert
into table SALES
(
No position(1:6) integer external,
RECPT position(7:36) char,
RETAIL position(37:43) decimal external,
COST position(45:53) decimal external
)
Also, I want to know by using function (or any other way), can we convert 5 digit item number into 4 digit item number. Basically I
want to trim first digit of the item number.
Thank again for help.
|
|
|
|
| Re: Where clause in control file [message #479651 is a reply to message #479643] |
Mon, 18 October 2010 10:41 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
heyit wrote on Mon, 18 October 2010 08:14
You mentioned that "Any values that do not fit will automatically be rejected." In order to acheive that, do we need to specify anything in control file (any parameter SKIP etc.) or it is the way SQL Loader works? Because my script failed because of that.
That is how SQL*Loader works. Your script failed due to something else. You need to post a complete test case with matching data, control file, create table statement, and the command that you used to load, as I did. Also, post your log file.
heyit wrote on Mon, 18 October 2010 08:14
Also, I want to know by using function (or any other way), can we convert 5 digit item number into 4 digit item number. Basically I want to trim first digit of the item number.
I already demonstrated that.
|
|
|
|
|
|
|
|
|
|
| Re: Where clause in control file [message #479700 is a reply to message #479693] |
Mon, 18 October 2010 14:15 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If you mean that for rows where item_no is the specific value 1234, then you want to trim the the first digit from any 5-digit values in another column num_val, but for rows where item_no is not 1234, then you do not want to trim num_val, there are at least two ways to do that. I have demonstrated both below, but I am testing in 11g and they may not work in 8i. The first method uses decode to check the value of item_no and the second method uses when clauses to check the value of item_no.
You have posted conflicting table structures and such. It is difficult to tell if you are still talking about the same problem. If you are talking about the same problem, then please be consistent. If this is a different problem, then please put it in a different thread or if it is similar at least indicate that it is a similar problem for a different table.
-- test.ctl:
load data
infile *
into table test
fields terminated by ','
trailing nullcols
(item_no,
num_val char "decode (:item_no, 1234, fix_data (:num_val), :num_val)",
account)
begindata:
1234,56789,account1,
4321,98765,account2,
SCOTT@orcl_11gR2> create table test
2 (item_no number (4),
3 num_val number (5),
4 account varchar2(10))
5 /
Table created.
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log
SCOTT@orcl_11gR2> create or replace function fix_data
2 (p_number in varchar2)
3 return number
4 as
5 begin
6 if length (p_number) > 4 then
7 return to_number (substr (p_number, 2, 4));
8 else
9 return to_number (p_number);
10 end if;
11 end fix_data;
12 /
Function created.
SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> select * from test
2 /
ITEM_NO NUM_VAL ACCOUNT
---------- ---------- ----------
1234 6789 account1
4321 98765 account2
2 rows selected.
SCOTT@orcl_11gR2>
-- test.ctl:
load data
infile *
into table test
when (1:4) = '1234'
fields terminated by ','
trailing nullcols
(item_no,
num_val char "fix_data (:num_val)",
account)
into table test
when (1:4) != '1234'
fields terminated by ','
trailing nullcols
(item_no position (1),
num_val,
account)
begindata:
1234,56789,account1,
4321,98765,account2,
SCOTT@orcl_11gR2> create table test
2 (item_no number (4),
3 num_val number (5),
4 account varchar2(10))
5 /
Table created.
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log
SCOTT@orcl_11gR2> create or replace function fix_data
2 (p_number in varchar2)
3 return number
4 as
5 begin
6 if length (p_number) > 4 then
7 return to_number (substr (p_number, 2, 4));
8 else
9 return to_number (p_number);
10 end if;
11 end fix_data;
12 /
Function created.
SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> select * from test
2 /
ITEM_NO NUM_VAL ACCOUNT
---------- ---------- ----------
1234 6789 account1
4321 98765 account2
2 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
Goto Forum:
Current Time: Mon Dec 23 09:52:15 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
