| FILLER field in SQL*Loader [message #173164] |
Fri, 19 May 2006 14:41  |
hibyte
Messages: 11
Registered: March 2006
|
Junior Member |
|
|
I've a table with 3 columns but input data files with 5 fields.
One of the column gets loaded from multiple columns (mutually exclusive) based on the value. When I specify the 2 extra fields in the file as FILLER, Can I reference them in the "decode"? I'm getting an error.
Table:
Create table Dept(
DeptID number,
DeptName varchar2(20),
SubDept varchar2(20)
)
Input File:
DeptID|DeptName|SubDept1|SubDept2|SubDept3
10|ACCTS|ACCT PAYABLE|US|SF
20|FINANCE|N.A.|CFO-US|N.A.
30|ACCTS|ACCT RECEIV|JP|TO
In the case of 1st & 3rd record SubDept should be loaded by SubDept1(Field #3) where as for 2nd record it should be SubDept2(Field #4)
I tried with Control file as:
load data
infile 'dept.out'
into table DEPT
FIELDS TERMINATED BY '\t'
(
DeptID integer external,
DeptName char,
SubDept1 FILLER char,
SubDept2 "decode(:subdept1, 'N.A.', :subdept2, :subdept1)",
SubDept3 FILLER char
)
But got an error as can't bind variable.
Thanks,
HiBy
|
|
|
|
|
|
| Re: FILLER field in SQL*Loader [message #173319 is a reply to message #173164] |
Sun, 21 May 2006 17:08  |
 |
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You can use either of the following 2 methods. I used table name dept2 instead of dept for the demonstrations.
-- test.ctl:
load data
infile 'dept.out'
into table DEPT2
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
DeptID integer external,
DeptName char,
SubDept1 char,
SubDept2 char,
SubDept3 char
,SubDept "decode(:subdept1, 'N.A.', :subdept2, :subdept1)"
)
SCOTT@10gXE> create table Dept2
2 (DeptID number,
3 DeptName varchar2(20),
4 SubDept1 varchar2(20),
5 SubDept2 varchar2(20),
6 SubDept3 varchar2(20),
7 SubDept varchar2(20))
8 /
Table created.
SCOTT@10gXE> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
SCOTT@10gXE> ALTER TABLE dept2 DROP COLUMN subdept1
2 /
Table altered.
SCOTT@10gXE> ALTER TABLE dept2 DROP COLUMN subdept2
2 /
Table altered.
SCOTT@10gXE> ALTER TABLE dept2 DROP COLUMN subdept3
2 /
Table altered.
SCOTT@10gXE> SELECT * FROM dept2
2 /
DEPTID DEPTNAME SUBDEPT
---------- -------------------- --------------------
10 ACCTS ACCT PAYABLE
20 FINANCE CFO-US
30 ACCTS ACCT RECEIV
SCOTT@10gXE>
-- test.ctl:
load data
infile 'dept.out'
into table DEPT2
WHEN subdept1 = 'N.A.'
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
DeptID integer external,
DeptName char,
SubDept1 FILLER char,
SubDept char
)
into table DEPT2
WHEN subdept <> 'N.A.'
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
DeptID POSITION (1) integer external,
DeptName char,
SubDept char
)
SCOTT@10gXE> create table Dept2
2 (DeptID number,
3 DeptName varchar2(20),
4 SubDept varchar2(20))
5 /
Table created.
SCOTT@10gXE> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
SCOTT@10gXE> SELECT * FROM dept2
2 /
DEPTID DEPTNAME SUBDEPT
---------- -------------------- --------------------
20 FINANCE CFO-US
10 ACCTS ACCT PAYABLE
30 ACCTS ACCT RECEIV
SCOTT@10gXE>
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
