Re: Issue with an SQL having clause

On 2/3/11 4:57 PM, Pete Helgren wrote:

I need to select records where the total of some fields is greater
than zero. The field comparisons are from two different tables. Here
is the SQL:

select /* <ed> removed AS clauses */
ixprod,ixdesc,extvin,(lqord-lqshp),coalesce(sqreq-sqfin,0)
from <<SNIP joins; same issue irrespective the nbr of files>>
group by
ixprod,ixdesc,extvin,(lqord-lqshp),coalesce(sqreq-sqfin,0)
having
((coalesce(sqreq-sqfin,0)) - (lqord-lqshp)) > 0
<---- It doesn't like this line

I am running this in an interactive SQL session to test it and it is
complaining about the SQREQ column in the having clause.

<<SNIP>>

What am I missing?

Either side\operand of the predicate which is an expression that includes a column name, must match an expression which is in both the SELECT column\expression list and the GROUP BY column\expression list. The two combined expressions (expr1 - expr2) makes a new arithmetic expression on the left of the less than sign; an expression that appears in neither the SELECT nor the GROUP BY. Thus either that new expression must be added to the SELECT and GROUP BY, or each expression must exist [in the HAVING clause] *only* in the same form as in those earlier column\expression lists. Since with a simple algebraic modification the noted predicate can be expressed without any arithmetic, just change the HAVING clause [as suggested by Joe] to:
coalesce(sqreq-sqfin,0)) > (lqord-lqshp)

Oddly the given query seems to include no "total of some fields" for the lack of any SUM aggregate function. Since the given query seems not to have been simplified [for reviewers], perhaps the lack of an aggregate is an oversight?

BTW, the reason the extra parentheses are a problem is that the comparison to a like-expression is done on the parsed result [a parser node?]. Thus A-B is not (A-B), because the latter has a parenthesis in the first byte whereas the former does not. A parsed expression is not "simplified", instead effectively just all of the blanks compressed out of the expression.

Regards, Chuck