4

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?

Improve this question
1
  • One of the things that should be on MS's todo list. Commented Jan 31, 2012 at 20:25

5 Answers 5

Reset to default
5

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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

@JNK - its the most common way. Besides this is a wiki like site - edit if you don't like and back it up with examples. Simple right?
Yep. I added an alternate method. I'll edit it for you :) Also +1 for explaining the why (WHERE before SELECT)
2

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)
Improve this answer

Comments

1

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)
Improve this answer

Comments

1

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)
Improve this answer

Comments

0

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
Improve this answer

4 Comments

You'd need t.RealExpirationDate.
@JonH If you insist 8-) But not obligatory
Which of these would be more efficient? I'm assuming the first one?
@Chris - The most efficient way is to not perform a WHERE filter on a calculated expression at all.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.