Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
300 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 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 1600 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

610 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