extract data from two different databases

pongal · Post by **pongal** » Fri Aug 19, 2005 5:00 am

Hi,

I have a requirement in such a way that, i want to extract the data from two different database tables like

SELECT A.INDATE,A.PRJNAME,B.SRID
FROM
PRIME.PROJECT A,
PRIME.SECTION B,
A.PRJID = B.SRID AND
A.PRID > (SELECT MAX(LAST_EXTID) FROM VEND_EXTRACT)
ORDER BY 1;
Here LAST_EXTID is the column name from some other database VEND_EXTRACT.

my question is how to link second database VEND_EXTRACT and pull the data through oracle_enterprise stage.

jasper · Post by **jasper** » Fri Aug 19, 2005 5:18 am

lots of different options , best option depends on a lot of factors(like number of records in the tables)

-easiest: 2 oracle stage one for the table, one for the max . Filter records in the job (disadvantage: you select all records from the table which could be a lot of overhead)
- in a workflow first select your max-field, pass this as parameter to the next job where the query is like SELECT A.INDATE,A.PRJNAME,B.SRID
FROM
PRIME.PROJECT A,
PRIME.SECTION B,
A.PRJID = B.SRID AND
A.PRID > #PARAMETER#
- if this is the way to take a delta , you can also make a config table where you hold the last processed id and use this is parameter.

...

pongal · Post by **pongal** » Fri Aug 19, 2005 9:35 am

hi jasper,
your said logic seems to good and i have doubt that how to pass parameter from one to another job.
can you explain me breifly

pongal · Post by **pongal** » Sun Aug 21, 2005 6:20 am

i have extracted MAX(LAST_EXT_ID) from first job and how to pass that value as a parameter to the second job's query where i need to do filter condition like this
SELECT A.INDATE,A.PRJNAME,B.SRID
FROM
PRIME.PROJECT A,
PRIME.SECTION B,
A.PRJID = B.SRID AND
A.PRID > #PARAMETER#

can anybody help me out

is there anyother alternative to bring up value from different database field

Thanks
pongal

elavenil · Post by **elavenil** » Sun Aug 21, 2005 8:39 am

Your DBA could help by creating DBLink to join two databases. Using DBLink may degrade the performance of the query.

HTWH.

Regards
Saravanan

jasper · Post by **jasper** » Mon Aug 22, 2005 2:37 am

the way we normally do this is to put it an a hashfile and read this file in the workflow(user-variable stage that executes a readfromhash-function)

pongal · Post by **pongal** » Mon Aug 22, 2005 12:19 pm

jasper,
here hashfile is disabled in palette if you parallel job.
and also i am extracting data from source database table, so how do we need to pass that parameter in a query?
can you explain more on this

ray.wurlod · Post by **ray.wurlod** » Mon Aug 22, 2005 4:42 pm

You use job parameter references in a WHERE clause just like you use them in any passive stage, the job parameter name - correctly spelled and correctly cased - surrounded by "#" characters. The examples earlier illustrate the technique.