Re: SQL and retrieving the next xx rows -- MIDRANGE-L

Although /processing/ all of the rows _in order to obtain_ the desired result set, if the FETCH FIRST 100 is not working such that the result set enables reading more than 100 rows, I would expect that to be a defect.?

How selective are the ">= 50000" on both files? Is there an index on that column as the first key? An EVI on that column, if not very selective? Probably worthwhile to use Visual Explain to review the implementation, and review for advised INDEX information.

I am not sure if the ALWCPYDTA() feature with the option to disallow temporaries is valid with any UNION, but that could be used if allowed, to force use of an [temporary] index. If disallowing temporary copy of the data is supported for such a query, then CREATE INDEX of the recommended, to avoid the query implementation requiring a full index build for the selected rows each time [versus what it is apparently using now, index, temporaries, and sort].

If it is acceptable to limit the full result set of the UNION data to 100 rows, is it similarly acceptable to limit both of the individual sets that are then combined in the UNION? That can not be known from what is given; and what the actual data is may allow it presently, but it must be valid always, to even consider doing so. Not generally likely, however if so, then generate a limited set from each SELECT using a Common Table Expression for each, and then union those limited sets.

With
CON as ( SELECT CVINST, <<SNIP>>> 'con', RRN(RMSCONPF)
FROM RMSCONPF WHERE CVINST >= 50000
ORDER BY 4, 6, 2
FETCH FIRST ## ROWS ONLY /* 50 or perhaps more rows
to ensure always valid */
,MOR as ( SELECT MTINST, <<SNIP>> 'mor', RRN(RMSMORPF)
FROM RMSMORPF WHERE MTINST >= 50000
ORDER BY 4, 6, 2
FETCH FIRST ## ROWS ONLY /* same comment as above */
select * from CON
union all
select * from MOR
ORDER BY 4, 6, 2
FETCH FIRST 100 ROWS ONLY /* may already be ensured */
OPTIMIZE FOR 20 ROWS FOR READ ONLY /* by now, moot */

FWiW: Given the stated as desired results, a Multi-Format Logical File, probably with dynamic select for xxINST, may be a good choice for use with RLA [Row Level Access], instead of using SQL. If such a keyed DDS LF is created, then with CPYF CRTFILE(*YES) TOFILE(QTEMP/LIMITED) [could even defer dynamic select to the copy request if the literal compare value may change], a MFLF might still be best to enable the SQL to continue to be used, making the SQL a non-UNION select of the copy generated from the MFLF instead of the SQL.

Regards, Chuck

Bradley V. Stone wrote:

Yes, the problem I seem to be having is a UNION ALL. I added a FETCH FIRST clause and I'm still getting all bazillion records
into the result set.

I want to tell it "after 100 rows, just stop looking". But I
suppose it's not that easy using a union.

Here's my SQL now: