All geek questions in one place

How to reference an alias in a WHERE clause?

Here is 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 RealExpirationDate mistakes saying that RealExpirationDate is an invalid column name. How can I refer to this alias?

+4
source share
5 answers

You cannot in your code above, remember that WHERE happens before SELECT , so you will need to use:

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

The most common way aliases of something like this would be some kind of internal view / query, for example:

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

In fact, you should not try to reuse an alias in this case. It is not amenable to movement (cannot search the ExpirationDate range).

Just use

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

You cannot refer to an alias. Your request must 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)
+1
source

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

 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)
+1
source

Using:

 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
0
source

Source: https://habr.com/ru/post/1393975/

More articles:


All Articles