Use composite select items in where clause

Question

This question is best phrased with a simple example.

Why can't I do this?

select (lastname + ', ' + firstname) as fullname
from people
where fullname = 'Bloggs, Joe'

instead I have to do this:

select (lastname + ', ' + firstname) as fullname
from people
where (lastname + ', ' + firstname) = 'Bloggs, Joe'

which smells bad to me.

The more complex the query, the worse this problem becomes.

Follow-up

Here is a better example based on the real-world problem from which the question originated.

SELECT ClientID, 
       Name, 
       ContractStartDate, 
       ContractDetails.ContractLength, 
       DATEADD(MONTH, ContractDetails.ContractLength, ContractStartDate) 
           as ContractEndDate
FROM Clients
LEFT OUTER JOIN ContractDetails 
    ON Clients.ClientID = ContractDetails.ClientID
WHERE DATEADD(MONTH, ContractDetails.ContractLength, ContractStartDate) 
      > '2009-06-30'

I have rewritten the query to use an embedded view as suggested. However it still contains repetition - but this time of a join.

SELECT ClientID, 
       Name, 
       contractStartDate, 
       ContractDetails.ContractLength, 
       contractEndDate
FROM (
      SELECT ClientID, 
             Name, 
             ContractStartDate, 
             DATEADD(MONTH, ContractDetails.ContractLength, contractStartdate) 
               AS contractEndDate
      FROM Clients
      LEFT OUTER JOIN ContractDetails 
        on Clients.ClientID = ContractDetails.ClientID
      ) myview
LEFT OUTER JOIN ContractDetails 
  on myview.ClientID = ContractDetails.ClientID
WHERE myview.ContractEndDate > '2009-06-30'
ORDER BY ClientID

The point of this query is to find all live clients as at a particular point in time, where no historical state data is kept (i.e. calculating the contract end date from a known contract start date and length).

Can anyone come up with a way of removing this repetition?

final follow-up

Robin Day helped me out with the key thing I was missing here that actually allowed me to remove the duplication. However KM has a point where he is saying that the WHERE should be on the nested view, rather than the end result, which would require part of the statement to be duplicated (which is what I am trying to avoid). In this particular case I can get away with it because I know there's not millions of records in the ContractDetails table, and never will be.

SELECT ClientID, 
   Name, 
   ContractStartDate, 
   myview.ContractLength, 
   ContractEndDate
FROM (
  SELECT ClientID, 
         Name, 
         ContractStartDate, 
         DATEADD(MONTH, ContractDetails.ContractLength, ContractStartdate) 
           AS ContractEndDate,
         ContractDetails.ContractLength as Length
  FROM Clients
  LEFT OUTER JOIN ContractDetails
    on Clients.ClientID = ContractDetails.ClientID
  ) myview
WHERE myview.ContractEndDate > '2009-06-30'
ORDER BY ClientID

Answer 1

try:

select
    (lastname + ', ' + firstname) as fullname
    from people
    where lastname = 'Bloggs' AND firstname='Joe'

don't filter based on the formatted output "fullname", filter based on the columns, which should be in an index.

EDIT
based on your revised question:

put the condition in the derived table to limit it (and keep it as small as possible). I have seen queries made much faster by doing this alone. I'm sure the query engine is smart enough to not do the DATEADD() twice, so don't worry about it.

SELECT ClientID, 
       Name, 
       contractStartDate, 
       ContractDetails.ContractLength, 
       contractEndDate
FROM (
      SELECT ClientID, 
             Name, 
             ContractStartDate, 
             DATEADD(MONTH, ContractDetails.ContractLength, contractStartdate) 
               AS contractEndDate
      FROM Clients
      LEFT OUTER JOIN ContractDetails 
        on Clients.ClientID = ContractDetails.ClientID
      WHERE DATEADD(MONTH, ContractDetails.ContractLength, contractStartdate) > '2009-06-30'
      ) myview
LEFT OUTER JOIN ContractDetails 
  on myview.ClientID = ContractDetails.ClientID
ORDER BY ClientID

Answer 2

You can used a derived table / nested view...

select
    fullname
from
(
    select
        (lastname + ', ' + firstname) as fullname
    from
        people
) myview
where
    myview.fullname = 'Bloggs, Joe'

EDIT: Just to clarify, this is to show the concept you are asking about. In this specific example your WHERE clause should check firstname = 'Joe' and lastname = 'Bloggs' as KM has answered rather than checking fullname.

Answer 3

The select list is a transformation of a virtual table returned by the from, where and order clauses. The clauses are not aware of the select list. Additionally any transformations of columns defined in the where clause are not sargable and force SQL to perform table or index scans. In other words, to do so would absolutely kill performance.

Answer 4

Isn't your edited example overly complex? What's wrong with:

SELECT *
FROM (
      SELECT ClientID, 
             Name, 
             ContractStartDate, 
             ContractLength,
             DATEADD(MONTH, ContractDetails.ContractLength, contractStartdate) 
               AS contractEndDate
      FROM Clients
      LEFT OUTER JOIN ContractDetails 
        on Clients.ClientID = ContractDetails.ClientID
      ) myview
WHERE myview.ContractEndDate > '2009-06-30'
ORDER BY ClientID