Re: Puzzle: What should this query return?

Steve Kass <skass_at_drew.edu> wrote in message news:<3D1E8695.62E3BC42_at_drew.edu>...
> Peter,
>
> Itzik Ben-Gan has done a good job of convincing me that the correct
> answer is 12. According to the (SQL-92) standard, if the select list
> contains a set expression, and there is no grouped table, then the result
> set contains exactly one row. In that case, the columns of the result
> set are found be evaluating the value expressions in the select list, which
> in this case, yields 12.
>
> It doesn't matter whether or not the set expression is evaluated or not -
> its presence is enough to force the single-row result set.
>
> I envy you for the opportunity to dig into why Ocelot is foiled, since
> I imagine it will lead not only to a more robust product, but also to
> a better understanding of the standard.
>
It was my understanding that there is a grouped table because "GROUP BY <grand total>" is implied, that that table contains zero rows, that the application of a COUNT function using a table as an argument source is what produces a value equal to the cardinality of the table. I should certainly strive towards a better understanding. Thank you.

Peter Gulutzan
Ocelot Computer Services Inc. Received on Sun Jun 30 2002 - 16:12:48 CEST