In effect there is a difference between CTEs and views!
ORDER BY and FETCH FIRST x ROWS are allowed for CTEs but not for VIEWs.
CTEs are great for structuring complex ad-hoc queries, but in "real live" or
"in production" they should be replaced by views.
Views can be used in other views like any table or physical file.
Since release 7.1 global variables can be created and used within views (and
of course CTEs).
Those views can be set from outside. In this way it is even possible to
generated Views with Recursive CTEs where the starting point can be set
dynamically.
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!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von CRPence
Gesendet: Thursday, 04.4 2013 18:38
An: midrange-l@xxxxxxxxxxxx
Betreff: SQL CTEs; was: SQL: how to join and roll up THREE levels
On 04 Apr 2013 08:37, Stone, Joel wrote:
I was wondering why all of the SQL/400 books I have avoided these
types of queries. I have two SQL books. One has only a paragraph on
CTEs and the other only a page.
I think one could write an entire book on CTEs and where to use them.
SQL/400 is ancient nomenclature, so, that such [titled] books would not
be very current is not surprising ;-)
Seriously though, the CTE [except for recursion] is really nothing more
than what a VIEW can provide; or using multiples of either CTE or VIEW, they
are really no different except management. The CTE is scoped to the query
being run [persists only for the duration], whereas the VIEW has to be
managed as an /object/ on the system. In a script the VIEW is often just as
good if not better; the VIEW(s) can be cleaned up with the DROP VIEW
requests at the end of the script. In a program using the VIEWs can be
somewhat of a nuisance if the scope should be just to the query, because
then abnormal termination needs to DROP VIEW just as does the mainline
processing; obviously if the termination is the job vs the
program\activation-group, then if the VIEWs are created in QTEMP, that is
not a concern.
So other than recursive queries [using a recursive CTE], I think the
coverage in a book could be limited to showing the syntax and stating that
the CTE is just a temporary query-scoped [derived table] that replaces what
would otherwise be created as a VIEW. If there was a way to prevent query
rewrite, so as to force a CTE to run to completion exactly as written and in
the order declared, then *that* could make the potential for use of [a
series of] CTEs much more interesting.
Often however, what the VIEW or a CTE might define can be beneficial for
multiple queries. For that, the VIEW is most desirable. Summary queries as
described in prior messages being encapsulated in VIEW objects, enables
multiple query requests\requesters to reference that static pre-defined
VIEW. Such a VIEW both well-named and registered in the Development
Environment makes the access to that query conspicuous, and assists to
prevent multiply defining the /same/ query in multiple CTEs; albeit possibly
written only effectively the same, perhaps even incorrectly, contrary to the
intent. One would hope that the VIEW would be defined under development
controls to ensure that the VIEW has general applicability for future use,
and is reviewed as such.
FWiW I do not limit myself to DB2 for i /documentation/ [or books
specific to the IBM i platform] when it comes to learning the SQL.
There is plenty of [better] information in DB2LUW-land for general querying
and DML activity. That /documentation/ and information is not just what IBM
provides. I find the best assistance for learning SQL is in examples... and
thus why I try to provide them in my replies [usually tested as functional
with some set of test data; best provided by the inquirer however, because
that may expose unstated assumptions]. I suppose most books on SQL are
written with the intent to provide examples as well [I have never owned
one], because the syntax is likely always very well documented.
--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
