Dynamically generate where clause-Urgent
Moderators: chulett, rschirm, roy
Dynamically generate where clause-Urgent
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
Is there any way to dynamically change the where clause in a DB2/UDB or ODBC stage used for lookup?
Thanks and regards,
Bakul
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.
Vignesh.
-
- Premium Member
- Posts: 385
- Joined: Tue Oct 07, 2003 4:55 am
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
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
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
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
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
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
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: Dynamically generate where clause-Urgent
You can use job parameters for any constant in the WHERE clause.bakul wrote: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
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers