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?
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
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With