Hello Retool Community!
I’m encountering an issue while running SQL queries that involve two dynamically set variables. When I include multiple {{ form.data.input }}
references in my query, I receive an error indicating that I’m hitting a bind variable issue (specifically, “bind variable :1 not set”).
This problem appeared after a recent Retool update, as similar queries worked fine in the past. Below is an example of the problematic query:
SELECT DISTINCT
a.*
FROM some_table a
LEFT JOIN another_table b ON b.id = a.id
WHERE b.branch_code IN ({{ branchCodes.value }})
AND a.status = 'active'
AND NOT EXISTS (
SELECT 1
FROM campaign_management_table c
WHERE c.some_id = a.id
{{ campaignId ? `AND c.campaign_id = ${campaignId}` : '' }} -- Only include this if campaignId exists
AND c.is_active = true
);
In the debug console, I see the following error when executing this query:
compilation error: error line 5 at position 27 Bind variable :1 not set.
Here's the output from the debug console:
branchCodes.value: (9) ["A", "B", "J", "L", "L", "M", "S1", "S", "S3"]
form2.data.select2: 2
additionalScope: {}
triggeredById: ""
environment: "production"
Interestingly, when I modify the query to only use one dynamic variable, it runs successfully:
SELECT DISTINCT
a.*
FROM some_table a
LEFT JOIN another_table b ON b.id = a.id
WHERE b.branch_code IN ({{ branchCodes.value }})
AND a.status = 'active'
AND NOT EXISTS (
SELECT 1
FROM campaign_management_table c
WHERE c.some_id = a.id
AND c.is_active = true
);
The debug output for this successful query is:
branchCodes.value: (9) ["A", "B", "J", "L", "L", "M", "S1", "S", "S3"]
additionalScope: {}
triggeredById: ""
environment: "production"
I’ve tried several workarounds, but it appears that using multiple bound variables with {{ }}
consistently leads to this error. Has anyone else experienced this issue, or can you suggest a potential fix?
Thank you for your assistance!