: SQL, Logical Files, Unions & Indexes.

<snip>
Both ways, we will end up with something hardly readably
(contrary to LFs over multiple Tables that furthermore are referenced in
a
oldfashioned FRF) :
One select takes 2000 chars and more, and I have to unionize 20 regions,

this ends up with ...
</snip>

For this reason, I look to the stored procedure and make it table
driven.

Create a table with two fields.

RunType 10a or something Values like "PROD","TEST", etc.
Library Name

Create one record for each library. My thinking here is just that you
may have multiple libraries for production, testing, etc.

In the stored procedure, just do into a loop for each library for the
type.

Create an SQL Insert Statement in a string, execute direct and put
results into a Global Temporary table. Important to use a Global
Temporary Table. Much faster than using a regular table.

Now return result. If you have multiple ways that you return the result
(Total, Total by Region, etc), create a stored procedure, pass it the
type and have it build the Global Temporary Table and return to caller
to execute SQL to do the result you want.

The advantage here is obvious. If you add a new region, just update the
table.

What drives this how often do you run the query and how efficient does
it have to be?

If it runs a hundred times a day and must have split second response,
you are better to just put the query into a view and update if you need
a new region.

The whole thing is really driven by how long it takes to run one insert.
If it takes a few seconds, time to do twenty isn't going to be much.

This could all be written in the stored procedure language making it
pretty simple. Not much more in RPG.