Re: Sql with Lateral

[Niels Liisberg <nli@xxxxxxxxxxxxxxxxx>]

Perhaps you can rewrite it so the selection is in the lateral so the
"where"  in the lateral has the correct one row to return :


SELECT SDEXP01,ISSTYLEID,ISCOLORID,IJINSTR01,sum(istunits)
FROM whssd00 LEFT JOIN ptiis00  on isdid# = sddid#
left JOIN LATERAL (
    SELECT *
     from ptiij00
     where sddid# = ijdid# AND  isline# = ijline
     fetch first row only
) on 1= 1
where sdexp01 >= 1234567
GROUP BY SDEXP01, ISSTYLEID, ISCOLORID, IJINSTR01
ORDER BY SDEXP01, ISSTYLEID, ISCOLORID, IJINSTR01

On Wed, Feb 15, 2023 at 3:59 PM Art Tostaine, Jr. <atostaine@xxxxxxxxx>
wrote:

> I'm trying to only get the first record from PTIIJ00 because it has
> multiple records with the same line# when matching PTIIS00.  This SQL
> doesn't get any PTIIJ00 row:
>
> SELECT SDEXP01,ISSTYLEID,ISCOLORID,IJINSTR01,sum(istunits)
> FROM whssd00 LEFT JOIN ptiis00  on isdid# = sddid#
> left JOIN LATERAL (SELECT * from ptiij00 fetch first row only)
> ON sddid# = ijdid# AND  isline# = ijline
> where sdexp01 >= 1234567
> GROUP BY SDEXP01, ISSTYLEID, ISCOLORID, IJINSTR01
> ORDER BY SDEXP01, ISSTYLEID, ISCOLORID, IJINSTR01
>
> If I remove FETCH FIRST ROW ONLY it brings in the PTIIS00 record multiple
> times and it makes my SUM(UNITS) wrong.
>
> I can explain more detail if necessary.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.