I am working on an SQL query that involves a LEFT JOIN and an EXISTS subquery, but I am encountering an "Unsupported subquery" error. The query is as below where we have a LEFT JOIN operation while ensuring that only records matching certain date conditions are considered. Here is the query:

SELECT  
COUNT(a.key_column), 
COUNT(DISTINCT a.key_column)   FROM temp_table a LEFT JOIN main_table b 
ON a.client_key::INT = b.key_column
AND b.type_column = 'TYPE_A'
AND (
    a.date_field_1 <= b.end_date 
    AND a.date_field_2 >= b.start_date
)
AND EXISTS (
    SELECT 1
    FROM main_table c
    WHERE a.client_key::INT = c.key_column
    AND c.id_column = b.id_column
    AND c.type_column = 'TYPE_A'
    AND a.date_field_1 <= c.end_date
    AND a.date_field_2 >= c.start_date
    AND NOW() BETWEEN c.start_date AND c.end_date
);

I am working on an SQL query that involves a LEFT JOIN and an EXISTS subquery, but I am encountering an "Unsupported subquery" error. The query is as below where we have a LEFT JOIN operation while ensuring that only records matching certain date conditions are considered. Here is the query:

SELECT  
COUNT(a.key_column), 
COUNT(DISTINCT a.key_column)   FROM temp_table a LEFT JOIN main_table b 
ON a.client_key::INT = b.key_column
AND b.type_column = 'TYPE_A'
AND (
    a.date_field_1 <= b.end_date 
    AND a.date_field_2 >= b.start_date
)
AND EXISTS (
    SELECT 1
    FROM main_table c
    WHERE a.client_key::INT = c.key_column
    AND c.id_column = b.id_column
    AND c.type_column = 'TYPE_A'
    AND a.date_field_1 <= c.end_date
    AND a.date_field_2 >= c.start_date
    AND NOW() BETWEEN c.start_date AND c.end_date
);

• snowflake-cloud-data-platform
• correlated-subquery

• Ignore the select part for now, there is additional logic, just need to ensure the from part is corrected – Preetham Somsole Commented Mar 6 at 21:09
• You should edit your question to keep it up to date, rather placing comments. – zerocukor287 Commented Mar 12 at 9:13

1 Answer 1

Your query runs OK except for this condition

AND NOW() BETWEEN c.start_date AND c.end_date

as there is no NOW() function in snowflake, you can use other alternatives like CURRENT_DATE(),CURRENT_TIMESTAMP() etc, You did not mention the datatypes or sample input data so I am assuming start_date and end_date contains date.

Are you sure the error message was 'Unsupported subquery' as for NOW() it throws Unknown function NOW.May be other parts of your query(which you have not shared) has some error.

Instead of NOW() I used CURRENT_DATE() and inserted some sample data and the query worked OK.

Snapshot below: