[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