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.
midrange.com
