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