Home » RDBMS Server » Server Utilities » SQLLDR - look up on table to find column value
SQLLDR - look up on table to find column value [message #403786] Mon, 18 May 2009 12:21 Go to next message
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 #403790 is a reply to message #403786] Mon, 18 May 2009 13:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use external table.

Regards
Michel
Re: SQLLDR - look up on table to find column value [message #403796 is a reply to message #403786] Mon, 18 May 2009 14:35 Go to previous messageGo to next message
Littlefoot
Messages: 20891
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>
Re: SQLLDR - look up on table to find column value [message #411780 is a reply to message #403796] Mon, 06 July 2009 12:56 Go to previous messageGo to next message
nidi_03
Messages: 10
Registered: May 2009
Location: US
Junior Member
Thanks a lot. This sure was helpful.
Re: SQLLDR - look up on table to find column value [message #411782 is a reply to message #411780] Mon, 06 July 2009 13:13 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Assuming of course that your lookup table's dates are truncated (ie. lack the time component).
Previous Topic: Parameters / Variables in SQL Loader Control File
Next Topic: exp n imp
Goto Forum:
  


Current Time: Sun Dec 04 06:52:04 CST 2016

Total time taken to generate the page: 0.05505 seconds