Re: SQL statement to read previous records

On 18-May-2016 10:00 -0500, Darren Strong wrote:

Denis has it. I'll fix up the syntax a bit and add a join to bring
the data together with MFLD columns for the main columns, and ATR
columns for the attribute columns. You could also use a subselect if
you're only bringing in one column from the main table. I didn't test
any of this...

select a.ATR1, a.ATR2, b.MFLD1, b.MFLD2
from FileX a
where a.RcdTyp='ATTRIBUTE'
left outer join lateral
(select MFLD1, MFLD2
from FileX b
where RRN(b)<RRN(a)
and RcdTyp='MAIN'
order by RRN(B)
fetch first row only) B
on 1=1

The where clause in the above would have to go after the join predicate or move into a NTE; or the where clause removed, but that predicate replacing the join predicate, thus including data from the main-records as rows beyond just as additions to the tuples. Also, the file-identifier reference in the RRN(A), I had never known to be supported in that context; for which msg SQL5001 "… table A undefined" would be expected.

With the following changes, the statement should be functional on v7r1:

select a.ATR1, a.ATR2,b.MFLD1, b.MFLD2
from
( select a.*, rrn(a) as rrn_a
from FileX a
where a.RcdTyp='ATTRIBUTE'
) as a
left outer join lateral
(select MFLD1, MFLD2
from FileX b
where RRN(b)<rrn_a /* using RRN_A vs RRN(A) */
and RcdTyp='MAIN'
order by RRN(B) desc /* added DESC per addendum */
fetch first row only
) as B
on 1=1

Alternative as subselect:

select a.ATR1, a.ATR2
, (select MFLD1
from FileX b
where RRN(b)<RRN(a)
and RcdTyp='MAIN'
order by RRN(b)
fetch first row only)
from FileX a
where a.RcdTyp='ATTRIBUTE'

I recalled the restriction on the reference to a file-identifier for the RRN(A) being applied to a subquery as well; apparently incorrectly recalled. However I learned that although the syntax is allowed, even as far back as v5r3, the result of the scalar subselect was always NULL on each release; an apparent defect :-(

With the following changes, the statement should be functional on v7r1:

select a.ATR1, a.ATR2
, (select MFLD1
from FileX b
where RRN(b)<RRN_A /* using RRN_A vs RRN(A) */
and RcdTyp='MAIN'
order by RRN(b) /* added DESC per addendum */
fetch first row only)
from
( select a.*, rrn(a) as rrn_a
from FileX a
where a.RcdTyp='ATTRIBUTE'
)