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

Question

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

Ken (IS/IT--Management)

(OP)

1 Dec 16 23:15

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 (IS/IT--Management) · Answer 1 · 2016-12-02 00:19:50

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

stefanhei (TechnicalUser) · Answer 2 · 2016-12-02 08:22:47

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 (IS/IT--Management) · Answer 3 · 2016-12-04 22:02:11

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 (Programmer) · Answer 4 · 2016-12-05 13:26:04

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

Come Join Us!

Posting Guidelines

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