Home » RDBMS Server » Server Utilities » Referencing data from earlier lines or immediate previous line ...
Referencing data from earlier lines or immediate previous line ... [message #115211] Fri, 08 April 2005 03:09 Go to next message
peketi
Messages: 4
Registered: April 2005
Junior Member
I am using Control files to load data from a text file onto multiple tables. I have a case where I need to refer for a data loaded onto table 'A' in table 'B'. Data to be loaded is not delimited and hence I am using POSITIONS to load the data. Here is sample data, a classical EMP/DEPT tables:

KING PRESIDENT 20021212 01 50 Computers
BLAKE MANAGER 20021212 02 50 Computers

EMP Table would be like this :
EMPNO ENAME JOB HIREDATE MGR DEPT
01 KING PRESIDENT 12-DEC-2002 0 50
02 BLAKE MANAGER 12-DEC-2002 0 50

But, I would like to set the field 'mgr' as 01 for BLAKE.

This example is taken just to define the problem as data is to be kept confidential.

Any help in this regard would be highly appreciated.

Thanks in advance
Peketi

Report message to a moderator

Re: Referencing data from earlier lines or immediate previous line ... [message #115252 is a reply to message #115211] Fri, 08 April 2005 07:57 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
how about using a trigger to fire in the background.
load the data as is.
after insert on tableB, the trigger fires and fetches the data (maangerID) from tableA, updates tableB.
i am not a big fan of triggers.
or how about a stored proc that can be executed after load, that does the same work as above.?

Report message to a moderator

Re: Referencing data from earlier lines or immediate previous line ... [message #115261 is a reply to message #115252] Fri, 08 April 2005 09:23 Go to previous messageGo to next message
peketi
Messages: 4
Registered: April 2005
Junior Member
Stored proc, my coding guidelines would not allow, but triggers yes, I would try that. Thanks for the reply.

Would still appreciate if I could get some other technique, if exists, regarding the referencing data between two tables.

Report message to a moderator

Re: Referencing data from earlier lines or immediate previous line ... [message #115534 is a reply to message #115211] Tue, 12 April 2005 09:27 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
You are allowed to write a trigger but not a stored proc?

What if you wrote a trigger that called a stored proc?

After the data is loaded into a staging table it would be doable with a query using the analytical lead and lag functions...

Report message to a moderator

Previous Topic: Concatenate fields in sql loader
Next Topic: update ASCII FIle
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jul 04 06:43:19 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.