RedShift Correlated Sub-query

Question

Need your help. I am trying to convert below SQL query into RedShift, but getting error message "Invalid operation: This type of correlated subquery pattern is not supported yet"

SELECT
    Comp_Key,
    Comp_Reading_Key,
    Row_Num,
    Prev_Reading_Date,
    ( SELECT MAX(X) FROM (
                        SELECT CAST(dateadd(day, 1, Prev_Reading_Date) AS DATE) AS X
                        UNION ALL 
                        SELECT dim_date.calendar_date
                        ) a
        ) as start_dt
FROM stage5
    JOIN dim_date ON calendar_date BETWEEN '2020-04-01' and '2020-04-15'    
WHERE Comp_Key =50906055

The same query works fine in SQL Server. Could you please help me to run it in RedShift?

Regards,

Kiru

Answer 1

Kiru - you need to convert the correlated query into a join structure. Not knowing the data content of your tables and the exact expected out put I'm just guessing but here's a swag:

SELECT
    Comp_Key,
    Comp_Reading_Key,
    Row_Num,
    Prev_Reading_Date,
    Max_X
FROM stage5
    JOIN dim_date ON calendar_date BETWEEN '2020-04-01' and '2020-04-15'  
    JOIN ( SELECT MAX(X) as Max_X, MAX(calendar_date) as date FROM (
                        SELECT CAST(dateadd(day, 1, Prev_Reading_Date) AS DATE) AS X FROM stage5
                        cross join
                        SELECT dim_date.calendar_date from dim_date
                        ) a
        ) as start_dt ON   a.date = dim_date.calendar_date
WHERE Comp_Key =50906055

This is just a starting guess but might get you started.

However, you are likely better off rewriting this query to use window functions as they are the fastest way to perform these types of looping queries in Redshift.

Answer 2

Thanks Bill. It won't work in RedShift as it still has correalted sub-query.

However I have modified query in another method and it works fine. I am closing ticket.