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;