Empty SQL Response

[AntoineOA]

Hello Retool!

One of my SQL queries that worked for two years is now broken. Part of the data sent is dynamic and comes from a multi select. I've found the bug, it's when I give the value of the contract name in the where condition. I can give it with raw values but not dynamic values. When I copy the SQL code into the state of my query and run it in console, it works without a hitch, but not from Retool where I only get an empty table as a response. Do you know why I receive an emtpy response?

Here is the code I send contractInputs is a multi select:

Here is the code from the state (I paste it into the console and add single quote around contrat_name values ex='XXX-XXX-20201' and around the dates :

select date,
       contract_id,
       contract_name,
       min_charge_days,
       contract_name +
       IIF(contract_amendment = 0, '', ' [' + cast(contract_amendment as varchar(10)) + ']') as contract_fullname,
       product,
       license_mrr,
       maintenance_mrr,
       total_mrr,
       license_mrr_EUR,
       maintenance_mrr_EUR,
       total_mrr_EUR,
       currency
from CONTRACT_MRR_PREVAILING
         inner join VISTA_CONTRACT on CONTRACT_MRR_PREVAILING.contract_id = VISTA_CONTRACT.id
where contract_name IN (XXX-XXX-2021, XXX-XXX-2021)
  and date >= 2000-02-01
  and date <= 2100-02-01
order by date desc

[Harry_Doan]

I think the part using array in SQL query is not straight forward

where contract_name in ( {{contractsInput.values.toString()}} )

We use prepared statement by default so the whole contractsInput.values will be passed in as a string. Here is a guide to do it in Retool

SQL Cheatsheet - best practices for different flavors of SQL syntax

Learn about some best practices for working with SQL! This post is back by popular demand -- we all use SQL in our Retool apps fairly often. Example SQL snippets to use for the various SQL resource types can be super useful to have on hand. Use arrays in queries Every SQL database has a slightly different way of handling arrays. Because SQL resources in Retool are set to convert queries to prepared statements by default this can have more challenging interactions with multiple values inside a s…

e.g. for PostgresQL:

SELECT
  *
FROM
  users
WHERE
  id = ANY({{ [1, 2, 3] }})

[AntoineOA]

I used to send ContractInput.values and it worked, but it hasn't worked for a few days now, even though I haven't changed a thing. That's why I try to send : contractInput.values.toString()

[joeBumbaca]

Hey @AntoineOA, can you share what resource your are using for this query (ie postgres, sql server, etc)?

[AntoineOA]

Hello @joeBumbaca, I use SQL server. I find another solution with retool components and by updating my query. It working perfectly for now. Thank you for your answer.

Antoine

[AntoineOA]

Hi @joeBumbaca,

Sorry to reopen this topic. As I said, I'm using SQL server. I still my initial issue.
I tried to update the query with this where I had with the map some quotes to my values. When i copy the retool state of the query in my sql console it work perfectly but when I run it on retool side param1 show this error Incorrect syntax near '@param1'. How can I fix it ?
Here is the query :

select date,
       contract_id,
       contract_name,
       min_charge_days,
       contract_name +
       IIF(contract_amendment = 0, '', ' [' + cast(contract_amendment as varchar(10)) + ']') as contract_fullname,
       product,
       license_mrr,
       maintenance_mrr,
       total_mrr,
       license_mrr_EUR,
       maintenance_mrr_EUR,
       total_mrr_EUR,
       currency
from CONTRACT_MRR_PREVAILING
         inner join VISTA_CONTRACT on CONTRACT_MRR_PREVAILING.contract_id = VISTA_CONTRACT.id
where contract_name IN {{'(' + contractsInput.data.map(d => `'${d}'`) + ')'}}
  and date >= '2000-02-01'
  and date <= '2100-02-01'
order by date desc

Here is the full answer :

[joeBumbaca]

Hey @AntoineOA! As @Harry_Doan mentioned above, we've got the proper syntax for using arrays in a variety of SQL flavors here in this post.

I'm able to use a multiSelect component's value in this SQL Server query using the syntax shown.

App component value:

SQL syntax:

Results:

[AntoineOA]

Thank you It was exactly what I was Looking for