RE: RE: Looking for SQL equivalent to RPG

Buck... Thanks for the suggestion. I briefly considered that. But other applications are storing/changing the data in REFERRF. They are the source of the "missing" sequence numbers.

The file REFERRF is actually the database table that controls the behavior of our entire ERP system.

The RFCAT value I'm trying to get is all the default values for a new customer. I have no idea why they (ERP Software) didn't just put the default customer data in the CUSTOMER MASTER files... ugh. It's what we have.

For now I'll just leave the tried-and-true RPGLE procedure in place. I was just trying to go 100% SQL access.


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

On 10/17/2023 11:56 AM, Greg Wilburn wrote:

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

I tinkered a bit with how to fill in the missing rows, and this is what
I came up with:

-- build a list of all possible RFSQ2 values
-- 20 is a MAGIC NUMBER that corresponds with
-- the maximum number of RFSQ2 values
with rfsq2s (nbr) as (
values(1)
union all
select nbr + 1 from rfsq2s
where nbr < 20),
-- build a list of all possible RFCAT/RFSLC values
rfcat_rfslcs as (
select distinct rfcat, rfslc
from referrf
order by rfcat, rfslc
),
-- meld the full list of RFSQ2s with the
-- full list of RFCAT/RFSLCs with the
-- actually populated rows in REFERRF
-- this effectively synthesises the 'missing' rows in REFERRF
full_rfdtas as (
select
s.rfcat, s.rfslc, nbr as rfsq2, coalesce(rf.rfdta, ' ') as rfdta
from rfsq2s c
cross join rfcat_rfslcs s
left join referrf rf on rf.rfsq2 = c.nbr
order by 1, 2, 3)
-- build out a structure of each RFDTA; actual or synthesised
-- based on it's offset (RFSQ2)
SELECT rfslc, LISTAGG(RFDTA) WITHIN GROUP(ORDER BY RFCAT, RFSLC, RFSQ2)
AS RFDATA
FROM full_rfdtas
WHERE rfcat='DFAC' and rfslc='025'
GROUP BY RFCAT, RFSLC
order by rfcat, rfslc;