Input PreSQL Error: Must declare the scalar variable

[dineshp]

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

[RishiK]

@dineshp this should work if you use a stored procedure. Can you try running it that way in the Pre-SQL?  This link will help you:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-call-stored-procedure-SQL-Serve...

[dineshp]

Hi @RishiK 

This is a production database, creating stored procs are restricted. Any reason why calling a local variable doesn't work in Pre SQL but works fine in the main query?

Thanks!

[anjaliaithal]

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)

[dineshp]

@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]

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