Filter based on an aliased column name
I'm using SqlServer 2005 and I have a column that I named.
The query is something like:
SELECT id, CASE WHEN <snip extensive column definition> END AS myAlias
FROM myTable
WHERE myAlias IS NOT NULL
However, this gives me the error:
"Invalid column name 'myAlias'."
Is there a way to get around this? In the past I've included the column definition in either the WHERE or the HAVING section, but those were mostly simple, IE COUNT(*) or whatever. I can include the whole column definition in this ad-hoc query, but if for some rea开发者_StackOverflow社区son I needed to do this in a production query I'd prefer to have the column definition only once so I don't have to update both (and forget to do one at some point)
You can't reference aliases in a where clause like that... you either have to duplicate the CASE in the WHERE, or you can use a subquery like this:
SELECT id, myAlias
FROM
(
SELECT id, CASE WHEN <snip extensive column definition> END AS myAlias
FROM myTable
) data
WHERE myAlias IS NOT NULL
Using CTEs is also an option:
;with cte (id, myAlias)
as (select id, case when <snip extensive column definition> end as myAlias
from myTable)
select id, myAlias
from cte
where myAlias is not null
Put the same CASE
statement in the WHERE
clause:
SELECT id, CASE WHEN <snip extensive column definition> END AS myAlias
FROM myTable
WHERE CASE WHEN <snip extensive column definition> END IS NOT NULL
EDIT
Another option is to nest the query:
SELECT id, myAlias
FROM (
SELECT id, CASE WHEN <snip extensive column definition> END AS myAlias
FROM myTable
) AS subTable
WHERE myAlias IS NOT NULL
(Edit: removed HAVING
option, as this was incorrect (thanks @OMG Ponies))
put the case in the where. SQL Server will be smart enough to just evaluate it one time so you aren't really duplicating the code:
SELECT id, CASE WHEN <snip extensive column definition> END AS myAlias
FROM myTable
WHERE CASE WHEN <snip extensive column definition> END IS NOT NULL
you could wrap it in a derived table:
SELECT dt.id, dt.myAlias
FROM (
SELECT id, CASE WHEN <snip extensive column definition> END AS myAlias
FROM myTable
) dt
WHERE dt.myAlias IS NOT NULL
However, I try to avoid having derived tables without a restrictive WHERE. You can try it to see if it affects performance or not.
I ended up creating a temp table to do this. Here is some pseudo code to give you an idea. This worked with a complex join, I am just showing a simple case here.
--Check to see if the temp table already exists
If(OBJECT_ID('tempdb..#TempTable') Is Not Null)
Begin
DROP TABLE #TempTable
End
--Create the temp table
CREATE TABLE #TempTable
{
YourValues NVARCHAR(100)
}
--Insert your data into the temp table
INSERT INTO #TempTable(YourValues)
SELECT yt.Column1 as YourColumnOne FROM YourTable yt
--Query the filtered data based on the aliased column
SELECT *
FROM #TempTable
WHERE YourColumnOne = 'DataToFilterOn'
--Don't forget to remove the temp table
DROP TABLE #TempTable
精彩评论