dp22+@andrew.cmu.edu (David Bruce Pinkus) (04/17/89)
Hi...
Is there an implementation of SQL around that will allow functions
to be nested in the same where clause?
i.e.
WHERE AVG(Col1 + Col2) = MIN(AVG(Col1+Col2))
Thanks very much,
Dave.dsm@cimshop.UUCP (David Masterson) (04/18/89)
In message <4YGLTiy00VA-MCvmVY@andrew.cmu.edu>, dp22+@andrew.cmu.edu writes: > Is there an implementation of SQL around that will allow functions >to be nested in the same where clause? >i.e. > > WHERE AVG(Col1 + Col2) = MIN(AVG(Col1+Col2)) > What is the MINimum of an AVeraGe? An average is only one number just like a minimum is. An aggregate function takes as input a set of numbers and produces a number as output. Therefore, nesting two aggregate functions like above doesn't make sense, but does the following? WHERE MIN(col1) > MIN(col2 + AVG(col1)) David Masterson uunet!cimshop!dsm or DMasterson@cup.portal.com
midkiff@Thalatta.COM (Michelle Midkiff) (04/20/89)
In article <4YGLTiy00VA-MCvmVY@andrew.cmu.edu> dp22+@andrew.cmu.edu (David Bruce Pinkus) writes: > Is there an implementation of SQL around that will allow functions >to be nested in the same where clause? >i.e. > > WHERE AVG(Col1 + Col2) = MIN(AVG(Col1+Col2)) Yes, I have implemented nested SQL functions in Oracle. -- In-Real-Life: Michelle Midkiff, Thalatta Corporation, (+1 206 455 9838) Domain: midkiff@Thalatta.COM Path: ...!sun!sunup!thebes!midkiff
gupta@cullsj.UUCP (Yogesh Gupta) (04/26/89)
In article <421@cimshop.UUCP>, dsm@cimshop.UUCP (David Masterson) writes: > In message <4YGLTiy00VA-MCvmVY@andrew.cmu.edu>, dp22+@andrew.cmu.edu writes: > > Is there an implementation of SQL around that will allow functions > >to be nested in the same where clause? > >i.e. > > > > WHERE AVG(Col1 + Col2) = MIN(AVG(Col1+Col2)) > > > What is the MINimum of an AVeraGe? An average is only one number just like > a minimum is. An aggregate function takes as input a set of numbers and > produces a number as output. Therefore, nesting two aggregate functions > like above doesn't make sense, but does the following? > > WHERE MIN(col1) > MIN(col2 + AVG(col1)) I do not think why one needs to write the above as a nested function. MIN(col2 + AVG(col1)) == MIN(col2) + AVG(col1) Since an aggregate function returns a single value, it is a constant in an expression that is inside another function, and can be taken out of the expression. Also, since MIN, MAX, SUM, and AVG of a constant are the same constant, the outer function can be eliminated: MIN(col2 + AVG(col1)) == MIN(col2) + MIN(AVG(col1)) (1) == MIN(col2) + AVG(col1) (2) (1) is due to AVG(col1) being a constant, and (2) is due to the fact that the MIN of a constant is that constant. Anyway, I guess this has been belaboured enough. -- Yogesh Gupta | If you think my company will let me Cullinet Software, Inc. | speak for them, you must be joking.