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

That's a great solution, Maria.  A work file of keys a very powerful design pattern, which is why I like it.  The only thing to remember with that approach is that the key file is static, and if the data in the master tables changes, the keys become stale.

I'm still intrigued by the use of row_number(); I never quite understood that.

On 8/4/2020 4:17 AM, Maria Lucia Stoppa wrote:

So, to sum up, my goals were:
- run multiple select on the same filtered data: total number of rows, rows
with pagination, some totals on 'grouped by' clause
- get to maintainable code, not looking for perfection but readable and
robust without duplicating any logic

The final solution I approached is made out of the following:
- create a global temporary table to host the keys of the selected rows, so
any following select will do a join to this table in QTEMP
- the creation of this global temporary table is done in a procedure by
itself, so it can be called anytime from other procedures to ensure the
same keys are fetched given the same filters set
- the total number of rows is calculated within the same select that
returns the rows with pagination by using row_number() olap function, so at
least two results are obtained at the same time
- no commit is set to reduce the execution time as there is no need for it

As I said, this is not perfection, but it runs smoothly and quite fast.

Thank you all again and have a great week.




Il giorno mer 29 lug 2020 alle ore 22:12 Maria Lucia Stoppa <
mlstoppa@xxxxxxxxx> ha scritto:

@John @Joe @Jon
I'm glad my question has arisen so many answers, especially because I know
it's a bit off-topic here.
All your ideas, hits and hints helped me to focus on and develop my
solution, which I will outline here in a while, with the aim to save
headaches to others.
Thank you all

Il giorno mer 29 lug 2020 alle ore 18:16 Jon Paris <
jon.paris@xxxxxxxxxxxxxx> ha scritto:

I agree and would add that including an outline of the major alternatives
considered in your notes can also save future duplication of effort as
folks attempt to "improve" the code.

On Jul 29, 2020, at 12:05 PM, Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>

wrote:

+100 on selecting the most effective solution regardless of elegance

and documenting it. My mentor always told me, "Never let the perfect be
the enemy of the good".

On 7/29/2020 10:56 AM, John Yeung wrote:

Honestly, if you were to just include copious comments explaining
"hey, yeah, this code is ugly, but we need it to run as fast as
possible, and this is the way that we've found runs the fastest", to
me that is perfectly fine.

John Y.

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription

related questions.

Help support midrange.com by shopping at amazon.com with our affiliate

link: https://amazon.midrange.com

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--

Maria Lucia Stoppa
mlstoppa@xxxxxxxxx