Issue
I was writing a SQL query in Appsmith to retrieve data based on two conditions but it was returning NULLs. I tried using a UNION between the two conditions but it still didn't work. After turning off prepared statements, I realized that only the first condition was working. When I evaluated the query, I found that the second condition was not filtering out any NULLs. I resolved this issue by using a JOIN instead of a UNION.
Resolution
The issue with the original query was that it was returning NULL values. One solution is to turn off prepared statements to check the evaluated value and see where the query is going wrong. However, the query itself needed to be rewritten to avoid collecting all the results and unifying both queries.
To avoid this, the query should use JOIN instead of UNION. Here's an example query that would return the intersection of records where both conditions are met:
SELECT *
FROM results
WHERE po_num IS NOT NULL
AND po_num = ‘{{report_po.text}}’
AND date_start IS NOT NULL
AND date_start BETWEEN ‘{{report_start_date.formattedDate}}’ AND ‘{{report_end_date.formattedDate}}’;
With this updated query, any NULL values will be excluded and only records that meet both conditions will be returned.