From: Neil M. <nm...@zi...> - 2001-12-09 23:05:40
|
On Sun, Dec 09, 2001 at 01:58:54AM -0400, Claudio Valderrama C. wrote: > > -----Original Message----- > > From: fir...@li... > > [mailto:fir...@li...]On Behalf Of > > Christian Pradelli > > Sent: Mircoles 5 de Diciembre de 2001 8:07 > > > > BUT the following combination DOESN'T work: > > > > SELECT CUSTOMER, ABS(SUM(TOTAL)) > > FROM SALES > > GROUP BY CUSTOMER, DIVIDE(ABS(TOTAL),TOTAL) > > // this is the like the last query but returns all values like positives > > > > invalid column reference > > At first glance, it's invalid. The grouping happens on a different UDF than > the SELECT part. However, what makes the case special is the presence of > aggregate functions. I regret to accept that's a valid construction. I don't > know what Neil may suggest, but the only idea I have is: > > - Given a concatenation of udfs, if we hit an aggregate function (count, > sum, avg, min, max), such branch is game over and we don't worry anymore: no > invalid reference. In other words, I think that grouping by fields/udf without them being in the select list is a bad idea but as it allowed for straight fields it should be allowed for udfs. Making aggregates a special case was where I was heading with aggregate_in_list but the udf test was blocking it first in this case. > > - Someone may write > select customer, abs(trunc(sum(total)+field)) > from sales > group by customer, abs(total) > that's invalid, but I prefer that this case goes undetected if it's a > nightmare, because > select customer, abs(trunc(sum(total)*2+3)) > should be accepted. > Adding a check for that was straight forward as it is already recursing the item to check for an aggregate and I don't think I have broken anything else in the process. -- Neil McCalden @home nm...@zi... |