CODEWITHSUNDEEP
sqlamazon-redshift
Not yet decided
Not yet decided

Reputation: 49

Outer apply to left join conversion

I have a query in SQL Server that I'm trying to migrate to redshift. It has OUTER APPLY in it but Redshift doesn't support it. How can I convert it to left join so that I can use it in Redshift?

....
OUTER APPLY
( 
    SELECT TOP 1 fel.*
    FROM fact.FactEventLog fel 
    WHERE fpt.ParcelProfileKey = fel.ParcelProfileKey
            AND
            fpt.LastEventKey = fel.EventLegKey
            AND
            FPT.DateLastEvent = fel.EventDateTimeUTC
) fel
....

Something answered in this stackoverflow or this answer

Upvotes: 1

Views: 802

Answers (1)

xQbert
xQbert

Reputation: 35323

Maybe.... someting like...

....
    LEFT JOIN
    ( 
        SELECT fel.*, row_number() over (partition by ParcelProfileKey, EventLegKey, EventDateTimeUTC order by null) RN
        FROM fact.FactEventLog fel 
    ) fel
      on fpt.ParcelProfileKey = fel.ParcelProfileKey
     AND fpt.LastEventKey = fel.EventLegKey
     AND fpt.DateLastEvent = fel.EventDateTimeUTC 
     AND 1=fel.RN

....

but it just seems so wrong w/o an order by actually defined in the window function. it's like you don't care what random result is returned just so long as 1 exists... but then why not use an exists.... shrug

Upvotes: 2

Related Questions