Input PreSQL Error: Must declare the scalar variable

Question

Hi,

I am declaring variable using T-SQL and it runs fine when i run in Table or Query section but i get "Must declare the scalar variable" error when i try to run the same in Pre-SQL.

Is there a limitation in running Pre or Post SQL?

Doesn't Pre-SQL accept variables?

DECLARE @Max_dup_215_D DATE;
SET @Max_dup_215_D = (SELECT MAX(FULLDATE) FROM TABLENAME);

SELECT * FROM TABLENAME WHERE FULLDATE = @Max_dup_215_D;

Designer Version: 2019.4.8.22007

Thanks,

Dinesh

anjaliaithal · Answer

In that case, try removing the semi-colon and running the statements

dineshp · Answer

@anjaliaithal For performance i want to use LV instead of a subquery. The problem isn't i can't achieve the out using subquery or calling LV in the main query section. It's that declaring and calling LV isn't possible in Pre-SQL which is very strange.

anjaliaithal · Answer

Instead of using this in PRE SQL statement , Why dont you use the below query in the Table/ Query section? It would give the same result. Just skip the Pre SQL part:

SELECT * FROM TABLENAME WHERE FULLDATE = (SELECT MAX(FULLDATE) FROM TABLENAME)