Re: Can an SQL common table expression be used for many different selects?

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Hi Maria

I am trying to understand what is happening - I don't know how 1 static 
statement can return a COUNT(*) in one execution and the rows in another 
execution - it can't be the same, since the column list isn't the same - 
but maybe an example can help me.

By dynamic filters, do you mean values from the request that are used 
with host variables? Performance is improved, as you probably know, with 
host variables, because the optimization does not need to be done more 
than once under certain conditions, as I understand it - performance 
guide gives details, I think multiple use in the same RPG module is one 
of those. And in many cases, the ODP for the processing is not 
completely closed, so is available for other values of host variables.

I wonder, without knowing more, if any of the OLAP functions can be 
used. The result set can include various subtotals in addition to the 
data rows.

As to Common Table Expressions - they apply only to the statement of 
which they are a part. They are recommended by the team at IBM, since 
the SQL engine can optimize things very well when there are supporting 
indexes and appropriate JOINs and WHERE clauses. But they are limited to 
the statement - they are not available to other statements, while global 
temporary tables (and temporary result table you also create, maybe in 
QTEMP) are available in the job.

It does seem that if you have many SELECTs over the same temporary 
result set, with different WHERE clauses (not just different values for 
host variables), that a temporary table is needed. But I'm not able to 
say more without seeing more of what the statements look like.

Hope this helps some!
Vern

On 7/28/2020 2:16 AM, Maria Lucia Stoppa wrote:
> Hi all,
> Hope you are well.
>
> A REST API I am working on returns data split into pages: many calls are
> necessary to get the complete set of data, but in this way, the user
> doesn't wait too much for the first page. Data are retrieved within an RPG
> ILE service program by a static SQL statement which applies some dynamic
> filters that come within the request.
>
> Everything works fine, except the same static SQL statement is run at least
> twice to know the total number of rows (a simple count(*)) and the rows
> themselves page by page.
> Now, on the same data retrieved by this SQL statement, others select
> statements must be run to get some totals according to different group by
> clauses in order to present the data set distribution to the final user.
>
> I hardly accepted the idea of running the same SQL statement (which, by
> the way, is pretty complex) at least twice to get both the total and the
> row, but I can't stand the idea of even more runs to get data distribution.
>
> There might be errors in my design of how the procedure should work,
> nonetheless, I wonder if a single common table expression might be used
> many times to serve many different select statements, as I would avoid the
> use of other solutions like global temporary tables.
>
> Any suggestion is really appreciated.
>
> Many thanks