[thelist] SQL Server Select with row filtering problem

Sean G. ethanol at mathlab.sunysb.edu
Tue Jun 18 23:29:01 CDT 2002


Howdy,

If I can go so far as to presume number_allowed is greater than or equal to
0 (or NULL), then for the sum to be greater than 0 one of the addends must
also be greater than 0.

So if you can't build your WHERE clause on the sum, use the addends.

Rather than (WHERE d.num > 0),
try (WHERE (ISNull(a.number_allowed, 0) > 0) OR (IsNull(r.number_allowed, 0)
> 0))


Of course, if either a.number_allowed or r.number_allowed can be less than
zero, it doesn't work out quite the same way.


HTH,


Sean G.


-----Original Message-----
SELECT a.tool_id, t.title,
    CASE
        WHEN a.number_allowed IS NULL THEN 0
        ELSE a.number_allowed
    END
    +
    CASE
        WHEN r.number_allowed IS NULL THEN 0
        ELSE r.number_allowed
    END AS num
FROM tool t
    LEFT OUTER JOIN package_tool_assoc a ON t.id = a.tool_id
    LEFT OUTER JOIN promotion_tool_assoc r ON t.id = r.tool_id
WHERE     (r.promotion_id = 5) OR (a.package_id = 3)

Problem:
I want to only return rows where 'sum' > 0 but SQL Server will not allow
me to reference num from here.  The only solutions I can think of
involve repeating the CASEd sum in the WHERE clause OR turning this
query into a derived table for a second query:





More information about the thelist mailing list