Solved

T-SQL: Can you reference a SELECT column expression in the WHERE clause without repeating the entire expression?

Posted on 2010-11-19
5
293 Views
Last Modified: 2012-05-10
The following won't work, right?
select FName+LName as FullName
from Employees
where FullName='Montgomery Scott'
Is there any way to reference the result of the column expression (FName+LName) using relative ordering, like this?
select FName+LName
from Employees
where [Column 0]='Montgomery Scott'
Or are you stuck repeating the entire expression like this?
select FName+LName
from Employees
where FName+LName='Montgomery Scott'
Does someone have a trick or a workaround?

I know this works but I'm trying to avoid a needless nested/derived table:
select * from (select FName+LName as FullName
from Employees) MyDerivedTable
where FullName='Montgomery Scott'
0
Comment
Question by:ZuZuPetals
5 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
ID: 34175986
>Does someone have a trick or a workaround?
sorry, but you have found all the details about this "issue"
you have to repeat the expression, or use a derived table

unless you consider to create, in the table, a computed column "fullname" with that expression ...

0
 
LVL 4

Expert Comment

by:Andre412
ID: 34176287
Im guessing it would be too obvious split the where

select FName+LName as FullName
from Employees
where FullName='Montgomery Scott'

becomes

select FName+LName as FullName
from Employees
where FName= 'Montgomery' and LName = 'Scott'

your only alternative is select from the select

Select fullname From
       (select firstname + ' ' + surname [fullname]
      from [user]) U
      where U.[fullname] like '%fred%'
0
 
LVL 3

Accepted Solution

by:
DFW_Ed earned 400 total points
ID: 34176332
here's the oder of operations:

http://www.bennadel.com/blog/70-SQL-Query-Order-of-Operations.htm

because the compiler hasn't gotten to the "select" list before reading the "where" clause you're not able to use derived columns like you can in an "order by"
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 34185873
No, You cannot directly use the object in where condition. you have to put full expression.
0
 
LVL 2

Author Closing Comment

by:ZuZuPetals
ID: 34188481
The order of operations really helped me--thanks.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sql query 12 67
Set the max value for a column 7 37
CREATE DATABASE ENCRYPTION KEY 1 58
Find data in a column which is not in  a date format 29 18
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now