Either/Or/Any query returning erroneous(?) results

I have a query that accepts a parameter CustOwn, which can be 0/1/2
(represents =0/=1/=either 0 or 1)
In theory, by calling the query with CustOwn = 2, it should return the sum
of queries where CustOwn = 0 or CustOwn = 1, however I found I wasnt getting
these anticpated results.
I've created 3 versions of the query to hardcode the query for different
CustOwn values (see below). Apart from the 'and CustOwn = x' line, each
version is identical. Yet they return 17/16/8 records respectively - but the
first query should in theory return the sum of the last two queries. Can any
one suggest why this is the case?
[Apologies for the awful explanation!]
Code Snippet:
/* Both - CustOwn not specified */
Select FA.PartNo, FA.Stock, FOO.OnOrder, (Fa.Stock - FOO.OnOrder)as NumFree,
FA.WIP, (FA.Stock - FOO.OnOrder + FA.WIP) as FreeWIP
from FiltersAvailable FA
inner join (Select PartNo, Count(*) as OnOrder
from
(Select d.PartNo, o.Owned, o.Custorderno,
Case Left(o.custorderno,7)
When 'CustOwn' Then Cast(1 as bit)
Else Cast(0 as bit)
End as CustOwn
from orders o
inner join orderdetail d on d.orderid = o.orderid
where (d.StockMoveOut is null or d.StockMoveOut = '')) as FOO
Where (Owned = 0)
Group By PartNo) FOO on FOO.PartNo = FA.PartNo
order By FA.PartNo
/* CustOwn = 0 */
Select FA.PartNo, FA.Stock, FOO.OnOrder, (Fa.Stock - FOO.OnOrder)as NumFree,
FA.WIP, (FA.Stock - FOO.OnOrder + FA.WIP) as FreeWIP
from FiltersAvailable FA
inner join (Select PartNo, Count(*) as OnOrder
from
(Select d.PartNo, o.Owned, o.Custorderno,
Case Left(o.custorderno,7)
When 'CustOwn' Then Cast(1 as bit)
Else Cast(0 as bit)
End as CustOwn
from orders o
inner join orderdetail d on d.orderid = o.orderid
where (d.StockMoveOut is null or d.StockMoveOut = '')) as FOO
Where (Owned = 0)
and (CustOwn = 0) <===================
Group By PartNo) FOO on FOO.PartNo = FA.PartNo
order By FA.PartNo
/* CustOwn = 1 */
Select FA.PartNo, FA.Stock, FOO.OnOrder, (Fa.Stock - FOO.OnOrder)as NumFree,
FA.WIP, (FA.Stock - FOO.OnOrder + FA.WIP) as FreeWIP
from FiltersAvailable FA
inner join (Select PartNo, Count(*) as OnOrder
from
(Select d.PartNo, o.Owned, o.Custorderno,
Case Left(o.custorderno,7)
When 'CustOwn' Then Cast(1 as bit)
Else Cast(0 as bit)
End as CustOwn
from orders o
inner join orderdetail d on d.orderid = o.orderid
where (d.StockMoveOut is null or d.StockMoveOut = '')) as FOO
Where (Owned = 0)
and (CustOwn = 1) <==================
Group By PartNo) FOO on FOO.PartNo = FA.PartNo
order By FA.PartNo
Thanks
Chris
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]>> Yet they return 17/16/8 records respectively - but the
first query should in theory return the sum of the last two queries. <<
it is sum(OnOrder), not number of rows, that for the
first query should in theory return the sum(OnOrder) of the last two
queries|||Well I'm not surprised nobody has answered by question... lol
I appear to have fixed the problem, but I'm still none the wiser!
I was missing some other criteria out of the inner loop. So when I added
this in, it 'fixed' the results such that I now get was was expected:
(CustOwn = Any) = (CustOwn = 1) + (CustOwn = 0)
I wouldnt have thought this would have made a difference but clearly it
has...
Chris|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
AND cust_own IN (SIGN(@.flag), SIGN(@.flag-2))
(0, -1)
(1, -1)
(1, 0)
Your code stinks. It is nested too deeply. You have BIT datatypes in
SQL! The availabilty of a filter is a status, not an entity.
StockMoveOut can be empty or NULL and they are given the same meaning.
I am going to guess that Ordes had no infomation that should not have
been in Order Details for this query.