Re: Recursive queries?

Clinging to sanity, ronz(at)ravensfield(dot)com (Andrew Rawnsley) mumbled into her beard:
> I haven't had any problems with it so far, although I haven't really
> stressed it yet. I was going to make this very plea...
>
> I agree that the syntax can probably be improved, but its familiar to
> those of us unfortunate enough to have used (or still have to use)
> Oracle. I imagine that bringing it more in line with any standard
> would be what people would prefer.

The SQL:1999 form is instead of the form

with recquery (a,b,c,d) as
(select a1,b1,c1,d1 from some table where d1 > 21)
select * from recquery;

Notice that I have indented this in the same way a Lisp programmer
would indent a LET form...

(let
((a value-for-a)
(b value-for-b)
(c compute-c)
(d 42)) ;;; The ultimate answer...
(compute-something-with-values a b c d))

In ML, there is an analagous "let/in" construct:

#let a = 1 and
b = 2 and
c = 3
in
a + b * c;;
- : int = 7

That example is oversimplified, a bit, as it does not do anything
recursive. In order to express a recursive relationship, the query
likely needs to have a UNION ALL, and look more like the following:

with recquery (a,b,c,d) as
(select a,b,c,d from base_table root -- Root level entries
where c > 200
union all
select child.a,child.b,child.c,child.d
from recquery parent, base_table child -- Self-reference here
where parent.a = child.b -- The link between nodes...
and c > 200)
select a,b,c,d from recquery;

The fact that the form of this resembles that of the Lisp/ML "let"
forms means that WITH can be useful in structuring queries as well.
For instance, supposing you're computing a value that gets used
several times, putting it into a WITH clause might allow evading the
need to compute it more than once.

with notrec (radius, pi, month) as
(select radius, 3.1412, date_trunc('month', txn_date) from pie_table)
select month, sum(pi * radius * radius as area), count(*)
from not_rec
where month between '2003-01-01' and '2004-01-01'
group by month;

has some 'elegance' by virtue of only using date_trunc once over

select date_trunc('month', txn_date), sum(3.1412 * radius*radius) as
area, count(*) from pie_table
where
date_trunc('month', txn_date) between '2003-01-01' and '2004-01-01'
group by month;

Admittedly, date_trunc() may not be an ideal example, as the date
constraint would work as well with an untruncated date the point is
that in the no-WITH approach, there is an extra use of date_trunc().
But the recomputation that takes place when a functional value is used
both in the result clause and in the WHERE clause is something that
WITH can eliminate.
--
"aa454","@","freenet.carleton.ca"
http://www.ntlug.org/~cbbrowne/emacs.html
Lisp Users:
Due to the holiday next Monday, there will be no garbage collection.