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.