Page 1 of 1
Filter Stage
Posted: Fri Nov 24, 2006 6:39 am
by coehcl
hi,
Is it possible to specify the output of SQL Query in the WHERE Property of Filter stage?
Posted: Fri Nov 24, 2006 7:43 am
by Nageshsunkoji
I think, it is not possible to do this directly in the where condition of filter stage. There should be a intermediate dataset/sequentail file to store the result of your SQL and then perform filter on the required column.
Posted: Fri Nov 24, 2006 8:00 am
by coehcl
Our requirement is that we need to pass only those records, within which a column is less than a particular value, for instance 10. But this 10 has to be parameterised, and this value is a result of a SQL query.
Any help will be highly appreciated.
Posted: Fri Nov 24, 2006 3:59 pm
by splayer
I think it can be done very simply by using a job parameter. Store the result of the query in the job parameter, and use that in the WHERE clause as:
= #job_parameter#
You have to define the job parameter in your job first.
#job_parameter# is a step in the right direction, but...
Posted: Fri Nov 24, 2006 5:45 pm
by jgreve
splayer wrote:
= #job_parameter#
You have to define the job parameter in your job first.
The #job_parameter# is easy enough to understand for the filter stage. I'm more curious about an
easy way to do something like this on the
Code: Select all
JobProperties.Paramters tab:
Parameter Name: job_parameter
Prompt: The job parameter
Type: Integer
Default Value: select 1+max(seq_foo) from somedb.sometable;
Description: Hit the database before we run this job.
note: I know it doesn't work that way. I haven't dug deeply enough into this yet, but I imagine if I _had_ to get a param at runtime from some database table, I would need a "getter" datastage job, my main datastage job, and a job-sequence to weave them together and feed the result from my "getter" job into my "main" job. Am I on the right track here?
(it is _really_ kind of a shame that the conductor doesn't have a "blackboard" to allow (essentially) global variables visible across all players).
Re: #job_parameter# is a step in the right direction, but...
Posted: Fri Nov 24, 2006 8:44 pm
by chulett
jgreve wrote:Am I on the right track here?
Yup. One job to fetch the value and stash it somewhere. Simplest place seems to be in the USERSTATUS area each job has. Then a Sequence can auto-fetch that value into a Parameter in a downstream job:
You will need a simple custom routine to allow you to call the function in a derivation and it is a 'last one in wins' structure:
Code: Select all
FUNCTION CallSetUserStatus(Arg1)
Call DSSetUserStatus(Arg1)
Ans=Arg1
Simple as that, two whole lines. The value is written to the User Status area and passed back out. A Server job can do this easily enough.
You could also write the value to a flat file and use a routine to read it and pass the value back out. Or write it to a hashed file with a hard-coded key. The routine that fetches the value can either be used in a
Routine Activity stage so the value can be passed in as a job parameter, or the routine can be used in the
Initial Value of a stage variable if you just need to get it into a job for use in derivations. If a hashed file is used for storage and the desire is to seed a Stage Variable then the supplied sdk routine
UtilityHashLookup can be used to retrieve it in most cases.
Note there is some Server specific answers in here, but much can also be leveraged by Parallel jobs.
Posted: Fri Nov 24, 2006 11:38 pm
by Sreedhar
Before passing the values to the filter stage, populate a column, with the output of the SQL query, then filter on the temporarily created column, which has the resultant of the SQL query.
At a later stage just drop the temporarily created column.