Hi,
in a JDBC SQL I am running the following SQL and all is working fine:
SELECT dtdodt, dtrefx
FROM myfile
WHERE DATE(SUBSTR(CHAR(dtdodt), 1, 4) || '-' ||
SUBSTR(CHAR(dtdodt), 5, 2) || '-' ||
SUBSTR(CHAR(dtdodt), 7, 2)) >= DATE(2024-12-31)
FETCH FIRST 200 ROWS ONLY
However when I replace the date with the variable like you can see next I am getting an error:
SELECT dtdodt, dtrefx
FROM myfile
WHERE DATE(SUBSTR(CHAR(dtdodt), 1, 4) || '-' ||
SUBSTR(CHAR(dtdodt), 5, 2) || '-' ||
SUBSTR(CHAR(dtdodt), 7, 2)) >= DATE({{ Dateperiod6 }})
FETCH FIRST 200 ROWS ONLY
It seems like the variable is NULL. But the variable contains exactly the same: 2024-12-31.
I think the SQL cannot access the variable content.
For the transformer approach, I would not expect this to work in Retool due to prepared statements. For example, prepared statements prevents you from putting the table name inside double curly brackets.
You can try disabling prepared statements on the resource settings, but we generally don't recommend this as it could have security risks and it could break other queries for this resource that are already working.
With prepared statements enabled, we're more likely to get your original query with just the dynamic variable working. What is {{Dateperiod6}}? If it is a Retool variable, you'll need to change it to {{Dateperiod6.value}}. I'd try that next and see if it works or if the error is any different. For troubleshooting, you could also try {{ '2024-12-31'}} to see if that works before we explore other syntax changes
Based on this "Use of parameter marker or NULL not valid" I think the issue is that what is being sent to the JDBC driver is the actual parameter marker ? instead of the variable content.
