INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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

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

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?

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

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 

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

(OP)
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

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close