CTE expression throws error in @EXECSQLCOUNT function

Products

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

Issue/Introduction

We have a query that runs fine within @SQLLOV, but when we're attempting to use the same query in @EXECSQLCOUNT for the table repeat count, we are getting errors:

0 - Failed to evaluate Table Repeat expression to a number: @execsqlcount(<<TABLE_NAME>>,"; with policies as (

       select <<COLUMN1>> FROM [dbo].[<<COLUMN2>>] p with (NoLock)

       WHERE p.<<COLUMN3>> IN ('requested_ID'))

, Accounts as (

       select distinct <<ACCOUNT_ID>> from table_values)

SELECT j.*  

FROM Accounts p

       inner join [dbo].[<<COLUMN4>> j with (NoLock) on p.P_A_ID = j.<<ACCOUNTID>>

UNION ALL

SELECT j.*  

FROM [dbo].[<<COLUMN5>> j with (NoLock)

WHERE j.A_I_D IS NULL

AND TransGUID IN

((SELECT TransGUID FROM Accounts a INNER JOIN [dbo].<<COLUMN4>> j2 with (NoLock) ON a.P_A_ID = j2.A_ID)

UNION

(SELECT TransGUID FROM policies a INNER JOIN [dbo].<<COLUMN4>> j2 with (NoLock) ON a.P_T_ID = j2.P_T_ID)))")@ - SQLSTATE = 37000

[Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ';'.

 

Environment

Release : 4.9

Component : CA Test Data Manager

Cause

The error is mis-leading.  The real issue is that we do NOT support the "; with policies as  in the EXECSQLCOUNT construct.

Resolution

For Table Repeat Count



This does not work:



@execsqlcount(P~DataSource~,"with CTE1 as

(select Column1,Column2 FROM [dbo].[Table1] p with (NoLock) WHERE p.Column2 IN (~Variable~))

, CTE2 as

(select distinct Column2 from CTE1)

SELECT j.Column3 FROM CTE2 p inner join [dbo].[Table2] j with (NoLock) on p.Column2 = j.Column4

UNION ALL

SELECT j.Column3 FROM [dbo].[Table2] j with (NoLock) WHERE j.Column4 IS NULL AND Column5 IN

((SELECT Column5 FROM CTE2 a INNER JOIN [dbo].Table2 j2 with (NoLock) ON a.Column2 = j2.Column4)

UNION

(SELECT Column5 FROM CTE1 a INNER JOIN [dbo].Table2 j2 with (NoLock) ON a.Column1 = j2.Column1))")@





This does work:



@execsql(P~DataSource~,"with CTE1 as

(select Column1,Column2 FROM [dbo].[Table1] p with (NoLock) WHERE p.Column2 IN (~Variable~))

, CTE2 as

(select distinct Column2 from CTE1)

, TotalCount as

(SELECT j.Column3 FROM CTE2 p inner join [dbo].[Table2] j with (NoLock) on p.Column2 = j.Column4

UNION ALL

SELECT j.Column3 FROM [dbo].[Table2] j with (NoLock) WHERE j.Column4 IS NULL AND Column5 IN

((SELECT Column5 FROM CTE2 a INNER JOIN [dbo].Table2 j2 with (NoLock) ON a.Column2 = j2.Column4)

UNION

(SELECT Column5 FROM CTE1 a INNER JOIN [dbo].Table2 j2 with (NoLock) ON a.Column1 = j2.Column1))

) Select Count(1) from TotalCount")@

 

By replacing the EXECSQLCOUNT with a properly formatted EXECSQL statement that returns the exact same end result, the execution is much faster.