× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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?



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.