parameterisation problem

[skumar]

Hi All

I am facing a problem while parameterising the query. I have a requirement to paramaeterise the source query.

For Ex: - SELECT * FROM #$SCHEMA#.EMP

I am calling the total query in a job parameter. When I declared the parameter as a string the total query is considered as string which includes Schema value also.

I tried in the parameter sourcequery(my param name) as

SELECT * FROM '":$SCHEMA:"'.EMP
but it is not working...

can some one throw light on this.

Thanks
skumar

[jwiles]

If your query, apart from the schema name, can change from run to run, you will need to build the final query before the job is started. DataStage will not replace parameters embedded within parameters.

A Basic routine called by a job sequencer should be able to handle that for you using the EReplace function. The routine should return the final query, which you would store in a user variable and pass as the query parameter to your job.

Regards,

[chulett]

You can't pass in a parameter that contains a parameter, it won't resolve things twice. Your second method should work but use the string you first posted there instead, see if that works.