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.