Monday, November 15, 2004

HAVING

One of the features we recently added to iTQL was HAVING. This is practically identical to SQL's use of HAVING. For example:
SELECT $foo COUNT (SELECT $bar 

FROM ...
WHERE $bar <-> <->)
FROM ...
WHERE $foo <-> <->
HAVING $k0 <tucana:occurs> '1.0'^^<xsd:double> ;

There are a few things that bother me with this. The first one is the implicit column names. All aggregate functions in iTQL are implicity given $kn. Where n is an integer. The variable name should be able to be set by the user; something like: "SELECT $no_people=COUNT ..." or you could copy the SQL 92 use of AS.

Another one is caused by copying SQL. Putting constraints in the WHERE that were meant for the HAVING will produce an error from an SQL interpreter explaining that certain constraints must be in the HAVING. What this really means is that it could've been done in the WHERE clause and have been automatically extracted if necessary.

Also, the use of double should really be changed to nonNegativeInteger.

A good summary of these first two points are highlighted in a presentation "The Importance of Column Names". It is part of the web site of the new version of "The Third Manifesto".

Here's an example:
SELECT D#, COUNT(*)  

FROM EMP
GROUP BY D#
HAVING COUNT(*) >= 50

In SQL:1992 this is equivalent to:
SELECT *  

FROM ( SELECT D#, COUNT(*) AS NUMBER_OF_EMPS
FROM EMP
GROUP BY D# ) AS TEETH_GNASHER
WHERE NUMBER_OF_EMPS >= 50

The Tutorial D version:
( SUMMARIZE EMP  BY { D# } ADD COUNT ( ) AS NUMBER_OF_EMPS ) 

WHERE NUMBER_OF_EMPS >= 50

No comments: