Nested selection in the From section

Question

In SQL Server

you can write nested SQL as follows:

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

That way, I can get a temporary table T, which can be nested in another query.

But I can not do the same in oracle SQL

He gives me ORA-01747:invalid column

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

select * works, but that’s not what I want. Does anyone know how to do this?

Answer 1

The request you wrote down is great for me, I specifically specify an alias or specify * . Can you send the exact request that you use when you get an 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 

I assume that you are trying to select con without double quotes. If you use a double-quoted identifier in Oracle, you tell Oracle that you want the identifier to be case-sensitive, which in turn means that you always need to refer to it case-sensitive and you need to include the column name in doubles each time quotes. I would strongly recommend using case sensitive identifiers in Oracle.

Answer 2

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

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