Aliased Column and Where Clauses

I want to use an aliased field with a where caluse as below

select member_ID,
CASE
WHEN (status_id & (16 | 128)) = (16 | 128) OR (status_id & 16 ) = 0
THEN CAST(1 AS bit) ELSE CAST(0 AS bit)
END AS IsMigrated
FROM member
LEFT OUTER JOIN language ON language.language_id = member.country_id
WHERE language = 'Deutsch'
-- AND IsMigrated = 1

However i keep getting "Invalid column name 'IsMigrated'" when i uncomment the AND IsMigrated = 1 line.

That construct is not available in TSQL; you will need to either need to convert that into a function or "spell it out fully" in the where clause.

Here are a few past threads that had related discussions:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=89097&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1137002&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=803299&SiteID=1

|||

You could try using a derived table, see below.

Chris

Code Snippet

SELECT t.member_ID,

t.IsMigrated

FROM (

SELECT member_ID,

CASE WHEN (status_id & (16 | 128)) = (16 | 128)

OR (status_id & 16) = 0 THEN CAST(1 AS BIT)

ELSE CAST(0 AS BIT)

END AS IsMigrated

FROM member

LEFT OUTER JOIN language ON language.language_id = member.country_id

WHERE language = 'Deutsch'

) t

WHERE t.IsMigrated = 1

|||

The data behind an 'aliased' column is not known by that alias during the data retreival. You cannot use an 'aliased' column name in the SELECT, JOIN conditions, or WHERE criteria of a query.

However, since the data from the query is 'pulled' into a derived table and then sorted, the derived table will know the 'aliased' data by the column name aliases, and you can use the alias in the ORDER BY

|||

I just keep running into this one...

A question to the developement team of SQL: is there any reason why this hasn't been implemented?

The simplest implementation would be to sunbstitue any reference to a derived column with it's definition, by doing a search & replace on the code before feeding it to the interpreter. I can't figure out why it would be difficult to implement or how it would break existing code.

Simple thing like

Col1+Col2 as Sub1,

col3 + col4 as Sub2,

Sub1 + Sub2 as Total

Shouldn't be too hard for the interpreter to figure out? (Access can do it...)

Also something like

select Col1 + '.' + Col2 as Concat,

... bla bla

Group by Concat

Order by Concat

This would really help to keep your 'eye on the ball' instead of on the technical implementation. It would also help maintainability.

So..... next CTP of Katmai it is then? ;-)

Regards,

Gert-Jan

|||

Post your suggestions at:

Suggestions for SQL Server

http://connect.microsoft.com/sqlserver

Aliased Column and Where Clauses

I want to use an aliased field with a where caluse as below

select member_ID,
CASE
WHEN (status_id & (16 | 128)) = (16 | 128) OR (status_id & 16 ) = 0
THEN CAST(1 AS bit) ELSE CAST(0 AS bit)
END AS IsMigrated
FROM member
LEFT OUTER JOIN language ON language.language_id = member.country_id
WHERE language = 'Deutsch'
-- AND IsMigrated = 1

However i keep getting "Invalid column name 'IsMigrated'" when i uncomment the AND IsMigrated = 1 line.

That construct is not available in TSQL; you will need to either need to convert that into a function or "spell it out fully" in the where clause.

Here are a few past threads that had related discussions:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=89097&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1137002&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=803299&SiteID=1

|||

You could try using a derived table, see below.

Chris

Code Snippet

SELECT t.member_ID,

t.IsMigrated

FROM (

SELECT member_ID,

CASE WHEN (status_id & (16 | 128)) = (16 | 128)

OR (status_id & 16) = 0 THEN CAST(1 AS BIT)

ELSE CAST(0 AS BIT)

END AS IsMigrated

FROM member

LEFT OUTER JOIN language ON language.language_id = member.country_id

WHERE language = 'Deutsch'

) t

WHERE t.IsMigrated = 1

|||

The data behind an 'aliased' column is not known by that alias during the data retreival. You cannot use an 'aliased' column name in the SELECT, JOIN conditions, or WHERE criteria of a query.

However, since the data from the query is 'pulled' into a derived table and then sorted, the derived table will know the 'aliased' data by the column name aliases, and you can use the alias in the ORDER BY

|||

I just keep running into this one...

A question to the developement team of SQL: is there any reason why this hasn't been implemented?

The simplest implementation would be to sunbstitue any reference to a derived column with it's definition, by doing a search & replace on the code before feeding it to the interpreter. I can't figure out why it would be difficult to implement or how it would break existing code.

Simple thing like

Col1+Col2 as Sub1,

col3 + col4 as Sub2,

Sub1 + Sub2 as Total

Shouldn't be too hard for the interpreter to figure out? (Access can do it...)

Also something like

select Col1 + '.' + Col2 as Concat,

... bla bla

Group by Concat

Order by Concat

This would really help to keep your 'eye on the ball' instead of on the technical implementation. It would also help maintainability.

So..... next CTP of Katmai it is then? ;-)

Regards,

Gert-Jan

|||

Post your suggestions at:

Suggestions for SQL Server

http://connect.microsoft.com/sqlserver

Aggregate() vs. A Set in the WHERE caluse

Hi,

I recently wrote some VBA code for Excel, which allowed a user to specify a list of members from a particular dimension and then produce a report which aggregated some measures with the members of a different dimension on the rows. It did this by creating an MDX query with the listed members fed into the aggregate function and stored in a calculated member which was then added to the WHERE clause.

eg

WITH MEMBER [Dimension].[Selected_Members] As Aggregate({[Dimension].[Member1], [Dimension].[Member2],.....})
SELECT [Measures].[Measure1] ON COLUMNS,
NON EMPTY [AnotherDimension].Members ON ROWS
FROM [Cube]
WHERE ([Dimension].[Selected_Members])

We found that as the number of members specified increased, the report slowed down dramatically. So, remembering that AS2005 allows you to specify sets in the WHERE clause of your MDX we decided to change it to something like this:

SELECT [Measures].[Measure1] ON COLUMNS,
NON EMPTY [AnotherDimension].Members ON ROWS
FROM [Cube]
WHERE ({[Dimension].[Member1], [Dimension].[Member2],.....})

We were quite surprised to see a very significant improvement in speed and the same results.

So, it would appear that the end result is exactly the same, but I would like to understand what the difference is and why one is so much faster than the other.

Many thanks,

Stuart

|||

I am surprised to see performance difference between these two examples. I would think that the execution plans should be exactly the same. Perhaps there is something going on in the cube.

Please note, that Aggregate cannot always be exchanged to set in WHERE clause, the results could become different. But if the set to Aggregate over is as static as in your example, it should usually be a safe rewrite.