× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. August 2020

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




As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.