CODEWITHSUNDEEP
sql
JL.
JL.

Reputation: 81262

SQL Dynamic Column Reuse

I have the following SQL statement:

Select 
   DateAdd(month, 1, DateField) as MyNewDate, 
   CASE WHEN MyNewDate < GetDate() THEN 0 ELSE 1 END as Expired
End

I would like to reuse the calculation in the DateAdd without reevaluating the DateAdd for the Expired column. In reality the query is a lot more complex than this simple dateAdd.

The error I get is :

Invalid column name 'MyNewDate'.

How can I reuse the dynamic column?

Upvotes: 1

Views: 123

Answers (2)

Marc
Marc

Reputation: 16512

You can't use an alias in the same query.

You need something like this

SELECT MyNewDate, CASE WHEN MyNewDate < GetDate() THEN 0 ELSE 1 END as Expired
FROM
(
    Select DateAdd(month, 1, DateField) as MyNewDate...
)

or retype it like

Select 
   DateAdd(month, 1, DateField) as MyNewDate, 
   CASE WHEN DateAdd(month, 1, DateField) < GetDate() THEN 0 ELSE 1 END as Expired
End

Upvotes: 3

Gaurav Soni
Gaurav Soni

Reputation: 6338

SELECT MyNewDate,
   CASE WHEN MyNewDate < GetDate() THEN 0 ELSE 1 END as Expired
(
Select 
   DateAdd(month, 1, DateField) as MyNewDate
FROM tab 
)

Upvotes: 0

Related Questions