Menu
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?
994
Sub queries in the from clause are supported by most of the SQL implementations. The correlation variables from the relations in from clause cannot be used in the sub-queries in the from clause.
First of all, you can put a nested SELECT within the WHERE clause with comparison operators or the IN , NOT IN , ANY , or ALL operators. The second group of operators are used when your subquery returns a list of values (rather than a single value, as in the previous example):
When you put a select statement into a FROM clause, it becomes a subquery. The subquery returns a temporary table in database server's memory and then it is used by the outer query for further processing.
Example -1 : Nested subqueries SELECT job_id,AVG(salary) FROM employees GROUP BY job_id HAVING AVG(salary)< (SELECT MAX(AVG(min_salary)) FROM jobs WHERE job_id IN (SELECT job_id FROM job_history WHERE department_id BETWEEN 50 AND 100) GROUP BY job_id); The above code is executed in Oracle 11g Express Edition.
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.
133
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
