LEFt JOIN LATERAL showing error with SELECT

Question

I am trying to run below query :

SELECT
tc.ID_NUMBER AS AFC_RPP_Number,
hc.BUSINESS AS Business,
hc.DIRECTOR AS Director,
tc.REASON_FOR_REVISION AS Description_of_Change
FROM alo_gg.AWS_PIM tc
left join lateral(
    select BUSINESS,DIRECTOR                
    FROM alo_ggg.tracker
    WHERE START_DATE <= tc.DATE AND  SO = tc.SO 
    ORDER BY START_DATE DESC 
    LIMIT 1
) hc;

Above query is showing error:

ERROR:  syntax error at or near "SELECT"
left join lateral (SELECT BUSINESS,DIRECTOR...

If I run the subquery separately it is giving me a result, but with lateral it is giving me an error.

@GMB And if the correlated subquery does not yield row then main query row will be skipped as well. LATERAL/LEFT JOIN LATERAL are not equivalent. — Lukasz Szozda, Apr 29 '20 at 17:25
I assume you want `CROSS JOIN LATERAL`. Also, could you [edit] the question and add the *complete* error message in all its glory, straight from the client? — Laurenz Albe, Apr 29 '20 at 18:09

score 9 · Answer 1 · answered Apr 29 '20 at 17:26

9

You need to add ON TRUE and remove comma:

SELECT
tc.ID_NUMBER AS AFC_RPP_Number,
hc.BUSINESS AS Business,
hc.DIRECTOR AS Director,
tc.REASON_FOR_REVISION AS Description_of_Change
FROM alo_gg.AWS_PIM tc -- removing comma
left join lateral(
    select BUSINESS,DIRECTOR                
    FROM alo_ggg.tracker
    WHERE START_DATE <= tc.DATE AND  SO = tc.SO 
    ORDER BY START_DATE DESC 
    LIMIT 1
) hc  ON TRUE;  -- adding `ON` clause

answered Apr 29 '20 at 17:26

Lukasz Szozda

162,964
23
234
275

1

Thanks @Lukasz Szozda. I was reading the MySQL documentation and could not find it. Can anyone please paste a link to the proper documentation. It might help others to get the catch. – Samik Chattopadhyay Feb 23 '22 at 18:49

LEFt JOIN LATERAL showing error with SELECT

1 Answers1