Tisha Tisha - 1 year ago 116
SQL Question

How can you write a query for Amazon redshift database such that the where clause of the correlated query has two conditions?

Example -

SELECT date, name,
(SELECT value
FROM this_table
WHERE col1= 'test1' and col2='test'
) AS num_sloths_bought_before
FROM source_table;

When I execute the query in redshift, I get this error -

[Amazon](500310) Invalid operation: This type of correlated subquery pattern is not supported yet;

Answer Source

You can usually rewrite Correlated Scalar Subqueries using joins, for your query it's a Left Join:

SELECT date, name, dt.value AS num_sloths_bought_before
FROM source_table
 ( SELECT val, 
   FROM this_table
   WHERE col2='test'
 ) this_table
ON source_table.val= this_table.val;