Re: RPG to SQL Help

Hi Greg,

you would simply SELECT the rows you need, and limit it to a maximum of 2 rows.

Example:

dcl-ds rows qualified dim(*auto:2);
custno ...
tran_date ...
tran_time ...
tran_type ...
end-ds;

%elem(rows) = *zero;
exec sql declare csrCustTrans cursor for
select custno, tran_date, tran_time, tran_type
from <your-table>
where custno = :your_custno
order by tran_date, tran_type
limit 2;
exec sql open csrCustTrans;
exec sql fetch csrCustTrans for 2 rows into :rows;

After the FETCH the variable sqlerrd(3) is set to the number of rows fetched - in your can 0, 1 or 2. But you can also check the number of elements in the rows data-structure array.

Don't forget to CLOSE the cursor after you have your data.

HTH and kind regards,
Daniel

Am 16.06.2025 um 18:16 schrieb Greg Wilburn <gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>:

This seems so simple... but for some reason I'm at a complete loss.

Given a table as follows:
CUSTNO, TRAN_DATE, TRAN_TIME, TRAN_TYPE

Using SQL, I need to select a specific record from this file (can use the RRN if needed), AND the very next record (chronologically) for the same CUSTNO (if one exists).

In RPG, I would just:
SETLL with the CUSTNO TRAN_DATE TRAN_TIME, then
READE with CUSTNO

What is the SQL equivalent to that?




[Logo]<https://www.totalbizfulfillment.com/> Greg Wilburn
Director of IT
301.895.3792 ext. 1231
301.895.3895 direct
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>
1 Corporate Dr
Grantsville, MD 21536
www.totalbizfulfillment.com<http://www.totalbizfulfillment.com>
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.