Substituting a column value in transformer

[tanaya.deshpande@tieto.co]

HI

I have a sql query in input column.I want to substitute parameter value in the where clause of the query ...by using stage variable How can I acheive this..

[hemachandra.m]

Could you please little brief about your requirement

Which stage you are performing sql query and where do you want to pass the parameter to the "where" clause.

In general SQL query will be written in any database stages, but you want to pass the parameter from stagevariable. But stagevariable concept in Transformer stage.

[tanaya.deshpande@tieto.co]

I am extracting the query from SQL server stage (The query is a column value) and I want to replace a where condition by a parameter value....
For example the column value can be

Select studNo,Subject
from Student
Where StudentNo = :varstudno

so now I want to substitute a parametr value in :varstudno.

How can this be done in a transformer by stage variable

[tanaya.deshpande@tieto.co]

This Sqlquery is a column value of a table which is being extracted by another query by using SQLserevr stage

[Sainath.Srinivasan]

Why don't you write the query and bind variable into two separate columns and with no field delimiter ?

[tanaya.deshpande@tieto.co]

can u pls elaborate with an example

[chulett]

How can this be done in a transformer by stage variable

It can't be. You can only bind in job parameters and thus values passed into the job. You may, however, be able to take your value and build the fully fleshed out sql into a file and then set the stage that you are looking to bind that into to load the SQL it uses from that file.

[hemachandra.m]

you mean to say, there is a SQL query stored in a table and you are using SQL stage to extarcing this column (sql query) and this query is passed to Transformer stage. In Transformer stage you want to replace the Where conditon Varible with values from parameter.

Is what your requirement?

[tanaya.deshpande@tieto.co]

yes that is it ...

I am fetching a column value from a table(the column value is a query) from sql server stage .

AS I stated erlier lets take an example.
suppose this query is the column value I am fetching

select studentname,Rank
from student
where studno = :varstudno;

Now I want to replace :varstudno by a job parameter in the transformer

[Sainath.Srinivasan]

Why are you using such a dynamic query ?

Downstream dataflow will never have clear picture on what and how many columns will be propagated.

Also what if the query needs a different parameter(s) ?

Maybe providing more detailed requirement (i.e. giving a high level picture) of what you are trying to accomplish may help clarify.

[chulett]

Reread my post. You cannot do what you are trying to do. You can use a Job Parameter directly in a DB stage query but nothing you've generated inside the job.

You should be using two jobs for this. If you need to generate a series of values and run the second job multiple times, consider either building a looping Sequence or leveraging the UtilityRunJob() function.