Oracle SQL: invalid identifier error with newly computed column

I am new to Oracle SQL, and any help will be appreciated! I am trying to run this query:

select ID, birthyear,
substr('&Enter_Date',7,4) Year,
Case When birthday is not null
 Then (Year-birthyear) End age
From client

I got an error message saying the Year is an invalid identifier.I am wondering how to use this newly created column (Year) to compute age.

Answer

You can’t reference it immediately; use the same SUBSTR, again:

select ID, 
       birthyear, 
       substr('&Enter_Date',7,4) Year, 
       --
       Case When birthday is not null Then (substr('&Enter_Date',7,4) - birthyear) 
       End age                              ^^^^^^^^^^^^^^^^^^^^^^^^^^
From client                                 here!

---

Or, calculate year in a subquery, then use it in main query, e.g.

select c.ID,
       c.birthyear,
       x.year,
       --
       case when c.birthday is not null then x.year - c.birthyear
       end age
from client c cross join (select substr('&Enter_Date',7,4) year
                          from dual
                         ) x