Invalid Query Tree when leaving variable entry dialog empty (valueless)

[Former Member]

Hi Everyone,

I am receiving an 'Invalid query tree' error - as shown below - from my code (also shown below), but only when the 'Define survey variables' box is left blank.

i.e.: I leave the field blank,

then I get the error below,

finally here is the code in question -

DECLARE @ytdAmount int
SET @ytdAmount = '[%0]'
     IF @ytdAmount = ''
     SET @ytdAmount = '0'

SELECT T0.CardName, T0.CardCode, TotalSales - TotalCredits AS '$ YTD Sales'

FROM
(
SELECT T0.CardCode, T1.CardName, SUM(T0.DocTotal) 'TotalSales'
FROM AU.dbo.OINV T0
INNER JOIN AU.dbo.OCRD T1 ON T1.CardCode = T0.CardCode

WHERE Year(T0.DocDate) = Year(Getdate())

GROUP BY T0.CardCode, T1.CardName
) T0
LEFT JOIN
(
SELECT T2.CardCode, T3.CardName, SUM(T2.DocTotal) 'TotalCredits'
FROM AU.dbo.ORIN T2
INNER JOIN AU.dbo.OCRD T3 ON T3.CardCode = T2.CardCode

WHERE Year(T2.DocDate) = Year(Getdate())

GROUP BY T2.CardCode, T3.CardName
) T1

ON T0.CardCode = T1.CardCode

GROUP BY T0.CardCode, T0.CardName, (TotalSales - TotalCredits)
HAVING SUM([TotalSales] - [TotalCredits]) > '[%0]'


ORDER BY T0.CardName

Any help here will be greatly appreciated.

Kind Regards,

David

[Former Member · Accepted solution]

Hi Everyone,

I made a mistake here in the form of using the 'user parsed variable' (from the UI), namely %0, twice in my code (instead of once).

I first use it at the top within an SQL variable declaration,

DECLARE @ytdAmount varchar(10)
SET @ytdAmount = '[%0]'
     IF @ytdAmount = ''
     SET @ytdAmount = '0'

I then follow on and use the %0 variable in the HAVING clause,

HAVING SUM([TotalSales] - [TotalCredits]) > '[%0]'

However what I really wanted to do was insert the SQL variable, @ytdAmount, into the HAVING clause, as follows,

HAVING SUM([TotalSales] - [TotalCredits]) > @ytdAmount

My code is now returning the results that I expect.

Kind Regards,

David