Execsql function in table repeat count while publishing does not work in TDoD

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

We have a TDoD (Test Data on Demand) flow which creates an Excel file as an output. The publish has been defined in Datamaker and the number of rows that needs to be published is defined in "table repeat count" field while publishing as a SQL query. 

The publish runs fine and as expected in Datamaker. The query is working fine in the Datamaker SQL editor. The same publish throws error from TDoD. Below is the error received during publish through TDoD portal:

 

ERROR: Publish failed for job 3609, Table repeat count, unable to resolve expression: 

@execsql(PKTT_IT,SELECT count(cod.code_Desc_x) FROM GTM00T.VUSER_FUNCTION func, 

GTM00T.VCODES cod where func.fctn_c = cod.code_c and func.usr_logon_i in ('TARGETWS1') with ur)@ reason:resolve_meta_data_item: TDMServiceException: eval_function_part: TDMServiceException: eval_function_part: TDMServiceException: execsql: TDMServiceException: execsql: BadSqlGrammarException: StatementCallback; bad SQL grammar 

[SELECT count(cod.code_Desc_x) FROM GTM00T.VUSER_FUNCTION func]; nested exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=COD.CODE_DESC_X, DRIVER=3.69.49 

End date and time: 2016/12/07 14:31:57 

 

Environment

TDM 3.5, 3.6, 3.8

Cause

The original SELECT query that was used in the table repeat count had a comma and that was not processed correctly by the TDM portal during publish time, being one of the parameters of the execsql function: 

SELECT count(cod.code_Desc_x) FROM GTM00T.VUSER_FUNCTION func, 

GTM00T.VCODES cod where func.fctn_c = cod.code_c and func.usr_logon_i in ('TARGETWS1') with ur 

 

For reference, the entire execsql function reads:

@execsql(PKTT_IT,SELECT count(cod.code_Desc_x) FROM GTM00T.VUSER_FUNCTION func, 

GTM00T.VCODES cod where func.fctn_c = cod.code_c and func.usr_logon_i in ('TARGETWS1') with ur)@

Resolution

To resolve the issue, replace the SQL used in the table repeat count with this ANSI SQL (that has no commas in it): 

SELECT count(cod.code_Desc_x) FROM GTM00T.VCODES cod where cod.code_c in ( SELECT func.fctn_c FROM GTM00T.VUSER_FUNCTION func where func.usr_logon_i in ('TARGETWS1')) with ur