Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to make SQL sub query work in Oracle, getting error ?

Status
Not open for further replies.

Ken

IS-IT--Management
Jul 13, 2005
68
CA
Hi,

Trying to find out whether below SQL Server - SQL style works in oracle.
On testing SQL in Oracle, it gave Error: Ora-00923 from keyword not found where expected

Code:
SQL Server code:
Select aid, sum(amount)
from  (
        Select t.aid, t.isbn, amt = (t.price * r.royalty)
        from  table as t
        inner  join table2   as r on t.isbn=r.isbn
        left   join table3   as s on t.isbn=s.isbn ) as A
Group by aid

Code:
Oracle Code with eror:
Select DEPTNO, sum(sal), sum(IN_amt)
from   (
        Select e.DeptNO, e.sal as sal, IN_amt = (e.sal * .1) 
        from  emp as e
        ) as A
Group by Deptno
Is there another way to run above Oracle sql query as Subquery similar to SQL Server SQL?

Please help.

Ken
 
try
Code:
Select DEPTNO, sum(sal), sum(IN_amt)
from   (
        Select e.DeptNO, e.sal as sal, IN_amt = (e.sal * .1) 
        from  emp e
        ) A
Group by Deptno
reason is that Oracle does not allow the "as" to define a table alias

And i'm not sure (as I never do it that way) that oracle supports the "int_amt = ..."
you may need to use "(e.sal * .1) as IN_amt" instead

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
Hi,
Oracle allows "as" to define a table alias but does not allow the SQL server style column alias using the equal sign ( IN_amt = (e.sql * .1) ).
Another way to run the query that works on Oracle and SQL Server, but with different syntax is the with clause.
This is the Oracle syntax:
Code:
WITH cte AS (
             SELECT e.deptno, e.sal AS sal, (e.sal * .1) AS in_amt
               FROM emp e
            )
SELECT deptno, SUM(sal), SUM(in_amt)
  FROM cte
 GROUP BY deptno
 
Hi Stefanhei/fredericofonseca,

Thank you for the follow up, SQL works when using 'as' instead of equal sign in Oracle.
Code:
  (e.sal * .1) AS in_amt -> will work  
in_amt =  (e.sal * .1) -> gives same error
Conclusion, Oracle 11g will not accept equal sign 'IN_Amt = (e.sal * .1)'.

Thanks,

Ken
 
why even bother with the sub select the following works fine

Code:
Select e.DeptNO, sum(e.sal) sal, sum(e.sal * .1) in_amt
        from  emp e
group by e.deptNO;

Bill
Lead Application Developer
New York State, USA
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top