CODEWITHSUNDEEP
sqlsql-server-2005t-sql
Chris
Chris

Reputation: 371

How do I reference an alias in a WHERE clause?

Here's my statement:

SELECT 
  C.Account, 
  (RTRIM(N.FIRST) + ' ' + RTRIM(LTRIM(N.MIDDLE)) + ' ' + RTRIM(LTRIM(N.LAST)) + ' ' + LTRIM(N.SUFFIX)) AS OwnerName,
  DateAdd(dd, -1, C.ExpirationDate) as RealExpirationDate, 
  C.Description, 
  C.Type
FROM CARD as C
  INNER JOIN NAME as N ON C.Account = N.Account
WHERE (RealExpirationDate BETWEEN @StartDate AND @EndDate)
  AND C.Type IN(10,15,17,25)

I keep getting an error saying that RealExpirationDate is an invalid column name. How can I reference that alias?

Upvotes: 4

Views: 454

Answers (5)

Martin Smith
Martin Smith

Reputation: 453887

You actually shouldn't try to reuse the alias in this case. It isn't sargable (Can't do a range seek on ExpirationDate).

Just use

WHERE C.ExpirationDate 
  BETWEEN DateAdd(dd, 1, @StartDate)  AND DateAdd(dd, 1, @EndDate)

Upvotes: 2

JonH
JonH

Reputation: 33183

You can't in your code above, remember WHERE happens before SELECT, so you'd have to use:

WHERE DateAdd(dd, -1, C.ExpirationDate) BETWEEN @StartDate AND @EndDate

The most common way to alias something like this would be some inner view / query like so:

SELECT
  n.FooBar,  --here we can use FooBar
  t.BarFoo
FROM
  MyTable t
INNER JOIN
(
 SELECT
   myTestCase as FooBar
 From MyTable2
) n

Upvotes: 5

JNK
JNK

Reputation: 65217

In SQL Server you can do this using CROSS APPLY. This is simpler than a subselect, but I'm not sure if there is a performance difference.

SELECT 
  C.Account, 
  (RTRIM(N.FIRST) + ' ' + RTRIM(LTRIM(N.MIDDLE)) + ' ' + RTRIM(LTRIM(N.LAST)) + ' ' + LTRIM(N.SUFFIX)) AS OwnerName,
  RealExpirationDate, 
  C.Description, 
  C.Type
FROM CARD as C
  INNER JOIN NAME as N ON C.Account = N.Account
CROSS APPLY
  (SELECT DateAdd(dd, -1, C.ExpirationDate)) CrossA(RealExpirationDate)
WHERE (RealExpirationDate BETWEEN @StartDate AND @EndDate)
  AND C.Type IN(10,15,17,25)

Upvotes: 1

Oleg Dok
Oleg Dok

Reputation: 21776

Use:

SELECT 
  C.Account, 
  (RTRIM(N.FIRST) + ' ' + RTRIM(LTRIM(N.MIDDLE)) + ' ' + RTRIM(LTRIM(N.LAST)) + ' ' + LTRIM(N.SUFFIX)) AS OwnerName,
  DateAdd(dd, -1, C.ExpirationDate) as RealExpirationDate, 
  C.Description, 
  C.Type
FROM CARD as C
INNER JOIN NAME as N ON C.Account = N.Account
WHERE (DateAdd(dd, -1, C.ExpirationDate) BETWEEN @StartDate AND @EndDate)
AND C.Type IN(10,15,17,25)

or

SELECT * from (
SELECT C.Account, 
  (RTRIM(N.FIRST) + ' ' + RTRIM(LTRIM(N.MIDDLE)) + ' ' + RTRIM(LTRIM(N.LAST)) + ' ' + LTRIM(N.SUFFIX)) AS OwnerName,
  DateAdd(dd, -1, C.ExpirationDate) as RealExpirationDate, 
  C.Description, 
  C.Type
FROM CARD as C
INNER JOIN NAME as N ON C.Account = N.Account
WHERE C.Type IN(10,15,17,25)
) t
WHERE RealExpirationDate BETWEEN @StartDate AND @EndDate

Upvotes: 0

wickedone
wickedone

Reputation: 540

You can't reference an alias. Your query would have to be

SELECT 
  C.Account, 
  (RTRIM(N.FIRST) + ' ' + RTRIM(LTRIM(N.MIDDLE)) + ' ' + RTRIM(LTRIM(N.LAST)) + ' ' + LTRIM(N.SUFFIX)) AS OwnerName,
  DateAdd(dd, -1, C.ExpirationDate) as RealExpirationDate, 
  C.Description, 
  C.Type
FROM CARD as C
  INNER JOIN NAME as N ON C.Account = N.Account
WHERE (DateAdd(dd, -1, C.ExpirationDate) BETWEEN @StartDate AND @EndDate)
  AND C.Type IN(10,15,17,25)

Upvotes: 1

Related Questions