Re: Sql with Lateral

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.

--
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.