Problem useing multiple value in parameter with where clause

[shobhitj]

Hi,

I facing a problem while using mutiplie parameters in where clause.

the sql query is as below -
Where code in ('#$CODE#')

The value of parameter #$CODE# is AB\',\'CD

I have seen a post in the forum on this to use "\" slash as the complier does not identify the parameter value correctly if used directly AB','CD.

By this while running the job i gave the value as AB\',\'CD and it ran in Dev enviroment properly.

But when i have moved to job to another environment same thing is not working the query is fetching 0 records even when same query i ran in backend it resulted in data.

Please suggest on this, if "\" is environment specific and what setting can be done in new environment to make it working.

[ArndW]

Can you echo the parameter value in the job (i.e. to a PEEK stage) to see if the backslash characters are still in the string and that the single quotes have not been stripped?

[shobhitj]

Thanks for the response!!

Should i have peek stage as output to the stage where i am using where clause?

[chulett]

The Peek stage can be anywhere in the job, and no that behaviour is not environment specific unless (perhaps) your environments are different hardware platforms and/or operating systems.

[shobhitj]

Thanks craig for the reply!!

But if this is not the environment issue then what is causing the job to behave diffrently in both environments?

Can you please guide on this

[shobhitj]

I have cofirmed that both environments are on same operating system/hardware platform

[chulett]

You first need to do what Arnd asked and confirm if the quotes are indeed being stripped or not. Does your 'other environment' source from a different database? If so, any chance there's legitimately no records that match the where clause?

[shobhitj]

Hi Craig,

In other environment i am fecthing the data from same database and same job as used in env. in which job is working.

[chulett]

OK. What does the Peek stage show?

[shobhitj]

Hi Craig,

we have used Peek stage just after the ODBC stage (where clause) is been given but as mentoined earlier the data is not been picked from the Database (0 records).

Please suggest how to write the query been given to Database to peek stage.

[shobhitj]

Hi,

Is there any Environemnt variable which shows the query that is used to retrive data from Database in job log

[ray.wurlod]

There is an automatic entry very early in the job log that shows all the environment variables of the job's execution environment.

[chulett]

Not sure how that helps, they need to see how a job parameter is being resolved to see if it retains its quotes or not. Perhaps a union with a simple sql to unconditionally pull a single record so you can get information into the Peek stage?

[shobhitj]

Hi Arndw and Craig,

I have seen the output of the job in both the environemnt using peek stage. given mulpile intput to the paramter value, it is as follows -

Input Env 2 Output (in which Env 1 Output
issue is there)

AB\",\"CD AB\",\"CD AB","CD
AB\',\'CD AB\,\CD AB','CD (Desired o/p)
AB','CD AB,CD AB,CD
AB','CD AB,CD "AB,CD"
AB\',\'CD AB\,\CD "AB','CD"
AB'',''CD AB\,\CD AB,CD
AB''','''CD AB\,\CD AB,CD

So, in Env 1 the single qoute is stripped off if \ is not there not output is fine is \ is present before it.

But in Env 2 it is stripping the sengle quote even if \ is present.

Please suggest if any other character can be used in Env 2.

[ray.wurlod]

What is different between the environments?