RE: Looking for SQL equivalent to RPG

I should have clarified.

The RPG code is already in a service program procedure. I call it all over the place.

I was just looking to create the equivalent using SQL access rather than Native RLA.

Sorry for not stating that clearly.

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Buck Calabro
Sent: Tuesday, October 17, 2023 11:15 AM
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Subject: Re: Looking for SQL equivalent to RPG

On 10/17/2023 10:40 AM, Greg Wilburn wrote:

I have an older procedure that reads records within a file to create a character string from one of the fields (RFDTA) in the file. The resulting string is placed into various data structures, so the "position" is important.

-snip-

This positional/unit record requirement might be the first thing to
reconsider. SQL isn't very good at emulating unit record applications.

I created an SQL statement that works only when all sequence numbers exist.
SELECT rfslc, LISTAGG(RFDTA) WITHIN GROUP(ORDER BY RFCAT, RFSLC, RFSQ2) AS RFDATA
FROM REFERRF
WHERE rfcat=:incat and rfslc=:inslc
GROUP BY RFCAT, RFSLC

Is there a way with SQL to accomplish the same thing without having to read a row at a time?

In my opinion, one of the immense powers of our platform is the ability
to wrap RPG code into an SQL stored procedure. I myself would take this
option. If you worry about duplicating code (who doesn't?), consider how
to refactor the RPG into a subprocedure. Put that into a service
program, then both the existing RPG and the new SQL function/procedure
can reuse that single block of logic, and always return the same output
for the same input.