Re: Issue with an SQL having clause

[CRPence <CRPbottle@xxxxxxxxx>]

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