Re: Issue with an SQL having clause

[Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>]

Does this work?

having coalesce(sqreq-sqfin,0)>  (lqord-lqshp)


It might be that the having expressions must be exactly the same as 
those in the group by clause.  To be honest, "having" often gives me 
headaches.  I usually go to CTEs and end up with something like this:

with t1 as
(select ixprod,ixdesc,extvin,(lqord-lqshp) as ordqty,
coalesce(sqreq-sqfin,0) as openSO
from fix inner join ecl on ixprod=lprod
inner join eclx01 on lord=extord and lline=exline
left outer join fso on ixprod=sprod
group by ixprod,ixdesc,extvin, (lqord-lqshp),
coalesce(sqreq-sqfin,0))

select * from t1 where openSO>  ordqty



Joe

> 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 ixprod,ixdesc,extvin,(lqord-lqshp) as ordqty,
> coalesce(sqreq-sqfin,0) as openSO
> from fix inner join ecl on ixprod=lprod
> inner join eclx01 on lord=extord and lline=exline
> left outer join fso on ixprod=sprod
> 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.
>
> This DOES work:
>
> select ixprod,ixdesc,extvin,(lqord-lqshp) as ordqty,
> coalesce(sqreq-sqfin,0) as openSO
> from fix inner join ecl on ixprod=lprod
> inner join eclx01 on lord=extord and lline=exline
> left outer join fso on ixprod=sprod
> group by ixprod,ixdesc,extvin, (lqord-lqshp),
> coalesce(sqreq-sqfin,0)
> having  coalesce(sqreq-sqfin,0)>  0
>
> Yet with addition of one set of parenthesis in the having clause, it
> will stop working:
>
> select ixprod,ixdesc,extvin,(lqord-lqshp) as ordqty,
> coalesce(sqreq-sqfin,0) as openSO
> from fix inner join ecl on ixprod=lprod
> inner join eclx01 on lord=extord and lline=exline
> left outer join fso on ixprod=sprod
> group by ixprod,ixdesc,extvin, (lqord-lqshp),
> coalesce(sqreq-sqfin,0)
> having  (coalesce(sqreq-sqfin,0))>  0<-----This will not work.
>
> The reason for the parens is so that the having clause is properly
> evaluated, but I must be doing something wrong.
>
> What am I missing?