December 24, 2007 at 9:14 am
I'm sure this has been covered over and over, and that I should search the forums for the answer. But I haven't been able to find a nice round answer that covers all occurrences. If I have a conditional statement in the SELECT that returns the value as a column alias, please tell me there is a way to make use of that in the WHERE. Can this be done using HAVING?
December 24, 2007 at 9:52 am
HAVING is used with aggregates. I'm not sure what you mean. Are you saying something like
select *
from mytable
where ID = (select count(*) 'cnt'
from mytable2
)
December 24, 2007 at 10:26 am
Don't really know. The BOL says that in the absence of a GROUP BY, the HAVING acts as a WHERE. Doesn't in practice, though. I just thought that a WHERE filters before the rows are grouped, so using the HAVING would look at the data after the column's been populated. My query has a CASE in the select, and, if a row doesn't match, the value in the computed column will be empty. I was hoping to say "having [computedCol] is not null".
December 25, 2007 at 12:43 am
You can use the same CASE expression (that you used for select) inside the WHERE clause. For example:
-- create a table variable
DECLARE @numbers TABLE (Number INT)
-- fill the table
INSERT INTO @numbers (Number)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5
SELECT
Number,
CASE
WHEN Number % 2 = 0 THEN 'Even'
ELSE NULL
END AS 'IsEven'
FROM @numbers
WHERE
CASE
WHEN Number % 2 = 0 THEN 'Even'
ELSE NULL
END IS NOT NULL
If you want to make this simpler, you can use a CTE
;WITH numbers AS
(
SELECT
Number,
CASE
WHEN Number % 2 = 0 THEN 'Even'
ELSE NULL
END AS 'IsEven'
FROM @numbers
)
SELECT * FROM Numbers
WHERE IsEven IS NOT NULL
You cannot use the column alias in the group by expression. You can use the entire CASE statement just like what I did in the sample code above (for WHERE). Or you can use a CTE.
"SELECT" is done after "GROUP BY". So when at the time of the "evaluation" of "GROUP BY" the column alias does not exist. "ORDER BY" is done after "SELECT" and hence you can use an alias in the "order by" clause.
.
December 27, 2007 at 7:08 am
I knew I couldn't do it in the GROUP BY, but I don't know why it's not allowed in the WHERE. And, I knew I could do it by duplicating the entire statement in the WHERE - it's just a real pain. It seems like it wouldn't be a complicated thing to support an internal reference, either to the resulting value, or to the filtering expression.
December 27, 2007 at 7:16 am
WHERE is evaluated before GROUP BY. Hence you cant use it in WHERE. A CTE will help to simplify the code.
.
December 27, 2007 at 7:23 am
That's useful information: WHERE -> GROUP -> SELECT -> ORDER
Yeah, the CTE is probably the way to go. It's a new tool; I'm not used to thinking about it.
Thanks for the help
December 27, 2007 at 7:56 am
Hi!
Yes, in some cases it simplifies the code. It's probably more readable with CTE.
But a performance issue could arise because this way there are actually two select statements.
I guess it's a problem of readability/maintanability vs performance?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply