Max and Min value as input to DB2 stage
Moderators: chulett, rschirm, roy
Max and Min value as input to DB2 stage
Hi everyone. I want to be able to query for a max and min value each time a job is run, and then pass the max and min value into the DB2 stage as input parameters to the query for that stage, but of course, I can't create an input link into a DB2 stage so what would be the best way to go about this? Am I thinking about this in the wrong way? Help. Thanks!!
Nicole
Nicole
Use it as a subselect sql statement. Something like
Something like that.
Code: Select all
Select a, b, c
from mytable
where a between (select max(x) from yourtable) and (select min(x) from your table);
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
That is a good idea...BUT the max and min values are on a table which is on a totally different platform. Of course, the obvious option is to query for the max and min and plug them in before running the job, but we were trying to find a way to do it within the one job.
DSguru2B wrote:Use it as a subselect sql statement. Something like
Code: Select all
Select a, b, c from mytable where a between (select max(x) from yourtable) and (select min(x) from your tab ...[/quote]
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
I don't think this one will work because we still have a SQL statement in the DB2 Stage, but we want to run the first sql to get the max and min to pass as values in the where clause of the sql in the DB2 Stage.
Thank you for the suggestion.
Thank you for the suggestion.
Sainath.Srinivasan wrote:Create the sql into a file in the pre-job and use external SQL file in the job
How huge is your source data? If its not that huge, you can get your min and max value and store it in a hashed file and instead of using it in the where clause, use the min and max to filter the data in the constraint section of the transformer.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Actually it could be done using an External Source stage, but I wondered what you had in mind. But there's no advantage in a parallel job in using a hashed file - a sequential file would do as well - because the entire reference data set is imported into memory.
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.
I was going to say, if the min and max values are numerical values, then the OP could get them and create the values in between and load it into a lookup file set or perhaps a dataset. Then do a lookup/join and filter the records.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.