CODEWITHSUNDEEP
sqlgreenplumlateral-join
Safala
Safala

Reputation: 71

LEFt JOIN LATERAL showing error with SELECT

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.

Upvotes: 6

Views: 8493

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175924

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

Upvotes: 11

Related Questions