>For everyone that must be confused by now, I'm going to restate my original question correctly:
>
>I can't figure out why these two select statements give me different results. The only difference is the where clause.
>
>select sum(scordln.orl_qty-scordln.orl_qtyinvoiced) as nqty ;
>from scordln left outer join scorder ;
>on scordln.orl_order = scorder.ord_order ;
>where between(scorder.ord_acct,1,200) ;
>into cursor cqty
>
>select sum(scordln.orl_qty-scordln.orl_qtyinvoiced) as nqty ;
>from scordln left outer join scorder ;
>on scordln.orl_order = scorder.ord_order ;
>where scorder.ord_acct < 201 ;
>into cursor cqty
>
>I have an accct# 2 and acct# 202.
>The 1st select give results for acct# 2 only.
>The 2nd give results for acct# 1 and acct# 202
>
>Sorry for the confusion. I wish you could edit a message after you set it.
>
>Thanks
>
>Pat Murphy
In the 2nd one, the outer records - i.e., the records for scorder table that don't match the records in the scordln table - are included in the results, and therefore in the sum.
Add the bold line below to make the 2nd one behave like the first.
select sum(scordln.orl_qty-scordln.orl_qtyinvoiced) as nqty ;
from scordln left outer join scorder ;
on scordln.orl_order = scorder.ord_order ;
where scorder.ord_acct < 201 ;
<b>having !isnull(scorder.ord_acct)</b> ;
into cursor cqty
also, for demo purposes, I added the bolded items to the original query. this will show you a little better what's happening.
select <b>scordln.orl_order, scorder.ord_acct, </b>;
sum(scordln.orl_qty-scordln.orl_qtyinvoiced) as nqty ;
from scordln left outer join scorder ;
on scordln.orl_order = scorder.ord_order ;
where scorder.ord_acct < 201 ;
<b>group by scordln.orl_order</b> ;
into cursor cqty
Insanity: Doing the same thing over and over and expecting different results.