Sql – Nested Select in From Clause


In SQL Server,

you can write nested SQL like this:

FROM (SELECT count(*) as "con" FROM EMP) AS T

In such way, I can get a temp table T that can be nested into other query.

But I cannot do the same thing in oracle SQL

It gives me ORA-01747:invalid column

FROM (SELECT count(*) as "con" FROM EMP) T

select * works, but it's not what I want. Anybody knows how to do it?

Best Solution

The query you posted works fine for me whether I specify the alias explicitly or whether I specify a *. Can you post the exact query you're running when you get the exception?

  2  FROM (SELECT count(*) as "con" FROM EMP) T;


SQL> ed
Wrote file afiedt.buf

  1  SELECT "con"
  2* FROM (SELECT count(*) as "con" FROM EMP) T
SQL> /


My guess would be that you're trying to select con without the double-quotes. If you use a double-quoted identifier in Oracle, you're telling Oracle that you want the identifier to be case-sensitive which, in turns, means that you always have to refer to it in a case-sensitive manner and you have to enclose the column name in double quotes every time. I would strongly advise against using case sensitive identifiers in Oracle.