DSXchange

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.

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.

...

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

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

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

HTWH.

Regards
Saravanan

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)

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

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.