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

[Ken]

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

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

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]

try

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]

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:

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]

Hi Stefanhei/fredericofonseca,

Thank you for the follow up, SQL works when using 'as' instead of equal sign in Oracle.

  (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]

why even bother with the sub select the following works fine

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