SQL SET @Variable not resolved correctly in WHERE statement with Table Transformer

[Kamil Graczyk]

Hey Guys,
so basically I use this SQL statement to calculate the remainder of dividing the current week of the year by five:

SET @MYVAR = (FORMATDATE(GETDATE(), 'w') - (FLOOR(FORMATDATE(GETDATE(), 'w') / 5) * 5))

And when I try to use it with my table

Like this:

SELECT * FROM T1 WHERE 'person' = @MYVAR

It says "The query returned no rows."

What is the problem with that?
When I'm doing

SELECT @MYVAR FROM T1

It's resolved correctly.

[Stiltsoft support]

Hi @Kamil Graczyk ,

Maybe the following workaround will help your case:

Note that you will need the Table Toolbox macro to nest macros on Confluence Cloud.

Here are the settings of the internal Table Transformer macro:

SELECT *,
(FORMATDATE(GETDATE()) - FLOOR(FORMATDATE(GETDATE())/5)*5) AS 'weeks'
FROM T*

And here is the SQL query for the external Table Transformer macro:

SELECT 'value', 'person' FROM T*
WHERE 'value' = 'weeks'

Hope it will help your case.

Note that you've tried to compare numbers with the 'person' column that contains letters (strings). Seems that the 'value' is a correct field for the case.