alias fields Invalid column name

Question

getting this error Invalid column name 'ALIAS1'

what can i do?

SELECT T.firsrdate, T.lastdate ,
T.lastdate - T.firsrdate as ALIAS1,
case when ALIAS1> 15 then 'a' else 'b' end as ALIAS2 FROM Table1

posted the query in comment... thank you!

SELECT T.firsrdate, T.lastdate , T.lastdate - T.firsrdate as ALIAS1, case when ALIAS1> 15 then 'a' else 'b' end as ALIAS2 FROM Table1; — pnetaly, Nov 04 '19 at 12:09
Possible duplicate of [Why can't I use column aliases in the next SELECT expression?](https://stackoverflow.com/questions/34955911/why-cant-i-use-column-aliases-in-the-next-select-expression) — Nick, Nov 04 '19 at 12:12

VBoka · Answer 1 · 2019-11-04T13:08:02.507

If you are using SQLServer then the problem is not only your alias, it is substracting two columns with date type. Also using the alias created in the same select is not ok. Also if you are using T.firstdate expression then you should add capital T after the table name.

In my DEMO

You can see that the substraction of two dates will give an error (query 1 and query2 and query 3 from my DEMO)
You can also see what will happend if you do not use table alias T (query 4 from my DEMO)
And you can see what will happend if you use alias in the select you have created it (query 5 from my DEMO)

I believe this is what you need if you are using SQL Server:

SELECT T.firstdate
       , T.lastdate 
       , datediff(day, T.firstdate , T.lastdate) as Alias1
       , case when (datediff(day, T.firstdate , T.lastdate )) > 15 
            then 'a' 
            else 'b' 
         end as Alias2
FROM Table1 T;

HEre is the demo for that correct example: DEMO.

If you are using ORACLE then the first query will be ok like in this DEMO

SELECT T.firstdate
       , T.lastdate 
       , (T.lastdate - T.firstdate) as ALIAS1
       , case when (T.lastdate - T.firstdate) > 15 then 'a' else 'b' end as ALIAS2 
FROM Table1 T;

So, you see, we need to know what database do you use. :)

Thank you for your response, I did used the Capital T forgot to mention it here. I’m using sql server and your solution works but the “last minus the first date” is a simple example.. the real alias1 is a long code with 4 joins that I don't want to do twice.. I solved it eventually in the SSRS tool but I was sure that there was a solution in sqlserver as there is in oracle.. — pnetaly, Nov 05 '19 at 20:21
Happy to hear you have found a solution. Hope my answer was of at least some help - if so a vote up would be nice :) Thanks and Cheers! — VBoka, Nov 05 '19 at 20:25

score 0 · Answer 2 · answered Nov 04 '19 at 12:11

0

inline alias is not allow all dbms

 SELECT T.firsrdate, T.lastdate ,
T.lastdate - T.firsrdate as ALIAS1,
case when T.lastdate - T.firsrdate> 15 then 'a' else 'b' end as 
 ALIAS2 FROM Table1

answered Nov 04 '19 at 12:11

Zaynul Abadin Tuhin

31,407
5
33
63

score 0 · Accepted Answer · answered Nov 04 '19 at 12:13

It seem you are working with SQL server if so, then you can use apply to avoid repeat same expression :

select t.firstdate, t.lastdate, tt.alias1,
       (case when alias1 > 15 then 'a' else 'b' end) as alias2
from table1 t cross apply
     ( values (t.lastdate - t.firstdate) 
     ) tt(alias1);

alias fields Invalid column name

3 Answers3