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

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.

I smell a big, fat compiler bug
How do you rate this script
SET ROWCOUNT!
Table scan on 0 record temp table?
Parsing a TEXT column in an SP or trigger (SQL SERVER 2000)