DSXchange

Hi,

I have a datastage job which has a teradata connector stage as target that issues a delete statement to purge the data from the tables. Table name and where clause is parameterized so that the same job can be utilized for multiple purges. The issue that I am facing is when I have single quotes in my where clause, they are getting ommitted when the job executes, although when i check the values of these parameters in log. they are present.
so #WHERE_CLAUSE#=where (cast(dt_yr as date FORMAT 'yyyy') < 12)
becomes

delete from abc where (cast(dt_yr as date FORMAT yyyy) < 12) while execution and hence i am getting syntax error

Now the same job in 8.1 version (terdata api stage instead of connector) runs fine with same paramter values.

Hi, found the solution, we need to pass escape charcters also as part of the parameter value so

#WHERE_CLS#=where (cast(dt_yr as date FORMAT \'yyyy\') < 12)


so that single qoutes are treated as a character while execution

Or you can set APT_OSL_PARAM_ESC_SQUOTE so you don't have to do that.

Thanks chulett. I will try setting this variable and let you know the results.