column alias in where clause

Hi, I'm trying to migrate a database from Sybase to SQL Server. As far as I
understand (I'm not a db exert), it is not possible in SQL Server to
reference an alias for a column name that is defined in the select-clause in
the where clause. With Sybase this worked, so this kind of thing is widely
spread through the code. Just before I'm going to change all these alias
names to the actual column names: does someone know a way to avoid this,
which means using aliases in where clauses? While I'm at it: could it be the
case that 'IF' statements are not allowed in the select-clause?
Thanx in advance for all replies,
Martin
"Martin" schrieb:
> Hi, I'm trying to migrate a database from Sybase to SQL Server. As far as I
> understand (I'm not a db exert), it is not possible in SQL Server to
> reference an alias for a column name that is defined in the select-clause in
> the where clause. With Sybase this worked, so this kind of thing is widely
> spread through the code. Just before I'm going to change all these alias
> names to the actual column names: does someone know a way to avoid this,
> which means using aliases in where clauses? While I'm at it: could it be the
> case that 'IF' statements are not allowed in the select-clause?
> Thanx in advance for all replies,
> Martin
1. Aliases from the select list work in the where clause!
2. No 'if' in select statements, but 'case' does the trick ...
|||maybe I'm just doing it the wrong way, then (maybe the 'AS' is not really
what's meant by aliasing). But when I try:
SELECT My_ID, My_Some AS SomeAlias
FROM My_Table
WHERE (SomeAlias = 'a')
I get an "invalid colum name" error. I'll give the CASE a try, thanks!
"Christian Donner" wrote:

> "Martin" schrieb:
> 1. Aliases from the select list work in the where clause!
> 2. No 'if' in select statements, but 'case' does the trick ...
|||"Martin" schrieb:

> maybe I'm just doing it the wrong way, then (maybe the 'AS' is not really
> what's meant by aliasing). But when I try:
> SELECT My_ID, My_Some AS SomeAlias
> FROM My_Table
> WHERE (SomeAlias = 'a')
> I get an "invalid colum name" error. I'll give the CASE a try, thanks!
I'm terribly sorry! I mistook 'where' and 'order by' (I hope I will not be
banned from this forum for stupidity). Where clauses do NOT support aliases.
Sorry again for this blunder ...
|||Ok, so I have to rename those. Never mind the mix-up, after all, I got a
'positive' "It can't be done that way!"
"Christian Donner" wrote:

> "Martin" schrieb:
>
> I'm terribly sorry! I mistook 'where' and 'order by' (I hope I will not be
> banned from this forum for stupidity). Where clauses do NOT support aliases.
> Sorry again for this blunder ...
|||On Tue, 14 Jun 2005 02:29:01 -0700, Martin wrote:

>Hi, I'm trying to migrate a database from Sybase to SQL Server. As far as I
>understand (I'm not a db exert), it is not possible in SQL Server to
>reference an alias for a column name that is defined in the select-clause in
>the where clause. With Sybase this worked, so this kind of thing is widely
>spread through the code. Just before I'm going to change all these alias
>names to the actual column names: does someone know a way to avoid this,
>which means using aliases in where clauses? While I'm at it: could it be the
>case that 'IF' statements are not allowed in the select-clause?
>Thanx in advance for all replies,
>Martin
Hi Martin,
You can't use a column alias in the WHERE clause (or any other part of
the query, except ORDER BY). However, there is a workaround that uses
derived tables. This can be especially useful if a column in the SELECT
clause is defined using a lengthy expression and you don't feel like
repeating the expression. Simple example:
SELECT der.Expr1, der.Expr2,
der.Expr1 + der.Expr2,
der.Expr1 * der.Expr2
FROM (SELECT (insert lengtht expression here) AS Expr1,
(put another long expression here) AS Expr2
FROM MyTable
WHERE Col1 + Col2 < Col3) AS der
WHERE der.Expr1 BETWEEN der.Expr2 AND der.Expr2 * 2
AND der.Expr2 <> 0
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)