How to pass Parameters to user defined sql queries

[Nripendra Chand]

Hi All,

How can we pass parameters to user defined sql in ODBC stage?

-Nripendra

[Nripendra Chand]

Hi All,

i want to use DECODE function in my user query but this function is not taking parameter's corresponding value. So should i use procedures?

-Nripendra Chand

[vmcburney]

You can use a job parameter within a SQL stage by adding the parameter name surrounded by # symbols directly into user-defined SQL or condition fields.

Why do a DECODE in the database stage when you can do it in DataStage? DECODE is specific to Oracle so I assume you are trying to run it against an Oracle database. You might have more luck using it in the Oracle OCI stage or the Dynamic RDBMS stage instead of the ODBC stage. Personally I don't like using transformation functions in the select list of a database stage as it is not visible to the data lineage reporting functions in DataStage and MetaStage. Better to bring the column through in its rawest form and transform it in DataStage.

[talk2shaanc]

Hi Nripendra,
Using of job parameters in a decode statement wont throw any error. I feel that your are not calling the parameter properly.
say I have declared a parameter, DeleteFlag with Default value as 'Y' then my sql will look like:

select decode(col1,#DeleteFlag#,'YES','NO') from <table_name>

[Nripendra Chand]

Thanks a lot. I got it.

-Nripendra