RE: DB2 SQL window functions

["Chris Payne" <CPayne@xxxxxxxxxxxxxxx>]

Rick,
Nope, the over() function modifies the count(*) function. The idea is
that there can be aggregate data and detail data together in the same
query. So while normally you would use a group by if you were going to
use an aggregate function, or not include any columns that were not in
aggregate functions. The window function allows you to include a sum or
count or any other aggregate function, without combining the rows in the
result. Or at least this is what it says in the book, I have no idea how
it works in DB2

Chris

-----Original Message-----
From: midrange-l-bounces+cpayne=thecrowngrp.com@xxxxxxxxxxxx
[mailto:midrange-l-bounces+cpayne=thecrowngrp.com@xxxxxxxxxxxx] On
Behalf Of Rick.Chevalier@xxxxxxxxxxxxxxx
Sent: Monday, February 06, 2006 2:21 PM
To: midrange-l@xxxxxxxxxxxx
Subject: RE: DB2 SQL window functions


Chris,

I'm not familiar with the "window functions" you are referring to but I
do have a question about our syntax.

Should there be a comma between count(*) and over()?  Like this
count(*), over().

Rick

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Chris Payne
Sent: Monday, February 06, 2006 1:13 PM
To: midrange-l@xxxxxxxxxxxx
Subject: DB2 SQL window functions


I recently purchased the SQL cookbook from oreilly, and one of the
topics is something called "window functions". According to the book
this is one of a whole group of analytical functions that was added in
the SQL - 99 standard. I tried to run the very simple query:



SELECT

SUIT,

WEIGHT,

count(*) over()

FROM

cpayne.window



and got the response:



Token ( was not valid. Valid tokens: , FROM INTO.     



Does anyone know about these window functions? Have they been
implemented in DB2/400?

___________________________________
Christopher Payne
CPayne@xxxxxxxxxxxxxxx
System Administrator/Programmer



The Crown Group
Corporate Offices
2111 Walter Reuther Drive
Warren, MI 48091-6199



Phone: (586) 575-9800
Direct: (586) 558-5317
Fax:  (586) 575-9856


Privileged and Confidential.  This e-mail, and any attachments there to,
is intended only for use by the addressee(s) named herein and may
contain privileged or confidential information.  If you have received
this e-mail in error, please notify me immediately by a return e-mail
and delete this e-mail.  You are hereby notified that any dissemination,
distribution or copying of this e-mail and/or any attachments thereto,
is strictly prohibited.