invalid identifier error in oracle due to column alias

Question

I have a query as follows

select t.col1,
 t.col2,
 (select count(col1)
  from tab 
  where col1 = t.col1
        and col2 = t.col2
   ) as col3 
from tab t
where col3 > 1

The query gives an 'col3 invalid identifier' error.

I have tried different variations defining the alias which I have given below and the error I get when I use them

1. select t.col1,
          t.col2,
          (select count(col1)
           from tab 
           where col1 = t.col1
             and col2 = t.col2
          ) as "col3" 
   from tab t
   where col3 > 1
   

Error: col3 invalid identifier

1. select t.col1,
          t.col2,
          (select count(col1)
           from tab 
           where col1 = t.col1
             and col2 = t.col2
           ) as 'col3' 
   from tab t
   where [col3] > 1
   

Error: Missing expression after where

1. select t.col1,
          t.col2,
          (select count(col1)
           from tab 
           where col1 = t.col1
             and col2 = t.col2
          ) "col3" 
   from tab t
   where [col3] > 1
   

Error: Missing expression after where

Please explain me what the errors are about

P.S. I don't know why I am unable to mark the query examples as code here. I apologize for the poor readability of those queries

Answer 1

Your main problem is that you can't access a column alias on the same "nesting level". In order to be able to use the alias you need to wrap the whole query in a derived table:

select *
from (
  select t.col1,
         t.col2,
         (select count(col1)
          from tab 
          where col1 = t.col1
            and col2 = t.col2
         ) as col3 
  from tab t
) 
where col3 > 1

Your "numbered" examples would not work for two reasons: first for the above reason and secondly because identifiers that are quoted using double quotes become case-sensitive. So "col3" is a different column name than "Col3". As Oracle folds unquoted identifiers to uppercase (following the requirements of the SQL standard) col3 would be equivalent to "COL3"

Finally: [col3] is an invalid identifier in SQL regardless if you use it as a column alias or not. Identifiers must be quoted using double quotes. Those square brackets are invalid in SQL

Answer 2

You cannot use a column alias in a WHERE clause, only in an ORDER BY clause. You have two options.

One, you can wrap you entire query in another select and then filter on col3:

select * from (
select t.col1,
 t.col2,
 (select count(col1)
  from tab 
  where col1 = t.col1
        and col2 = t.col2
   ) as col3 
from tab t)
where col3 > 1;

Or you can repeat the scalar subquery in the WHERE clause:

select t.col1,
 t.col2,
 (select count(col1)
  from tab 
  where col1 = t.col1
        and col2 = t.col2
   ) as col3 
from tab t
where (select count(col1)
  from tab 
  where col1 = t.col1
        and col2 = t.col2
   ) > 1;

I suggest option 1 myself.