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

Thanks Patrik,

Il giorno mar 28 lug 2020 alle ore 11:31 Patrik Schindler via RPG400-L <
rpg400-l@xxxxxxxxxxxxxxxxxx> ha scritto:

Hello Maria,

Am 28.07.2020 um 09:16 schrieb Maria Lucia Stoppa <mlstoppa@xxxxxxxxx>:

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.


Since DB/2 utilizes a fair amount of caching, I don't see a big deal. But
I get your idea.

Since your regular SELECT returns many rows, and a SELECT COUNT()
precisely one, you can't use them in one statement. I have a dim memory
about a variable in some SQL-internal data structure containing the number
of rows returned by the last query, though. Maybe you want to consult the
manual about embedding SQL in HLL to learn more about it.

I'm already reading the SQL technical reference, you might be referring to
GET DIAGNOSTICS, but I haven't found anything interesting yet. I'll keep on
studying.

There might be errors in my design of how the procedure should work

If you have a very frequent amount of requests triggering these SQLs over
and over again while the results are the same, maybe you could
pre-calculate results into scratch tables in a batch job and use them? Call
it "cache".

The results may not be the same due to the filtering process. The result
set is the same for 8 to 10 different select statements within the same run
though, that's why I hoped the same "with" table could be used for all of
them. Maybe, a global temporary table is of help, or a view to the database
already grouped by.

Any suggestion is really appreciated.

This isn't about RPG, but about SQL, so it's a bit off topic in here. ;-)

I couldn't find any appropriate mailing list, so I ended up here, but you
are definitively right.

:wq! PoC

PGP-Key: DDD3 4ABF 6413 38DE - https://www.pocnet.net/poc-key.asc

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