Home » RDBMS Server » Server Utilities » Where clause in control file (Oracle 8i UNIX)
icon4.gif  Where clause in control file [message #479595] Mon, 18 October 2010 08:53 Go to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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 #479659 is a reply to message #479643] Mon, 18 October 2010 10:59 Go to previous messageGo to next message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
can we convert 5 digit item number into 4 digit item number. Basically I want to trim first digit of the item number.

SQL> with data as 
  2  (select 12345 val from dual union select 6789 from dual)
  3  select val, mod(val,10000) val2 from data
  4  /
       VAL       VAL2
---------- ----------
      6789       6789
     12345       2345

Regards
Michel
icon4.gif  Re: Where clause in control file [message #479690 is a reply to message #479659] Mon, 18 October 2010 12:57 Go to previous messageGo to next message
heyit
Messages: 14
Registered: May 2010
Location: US
Junior Member
Can we trim the value for a particular row, example:
If table is like:
item_no number,
num_val number,
account varchar2

I want to trim num_val from 5 digit to 4 digit (remove first digit only) where item_no = 1234.

Thanks!!
Re: Where clause in control file [message #479693 is a reply to message #479690] Mon, 18 October 2010 13:26 Go to previous messageGo to next message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this not what Barbara and I posted?

Regards
Michel

[Updated on: Mon, 18 October 2010 13:27]

Report message to a moderator

Re: Where clause in control file [message #479700 is a reply to message #479693] Mon, 18 October 2010 14:15 Go to previous message
Barbara Boehmer
Messages: 8922
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>


Previous Topic: To_number conversion
Next Topic: import error (urgent plz)
Goto Forum:
  


Current Time: Fri Sep 17 07:53:20 CDT 2021