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 …

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)?