RE: RPG to SQL Help

Sounds very cool. We have implemented Valence at my shop and CNX has done a good job of integrating the web interface with the RPG backend.
Haven't done much with the AI assist yet, but it does sound powerful.


-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Greg Wilburn
Sent: Wednesday, June 18, 2025 4:42 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: RPG to SQL Help

Just to tie this off...

I used the SQL statement you suggested... get the current and previous row.
This is data is ultimately going into a Valence(CNX) Customer application in the browser.

Here is what am I've done with it (I thought this was very cool):

I display a grid (left 1/2 of window) showing each date/time/user that change the customer or address.
When the user clicks on that row, a backend SQLRPGLE program is called to build the result.
1. It uses the query to load all the fields from the 2 rows into a Data Structure.
2. DATA_GEN using YAJLDTAGEN transforms that into two variables containing the JSON 3. I write that to a workfile (using SQL Merge) keyed by the user's "SID" (unique session ID in Valence)

When it returns to the browser app, it loads a second grid on the right 1/2 of the screen using the workfile data.
This is where JavaScript takes over and formats both JSON objects (before and after) into three columns:
Property(field name) - beforeValue - afterValue. It makes any row differences BOLD and RED.

The cool part is that I didn't write the JavaScript - the "Valence Assistant" (AI) in their app builder did it.

Now the user can click a row, and scroll up and down to easily see the changes.

Thought it was worth sharing.

Thanks for the help!
Greg

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Charles Wilt
Sent: Monday, June 16, 2025 3:51 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: RPG to SQL Help

Hey Greg,

Maybe I'm missing something...
But if you've already got the list (for a given customer), don't you already have all the data you need? Why open another cursor?

You can indeed scroll a cursor forward (and backward if defined as SCROLL-able).

What you can't do with a cursor is randomly position into it by Key (aka SETLL or CHAIN).

One thing that trips up lots of RPG developers when moving to SQL, is screens with a POSITION TO. As both users and developers are used to being able to POSITION TO a record and rolling backward to the prior record.
While you can get fancy with SQL, it's usually better to change the mindset of both users and developers and offer various FILTER/SORT BY.

I've rebuilt lots of screens that originally needed a dozen or more logical files to support POSITION TO/SORT BY to use SQL with much higher functioning filter and sort functionality. Once the users got used to it, they always loved the flexibility.

HTH,
Charles



On Mon, Jun 16, 2025 at 12:11 PM Greg Wilburn < gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

Charles/Daniel - thanks!

This seems to work (My date/time fields are numeric).

The users will be viewing the transaction list (like Sean's statement)
in a browser. When they select a row, I'm going to show them what
values changed between the two transactions (the list of fields per
row is much larger than my example).

I guess I was thinking there should be a way to "set the cursor" on my
sorted list, then simply "scroll" to the next value.
Maybe I was trying to get too "fancy", lol.

Thanks again
Greg


-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Charles Wilt
Sent: Monday, June 16, 2025 12:38 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: RPG to SQL Help

This is a good start...But as I read the original post, he's got a
starting date & time...

So the where needs to be
where custno = :your_custno
AND ( (tran_date = :your_date AND tran_time >= :your_time )
OR tran_date > :your_date )

Optionally, you could combine tran_date and tran_time into a
timestamp, that gives an easier comparison.
But depending on the number of records, performance could suffer
unless you add a derived index for the combined timestamp column.

Charles

On Mon, Jun 16, 2025 at 10:28 AM Daniel Gross <daniel@xxxxxxxx> wrote:

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
http://www.totalbizfulfillment.com
main=totalbizfulfillment.com<http://url.us.m.mimecastprotect.com/s
/oNLMCxkVkltV9nrUYiPhyvd9c?domain=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
omain=lists.midrange.com 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.

--
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
ain=lists.midrange.com 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.

--
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
n=lists.midrange.com 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.

Greg Wilburn
Director of IT
301.895.3792 ext. 1231
--
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
n=lists.midrange.com 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.

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

Greg Wilburn
Director of IT
301.895.3792 ext. 1231
--
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.