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

Ken · Dec 1, 2016

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

fredericofonseca · Dec 1, 2016

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

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

stefanhei · Dec 2, 2016

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

Ken · Dec 4, 2016

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

Beilstwh · Dec 5, 2016

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

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!

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

Ken

IS-IT--Management

fredericofonseca

IS-IT--Management

stefanhei

Technical User

Ken

IS-IT--Management

Beilstwh

Programmer

Similar threads

Part and Inventory Search

Sponsor