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

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

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

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

It took me a while myself to discover that trick. If you are "inside" a list (e.g. using * or #) you need to indent by 8 spaces to make it a "code block" - not with 4 spaces as you do normally. — user330315
– user330315, Commented Mar 17, 2014 at 16:09

user330315 · Accepted Answer · 2014-03-17 16:07:58Z

9

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

answered Mar 17, 2014 at 16:07

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Setsuna F. Seiei Over a year ago

Thank you. Greatly appreciate the detailed explanation.

WoMo · Accepted Answer · 2014-03-17 16:09:08Z

3

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.

answered Mar 17, 2014 at 16:09

WoMo

7,2862 gold badges31 silver badges37 bronze badges

2 Comments

ryvantage Over a year ago

"You cannot use a column alias in a WHERE clause" but whyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy -.-

Terai Over a year ago

@ryvantage because WHERE will be executed before the SELECT statement, so the alias isn't known at this step

Collectives™ on Stack Overflow

invalid identifier error in oracle due to column alias

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related