delete from template_items
where template_item_id in ({{BulkDeleteRows.value}})
BulkDeleteRows is a comma delimited list of IDs I get from a multiselect table.
If one item is selected and thus just a single value in BulkDeleteRows, it all works.
However, if I have multiple rows selected I get this error: Conversion failed when converting the nvarchar value ‘19,18’ to data type int. So somewhere something is assigning single parameter to the contents of my in() and typing it as an int.
I can easily get around this with a stored procedure, but thought I’d throw this out there as it does not seem like an expected result.
What type of resource are you encountering this on? I know that each SQL variant handles this syntax a bit differently, so there may be something we can do in the query itself to get this working. Otherwise, do you get the same error when trying this query in GUI mode:
I have it working on a stored procedure, but refactored it to see if using IN in GUI mode would work (I did not know about using IN here before) and that worked.
I think it comes down to making sure that the query is interpolated properly by Retool, which is just a matter of finding the right syntax. I’ll test some things out on my end to try to find the right syntax for SQL mode, for future reference.
That is almost exactly what my stored procedure looks like! Didn’t think to try that directly in Retool. There is a reason that after doing this for a few decades my forehead has a palm shaped impression in it.
I'm trying to use IN() with a multiselect table but I can't get it to work either. But I'm using .seleectedRow.data? Is that wrong?
SELECT * FROM PrintEvents
WHERE ([AccountID] = {{SelectedReference.value}}) AND (PrintEvents.EventID IN ({{SelectedEvents.selectedRow.data.EventID}})))
It works if the table SelectedEvents does not allow multiselect and I use EventID=SelectedEvents.selectedRow.data.EventID
I saw this done in the documentation with a multiselect dropdown and thought it might be the same for a table. I was wondering if the format for the selected rows isn't compatible with the query?
You need to either use the alternate IN syntax mentioned earlier in this post like this (assuming SQL Server is your database, other databases will have a similar function for doing this):
IN (SELECT value FROM STRING_SPLIT({{ BulkDeleteRows.value }}, ‘,’))
Or use the GUI editor and its IN keyword.
Sending the IN your way just sends the value of SelectedEvents.selectedRow.data.EventID as a single string. The alternate IN syntax send the string value, but tells the database to then split into multiple values that the IN can actually work against as you would expect.
The GUI method tells Retool to work the magic for you.
Thanks for the explanation. I saw what you'd done but didn't really understand.
I've tried it as this:
SELECT * FROM PrintEvents
WHERE ([AccountID] = {{SelectedReference.value}}) AND (PrintEvents.EventID IN (SELECT EventID FROM STRING_SPLIT({{ SelectedEvents.selectedRow.data.EventID }}, ‘,’))
But I still don't get any results. Each time I get the same thing.
SelectedEvents.selectedRow.data.EventID is undefined
So I've tried: both, formatDataAsObject and formatDataAsArray but neither seems to work with STRING_SPLIT? I'm guessing that none of these three formats are in the right form?
What is the value of SelectedEvents.selectedRow.data?
You can find out by hovering your mouse over data and a window will pop up with the value. The window has live data and you can navigate the objects/arrays that you see and that will hopefully give you some clues.
SelectedEvents.selectedRow.data is an "array of objects". It contains two items in the array (as expected) each of which is the row of the selected multiselect table.
if I use SelectedEvents.selectedRow.data.EventID I get undefined.
If I use formatDataAsObject(SelectedEvents.selectedRow.data).EventID I get an array of just the two values of EventID that are selected (just as I want). But using this with or without the STRING_SPLIT doesn't seem to work with IN().
Hmmm, looks like formatDataAsObject(SelectedEvents.selectedRow.data).EventID should be working. Is the query failing with an error or just not returning results?
If it is not returning an error you may be running into the same problem I am (SQL query does not return error - #3 by everett_smith) it can be very difficult to debug something that just "doesn't work" with no feedback as to why.
Which shows the data in the formatDataAsObject(SelectedEvents.selectedRow.data).EventID which looks like an array of int?
And here is my data from SQL Server:
There are 3 lines here that I would expect to be returned. (Because EventID is IN the array of int above). Usually I have an extra WHERE criteria: ResourceName <> NULL but I removed it to minimise the whole problem.
Hey @jclutterbuck, There is a bug on our end with our MSSQL resource where all failed queries are failing silently (not returning any data or errors) I've reported this internally and I'll update you here on this post with any progress as we work towards shipping a fix.
This is only a partial workaround, since it doesn't help you see why your queries are failing, but you could define a failure condition for MSSQL queries in Retool. Since the failed queries don't return any data or metadata, you could use the logic {{Object.keys(data).length === 0 && !metadata}} to define a failure condition in the Response tab of the query editor.
Hi all, We have shipped a fix for this bug to cloud. If you're self-hosting Retool, this fix will be included in an upcoming on-prem release in roughly two to four weeks!
That is almost exactly what my stored procedure looks like! Didn’t think to try that directly in Retool. There is a reason that after doing this for a few decades my forehead has a palm shaped impression in it.
I've reported this internally and I'll update you here on this post with any progress as we work towards shipping a fix.