SQLLDR - look up on table to find column value [message #403786] |
Mon, 18 May 2009 12:21  |
nidi_03
Messages: 10 Registered: May 2009 Location: US
|
Junior Member |
|
|
Hi,
I am loading table Emp using sqlldr. I need to populate the column Date_paid_key of table Emp by lookin up on another table dates. Sample data is as given below. How can I do it using sqlldr? I can load my csv in a temp table and then proceed. Any other suggestions?
Src.csv
EmpId EmpName Salary Date_paid
1 A 10 4/15/2009
2 B 20 4/18/2009
Lookup Table dates
Date Date_key
4/15/2009 1001
4/16/2009 1002
4/17/2009 1003
4/18/2009 1004
Target table Emp
EmpId EmpName Salary Date_paid_key
1 A 10 1001
2 B 20 1004
[EDITED by LF: applied [PRE] tags]
[Updated on: Mon, 18 May 2009 14:15] by Moderator Report message to a moderator
|
|
|
|
Re: SQLLDR - look up on table to find column value [message #403796 is a reply to message #403786] |
Mon, 18 May 2009 14:35   |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Here's SQL*Loader based solution.
First, let's create our environment:SQL> desc o_emp;
Name Null? Type
----------------------------------------------------------------- -------- ---------------
EMPID NUMBER
EMPNAME VARCHAR2(20)
SALARY NUMBER
DATE_PAID_KEY NUMBER
SQL> desc o_lookup;
Name Null? Type
----------------------------------------------------------------- -------- ---------------
DATE_KEY NUMBER
DATE_COL DATE
SQL> select * from o_lookup;
DATE_KEY DATE_COL
---------- ----------
1001 15.04.2009
1002 16.04.2009
1003 17.04.2009
1004 18.04.2009
SQL>
In order to find DATE_KEY from the lookup table, I'll create a rudimentary function:SQL> CREATE OR REPLACE FUNCTION o_test (par_date_paid IN DATE)
2 RETURN NUMBER
3 IS
4 retval O_LOOKUP.date_key%TYPE;
5 BEGIN
6 SELECT date_key INTO retval
7 FROM O_LOOKUP
8 WHERE date_col = par_date_paid;
9 RETURN (retval);
10 END;
11 /
Function created.
SQL>
This is a control file (I have included input sample data into it, for my own convenience). Note the way function is used in order to fill in the missing value:load data
infile *
replace
into table o_emp
fields terminated by ','
trailing nullcols
(empid,
empname,
salary,
date_paid_key "o_test(to_date(:date_paid_key, 'mm/dd/yyyy'))"
)
begindata
1,A,10,4/15/2009
2,B,20,4/18/2009
Finally, loading session and the result:SQL> $sqlldr scott/tiger control=test.ctl log=test.log
SQL*Loader: Release 10.2.0.1.0 - Production on Pon Svi 18 21:33:15 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 1
Commit point reached - logical record count 2
SQL> select * from o_emp;
EMPID EMPNAME SALARY DATE_PAID_KEY
---------- -------------------- ---------- -------------
1 A 10 1001
2 B 20 1004
SQL>
|
|
|
|
|