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

I haven't followed this thread, ... but

Will the following SELECT statement not return what you want?

with complex as (select table_name, table_owner, Count(*) over()
TotalNumber

from qsys2.systables)
select a.*
from complex a
order by a.table_name;

An for pageing just use limit and offset:
with complex as (select table_name, table_owner, Count(*) over()
TotalNumber

from qsys2.systables)
select a.*
from complex a
order by a.table_name
Limit 1000 offset 10000;

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 "

Mit freundlichen Grüßen / Best regards

Birgitta Hauser


"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
„Train people well enough so they can leave, treat them well enough so they
don't want to.“ (Richard Branson)


-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Maria
Lucia Stoppa
Sent: Dienstag, 4. August 2020 18:00
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Can an SQL common table expression be used for many different
selects?

Slightly different: the first select creates 1000 rows in the static key
table, then when my first pagination selects 50 rows, the first one is
numbered 1000, the second one 999, the third one 998 and so on and on.
So the first row gives me the total number of selected rows, no matter what
pagination is in use.
Is it clearer?

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

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

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

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