Dynamically generate where clause-Urgent

[bakul]

I have a situation where I have to retrieve values for the same fields from a DB2 database every time but the where condition depends on the input data.
Is there any way to dynamically change the where clause in a DB2/UDB or ODBC stage used for lookup?

Thanks and regards,
Bakul

[vigneshra]

Your question is not very clear in the first place. But as far I understood, I tell you something which you have to check if useful or not. You generate whatever the where condition you want to generate from the input data in a seperate job and write it into an OS file. Then in the second job, you read the file and assign it as a parameter to the job. You include that parameter in the userdefined query in the extraction DB2 plugin. I think this will serve your need.

Vignesh.

[Amos.Rosmarin]

Hi,

What do you mean by changing the sql, I guess it's not just changing the value it's like doing for one steam row A=? and for another B>?
The only way doing so is to use user defined sql and replace the input parameter with a valid sql statment .
It's ugly but it will work. Don't use it for a lot of data - performance will be lousy.


HTH,
Amos

[mleroux]

You can use a where field_name = ? clause in user-defined SQL the same way as DataStage does with a generated SQL query. Then, hook up a field to it via the GUI designer in your transformer.

[bakul]

I'll try to explain what I want to do.

I have to generate 2 types of queries based on an input parameter.
In one query , I need to select the rows with field 'STATUS' = 'A' .
In another query, I need to select the rows if the 'STATUS' is either 'A' or 'B' or 'C'.

The query to run depends on the input parameter. If the input is 'A', I use query 1 and if input is 'D' I use query 2. I need to do this in the same job.

I also need to count the number of records returned by such a lookup.

Is there any way I can accomplish this?


Thanks and Regards,
Bakul

[crouse]

If things are exactly as you state them, the first query is a subset of the second query.

Since the first select is a subset of the second, you could always use the second select. Filter the rows in the transformer stage with a constraint.

What/who needs to tell how many rows were returned? The answer determines how you should count the rows. Can you count the rows after the job runs, or do you need to do it inside the job?

-Craig

[kduke]

You need 2 lookups. Have 2 outputs with constraints for each situation. Another way maybe using stage variables with if STATUS='A' set and second set of assignments if that is not true and the second situation 'STATUS' is either 'A' or 'B' or 'C' is true.

[vmcburney]

Consider an intermediate step where you move your lookup data into a hash file. Include your aggregation and a design that lets you use one lookup instead of two. First you dump in all the rows where the STATUS is equal to A with aggregation and a count field. Next you dump in where STATUS is A, B or C with aggregation. That way you run the aggregation function against the database against the entire recordset instead of running millions of individual aggregations through a lookup link.

[ray.wurlod]

I have a situation where I have to retrieve values for the same fields from a DB2 database every time but the where condition depends on the input data.
Is there any way to dynamically change the where clause in a DB2/UDB or ODBC stage used for lookup?

Thanks and regards,
Bakul

You can use job parameters for any constant in the WHERE clause.

[ailuro]

In fact you can pass where clause conditions as job parameters (including column names, operators and constants) e.g., STATUS = 'A', STATUS IN ('A', 'B', 'C').

Just define a job parameter, say CONDITION, and put #CONDITION# in your ODBC stage's Outputs > Selection > WHERE clause.

[bakul]

Thanks all for the response! I have used job parameters and that seems to help!

[chulett]

Another option to keep in mind... for the future.

You can dynamically generate your SQL statement and write it out to a flat file. Then, in your stage, set the Update Action to 'User Defined SQL File'. What you then put in the SQL area is the pathname of the file.

Not something I've used alot, but it has come in handy a time or two.