Use Value from Flat File on Where Clause in Oracle Stage

[Kel]

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.

[priyadarshikunal]

a little confused on what you are trying to achieve. A little explanation is required.

[chulett]

You'd need to build the string before the job starts and pass it in as a parameter. That or build the entire SELECT in a file and have the stage use that file.

[ShaneMuir]

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

Code: Select all

select Table.ID Where( ORCHESTRATE.COL1|| '_' ||ORCHESTRATE.COL2|| '_' |||ORCHESTRATE.COL3) = Table.UNQ_ID 

Or you could combine the columns into a single field prior to the lookup (eg in a transformer stage) so it is just

Code: Select all

select Table.ID Where  ORCHESTRATE.LKP_COL = Table.UNQ_ID 

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.

[chulett]

Interesting, didn't consider that this might be a lookup. My response was based on your rather sparse post making me think this was the source in your job.

Or what Shane said.

[ShaneMuir]

Interesting, didn't consider that this might be a lookup. My response was based on your rather sparse post making me think this was the source in your job.

I was basing that assumption on the OP thread title.

[rkashyap]

Note ... If you build the filtration criteria externally and pass it as a parameter to the Oracle, then see this link to handle single quotes.