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

Let me see if I understand.  Let's assume the first select creates 1000 rows in the static keys table, and then your first pagination selects only 50 of those rows.  In that case, the first row from the pagination select would have a row_number of 50, the second would have 49 and so on, correct?


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

@Joe
Yes, the key file is static, and that's why I have this file recreated each
time the procedure is run, but within the same run I can use the data for
as many selects as needed.

The use of row_number has been suggested by Kyle Lawson, previously in the
thread, and this is what he wrote:
"
Here is another possibility. The CTE at the top would encapsulate your
complex query then in the select use the row_number() olap function to tell
you how many rows are left but to do that you have to order in the opposite
direction your result set is ordered by.

with complex as (
select table_name, table_owner
from qsys2.systables
)
select a.*, row_number() over(order by a.table_name desc)
from complex a
order by a.table_name
"
So, the first fetched row is given the total number of rows.
For the sake of time, I applied the pattern without really study it,
leaving the comprehension of how it works for future idle time.

Hope it helps.

Il giorno mar 4 ago 2020 alle ore 15:02 Joe Pluta <
joepluta@xxxxxxxxxxxxxxxxx> ha scritto:

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

--
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