DSXchange

I need to use the results from one Oracle stage to be used to filter the select statement on a following oracle stage. How can I use input on an oracle stage to filter the select statement?

If both the tables are from same database then do it in Sql.

Else you can do lookup using transformer

More details:

Table A's output is already being transformed, altering the data before it is joined with Table B. I am currently querying table b into a hash table, then joining it to Table A in a Transform stage.

The problem is that the query on table B is a full table scan... because I have no criteria on it. Pulling a full table scan into a hash before using a transform to select a handful of records is terribly inefficient.

Can you not load the results from Table A into a work table so that downstream from there you can join it to Table B to constrain the query?

If both the table are in different database, I believe the best way would be to do it in a query.

Ask your DBA to create a dblink for you.

As I understand the replies:

If same database, you must join inside the original SQL (an option, so long as you are NOT altering the data of one query via Datastage stages prior to using it as criteria for a later SQL)

If different databases of the same type, oracle dblinks can be used.

If two seperate database types are being used, a load table can be used as a work around.

So the answer is NO, Datastage does not have a way of filtering a query based on input from upstream.

I need another workaround, similar issue.

I am running a stored procedure on an oracle database which writes a CLOB file to a table, then returns a pointer to where the CLOB was written.

After executing the procedure in the stored_procedure stage, and sending the output to a HASH, I need to pull data from the CLOB into datastage for processing.

Pulling data into the CLOB is not the issue, the question is, how do I filter my query to pull the clob I need, based on the pointer index returned from the Stored Procedure Stage?

Stored Procedure->index->query based on index?

If datastage does not support filtering a database query based on criteria from an input link, what is my workaround?


Edit: I think my solution might be to Create two jobs and a Sequence. The first job executes the procedure and returns the values to the Sequence. The sequence passes the values to the second job, which then uses the Parameters the sequence passed it to filter the query against the CLOB table.