terdata connector statement error

[nikhil_bhasin]

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.

[nikhil_bhasin]

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

[chulett]

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

[nikhil_bhasin]

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