CODEWITHSUNDEEP
sqloracle-database
MildC
MildC

Reputation: 65

Nested Select in From Clause

In SQL Server,

you can write nested SQL like this:

SELECT T.con
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

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

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

Upvotes: 5

Views: 59912

Answers (2)

Zsolt Botykai
Zsolt Botykai

Reputation: 51593

Well, ORA-01747 occurs usually with reserved words but as far as I know con is not a reserved word. Anyway, try:

select t.* 
  from (select count(*) count_emp from emp) t

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231661

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?

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

       con
----------
        14

SQL> ed
Wrote file afiedt.buf

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

       con
----------
        14

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.

Upvotes: 7

Related Questions