Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-02-14 : 08:25:32
|
| SoniX writes "We have:SELECT ID, (FIRSTNAME + SURNAME) as FULLNAME from TABLE_NAME where FULLNAME = 'Dave Johnson'The problem is that we cannot use aliases in WHERE clause.Does anybody know a workaround for this situation(other than "SELECT INTO NEW_TABLE" solution)?In the real life I came accros this problem in an application where I have some predefined SQL Queries such as:SELECT ID, (FIRSTNAME + SURNAME) as FULLNAME from TABLE_NAMEand I let the users customize the WHERE clause by providing them a series of comboboxes containing the aliases list.Kind Regards,Mihai" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-02-14 : 08:28:14
|
| 1. Wrap the query inside a subquery:SELECT * FROM (SELECT ID, (FIRSTNAME + SURNAME) as FULLNAME from TABLE_NAME) A where A.FULLNAME = 'Dave Johnson'2. Create a view definition and query that instead:CREATE View myView ASSELECT ID, (FIRSTNAME + SURNAME) as FULLNAME from TABLE_NAMEGOSELECT * FROM myView where FULLNAME = 'Dave Johnson'3. Use the full expression in the WHERE clause:SELECT ID, (FIRSTNAME + SURNAME) as FULLNAME from TABLE_NAME where (FIRSTNAME + SURNAME)= 'Dave Johnson' |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-02-14 : 08:54:48
|
| ... And always remember that filtering on an EXPRESSION will tend to be the most inefficient way to retrieve rows since indexes cannot be used (assuming you have them). |
|
|
|
|
|
|
Workaround for FILTERING USING COLUMN ALIASES ?