In (Array) SQL statment not working with dynamic array?

Queries and Resources
,
1

We have a simple resource query to an SQL server:

SELECT * FROM dbo.con_invoice_line_items
WHERE invoice_id IN ({{lineItemsInvoiceIDs.value}})

this returns no results.

however writing the SQL manually:

SELECT * FROM dbo.con_invoice_line_items
WHERE invoice_id IN ('21/140476', '04/001587')

returns the correct output

surly this is an issue with how retool is handling the dynamic version???

for additional info, the resource is connecting to SQL Server 16.0.1000

image
image618×790 17.2 KB

image
image622×626 15 KB

1 Like
2

i'm kinda thinking this might be a syntax issue.
('{"product_a", "product_d"}') would normally define an array, with { } denoting the typeof array and not ( ) as might be expected. if so, this might be right(?):

IN ({ `{{ lineItemsInvoiceIDs.value }}` })

if it doesn't work and you end up going the guess/check route with different formatting, you'll want to be careful with 3 curly brackets in a row. just make sure the ones that designate JS code are surrounded by a space since { {{ js_code }} } can parse differently than {{ { js_code } }} and {{{ js_code }}}.

3

thanks buddy, sadly doesnt like it....

image
image633×911 25.1 KB

I've tried loads of variations on the formatting... same issues. but if I add them as hardcoded in the same format works fine. leading me to believe it may be a retool issue?

4

=/

seems like it isn't recognizing lineItemsInvoiceIDs.value as an array... or the invoice_id column type isn't the same as the lineItemsInvoiceIDs.value type? maybe try casting it to ARRAY?

I just noticed you're using mssql which i think has different types than postgresql, it might not even have ARRAY (you'd use VARCHAR/TEXT or JSON instead i guess). i'm barely treading water with postgresql, it's even worse with mssql so hopefully a db person will chip in here

5

if anyone has a similar issue i managed to get it to work in retool using the following method:

DECLARE @invoice_ids NVARCHAR(50);
SET @invoice_ids = {{lineItemsInvoiceIDs.value}};

DECLARE @invoice_id_table TABLE (invoice_id NVARCHAR(50));

INSERT INTO @invoice_id_table (invoice_id)
SELECT TRIM(value)
FROM STRING_SPLIT(REPLACE(@invoice_ids, '"', ''), ',');

SELECT *
FROM dbo.con_invoice_line_items
WHERE invoice_id IN (SELECT invoice_id FROM @invoice_id_table);

a bit of a workaround but working for a mssql resource in retool

2 Likes