Hi,
I need to fetch rows from an oracle enterprise stage, my mapping document looks something like this,
select Table.ID Where( DATASET.COL1|| '_' ||DATASET.COL2|| '_' |||DATASET.COL3) = Table.UNQ_ID
Is it possible to use the value coming from a dataset as a filter to my select statement.?
Thanks.
Use Value from Flat File on Where Clause in Oracle Stage
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
It is possible, but the question is how you should accomplish it.
You could use a sparse lookup, whereby you pass an individual SQL query per input row. This can be highly inefficient when you have many rows. Generally these are only used when the reference data is far larger than the input data.
To do a sparse lookup you need your lookup stage to be connecting directly to the database as a reference link, and then in the DB stage, you select a sparse lookup type. You can then reference the input columns that you wish to use by predicating them with the word ORCHESTRATE. In your example your query would become
Or you could combine the columns into a single field prior to the lookup (eg in a transformer stage) so it is just
But as I suggested earlier you should only use this approach if the input columns are dwarfed by the reference data eg 1% of the total reference table, and then only if the input records are not that numerous.
Depending on you system resources, you would be better reading the required data from the reference table and performing just a normal lookup, by combining your input columns into a single value and comparing that against the UNQ_ID.
Or if you are trying to limit the data in your reference table to a subset when executing then you would need to know the values of the input columns prior to starting the job and use a parameter in the sql query to filter the information.
EDIT: Or what Craig said.
You could use a sparse lookup, whereby you pass an individual SQL query per input row. This can be highly inefficient when you have many rows. Generally these are only used when the reference data is far larger than the input data.
To do a sparse lookup you need your lookup stage to be connecting directly to the database as a reference link, and then in the DB stage, you select a sparse lookup type. You can then reference the input columns that you wish to use by predicating them with the word ORCHESTRATE. In your example your query would become
Code: Select all
select Table.ID Where( ORCHESTRATE.COL1|| '_' ||ORCHESTRATE.COL2|| '_' |||ORCHESTRATE.COL3) = Table.UNQ_ID
Code: Select all
select Table.ID Where ORCHESTRATE.LKP_COL = Table.UNQ_ID
Depending on you system resources, you would be better reading the required data from the reference table and performing just a normal lookup, by combining your input columns into a single value and comparing that against the UNQ_ID.
Or if you are trying to limit the data in your reference table to a subset when executing then you would need to know the values of the input columns prior to starting the job and use a parameter in the sql query to filter the information.
EDIT: Or what Craig said.
Last edited by ShaneMuir on Fri Jul 03, 2015 7:25 am, edited 1 time in total.