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