Solved

Convert SQL server query to Oracle 10g query

Posted on 2013-02-05
20
484 Views
Last Modified: 2013-02-09
How can I convert the query below to Oracle 10g query. I am using  Oracle SQLDeveloper.

I am trying to get  the person that has the top 1 salary

SELECT S.empid EmployeeID  ,
       S.ename EmployeeName  ,
     to_char(SUM((I.price) * (OI.qty)) - (S.salary),'$99,999.99') AS  TOPProfit  
  FROM SALESPERSONS S
         LEFT JOIN ORDERS O
          ON S.empid = O.empid
         LEFT JOIN ORDERITEMS OI
          ON O.orderid = OI.orderid
         LEFT JOIN INVENTORY I
          ON OI.partid = I.partid

GROUP BY S.empid, S.ename, S.salary
ORDER BY SUM(I.price) * (OI.qty) - (S.salary) DESC;
0
Comment
Question by:ocdc
  • 11
  • 6
20 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 38858155
based on the calculation you're doing it looks like you might be looking for the employee with the top profits, not the top salary.

if you're really interested in the top salary, do you still want the profits?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 38858156
assuming you want employee with top profits, try this...

select * from
(SELECT s.empid employeeid,
       s.ename employeename,
       TO_CHAR(SUM((i.price) * (oi.qty)) - (s.salary), '$99,999.99') AS topprofit
  FROM salespersons s
       LEFT JOIN orders o ON s.empid = o.empid
       LEFT JOIN orderitems oi ON o.orderid = oi.orderid
       LEFT JOIN inventory i ON oi.partid = i.partid
GROUP BY s.empid, s.ename, s.salary
ORDER BY SUM(i.price) * (oi.qty) - (s.salary) DESC)
where rownum = 1;
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38858157
Have a read of this article explaining how the ROWNUM pseudo-column works in Oracle: http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

The gist of it would be something like this:

select * from 
(SELECT S.empid EmployeeID  ,
       S.ename EmployeeName  ,
     to_char(SUM((I.price) * (OI.qty)) - (S.salary),'$99,999.99') AS  TOPProfit  
  FROM SALESPERSONS S
         LEFT JOIN ORDERS O
          ON S.empid = O.empid
         LEFT JOIN ORDERITEMS OI
          ON O.orderid = OI.orderid
         LEFT JOIN INVENTORY I
          ON OI.partid = I.partid

GROUP BY S.empid, S.ename, S.salary
ORDER BY SUM(I.price) * (OI.qty) - (S.salary) DESC)
where ROWNUM = 1

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 38858162
assuming you want the profits of the employee with top salary try this...


SELECT employeeid, employeename, topprofit
  FROM (SELECT s.empid employeeid,
               s.ename employeename,
               TO_CHAR(
                   SUM((i.price) * (oi.qty)) OVER (PARTITION BY s.empid) - (s.salary),
                   '$99,999.99'
               )
                   AS topprofit,
               ROW_NUMBER() OVER (ORDER BY s.salary DESC) rn
          FROM salespersons s
               LEFT JOIN orders o ON s.empid = o.empid
               LEFT JOIN orderitems oi ON o.orderid = oi.orderid
               LEFT JOIN inventory i ON oi.partid = i.partid)
 WHERE rn = 1
0
 

Author Comment

by:ocdc
ID: 38858229
This query is close to get the top profited employee but it gives me the top less profited employee. (so S.salary in the order by close is not working)  

 How can I make    ORDER BY s.salary DESC  to give the top profited employee. I changed s.salary to topprofit but I am getting invalid identifier error when I do that.. That is because topprofit is not a column name.

SELECT employeeid, employeename, topprofit
  FROM (SELECT s.empid employeeid,
               s.ename employeename,
               TO_CHAR(
                   SUM((i.price) * (oi.qty)) OVER (PARTITION BY s.empid) - (s.salary),
                   '$99,999.99'
               )
                   AS topprofit,
               ROW_NUMBER() OVER (ORDER BY s.salary DESC) rn
          FROM salespersons s
               LEFT JOIN orders o ON s.empid = o.empid
               LEFT JOIN orderitems oi ON o.orderid = oi.orderid
               LEFT JOIN inventory i ON oi.partid = i.partid)
 WHERE rn = 1 ;

------------------------------------------------------------------------------------------------------------------

And If I run the query below I am getting    not a GROUP BY expression error.

select * from
(SELECT S.empid EmployeeID  ,
       S.ename EmployeeName  ,
     to_char(SUM((I.price) * (OI.qty)) - (S.salary),'$99,999.99') AS  TOPProfit  
  FROM SALESPERSONS S
         LEFT JOIN ORDERS O
          ON S.empid = O.empid
         LEFT JOIN ORDERITEMS OI
          ON O.orderid = OI.orderid
         LEFT JOIN INVENTORY I
          ON OI.partid = I.partid

GROUP BY S.empid, S.ename, S.salary
ORDER BY SUM(I.price) * (OI.qty) - (S.salary) DESC)
where ROWNUM = 1 ;
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 38858305
sorry, about the group by thing, I copied your query too closely and it had a bug in it
(it had salary in the group by which isn't correct)

select * from
(SELECT s.empid employeeid,
       s.ename employeename,
       TO_CHAR(SUM((i.price) * (oi.qty)) - (s.salary), '$99,999.99') AS topprofit
  FROM salespersons s
       LEFT JOIN orders o ON s.empid = o.empid
       LEFT JOIN orderitems oi ON o.orderid = oi.orderid
       LEFT JOIN inventory i ON oi.partid = i.partid
GROUP BY s.empid, s.ename
ORDER BY SUM(i.price) * (oi.qty) - (s.salary) DESC)
where rownum = 1;
0
 

Author Comment

by:ocdc
ID: 38859863
After running your modified query, for some reason I am still getting the same  error:

Error starting at line 6 in command:

Line 6.   select * from
(SELECT s.empid employeeid,
       s.ename employeename,
 TO_CHAR((SUM(i.price) * (oi.qty) - (s.salary)), '$99,999.99') AS topprofit
  FROM salespersons s
       LEFT JOIN orders o ON s.empid = o.empid
       LEFT JOIN orderitems oi ON o.orderid = oi.orderid
       LEFT JOIN inventory i ON oi.partid = i.partid
GROUP BY s.empid, s.ename
ORDER BY (SUM(i.price) * (oi.qty) - (s.salary)) DESC)
where rownum = 1

Error at Command Line:9 Column:33 - This line is this:  TO_CHAR((SUM(i.price) * (oi.qty) - (s.salary)), '$99,999.99') AS topprofit

Error report:
SQL Error: ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
*Cause:    
*Action:

---------------------------------------------------------------------------

Also, the query result below is very close to what I need but  if I use  s.salary in  this order statement. ORDER BY s.salary DESC , it gives me the less profited employee instead of top 1 profited  employee.     using topprofit would do it but since it is not an actuall column, I am getting "invalid identifier error" if I replace it with s.salary in the order clause below.

SELECT employeeid, employeename, topprofit
  FROM (SELECT s.empid employeeid,
               s.ename employeename,
               TO_CHAR(
                   SUM((i.price) * (oi.qty)) OVER (PARTITION BY s.empid) - (s.salary),
                   '$99,999.99'
               )
                   AS topprofit,
               ROW_NUMBER() OVER (ORDER BY s.salary DESC) rn
          FROM salespersons s
               LEFT JOIN orders o ON s.empid = o.empid
               LEFT JOIN orderitems oi ON o.orderid = oi.orderid
               LEFT JOIN inventory i ON oi.partid = i.partid)
 WHERE rn = 1 ;
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 38859975
the row number query doesn't apply,  I only suggested that because of ambiguity in what you were looking for.  Now that I know you don't really care about the top salary that no longer applies.
0
 

Author Comment

by:ocdc
ID: 38860115
yes, only need the mots profitable salesperson.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
ID: 38860145
some of the time you have the qty as part of the sum, sometimes you don't.
which is correct?

depending on what is supposed to be in the sum, you may need both salary and qty in the group by or neither or only one of them

I assume it should be but I'd like to confirm
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 38860156
going back to the beginning...

does this return the correct data? but too much and not sorted?

SELECT   s.empid employeeid, s.ename employeename, SUM(i.price * oi.qty) - s.salary profit
    FROM salespersons s
         LEFT JOIN orders o ON s.empid = o.empid
         LEFT JOIN orderitems oi ON o.orderid = oi.orderid
         LEFT JOIN inventory i ON oi.partid = i.partid
GROUP BY s.empid, s.ename, s.salary
0
 

Author Comment

by:ocdc
ID: 38860168
qty should be in the sum:
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 38860271
what about the last query I posted, is the data correct?  it's basically just your original query with extra () removed.

I want to confirm the data is correct, otherwise sorting/filtering on it won't help
0
 

Author Comment

by:ocdc
ID: 38861959
yes, the query gives all the correct records.  Now, need to show only Top 1(topprofited salesperson)

SELECT   s.empid employeeid, s.ename employeename, SUM(i.price * oi.qty) - s.salary profit
    FROM salespersons s
         LEFT JOIN orders o ON s.empid = o.empid
         LEFT JOIN orderitems oi ON o.orderid = oi.orderid
         LEFT JOIN inventory i ON oi.partid = i.partid
GROUP BY s.empid, s.ename, s.salary
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 38861998
select * from
(select * from
(SELECT   s.empid employeeid, s.ename employeename, SUM(i.price * oi.qty) - s.salary profit
    FROM salespersons s
         LEFT JOIN orders o ON s.empid = o.empid
         LEFT JOIN orderitems oi ON o.orderid = oi.orderid
         LEFT JOIN inventory i ON oi.partid = i.partid
GROUP BY s.empid, s.ename, s.salary )
order by profit desc)
where rownum=1
0
 

Author Comment

by:ocdc
ID: 38862040
thanks.  I modified it little to get the topprofited employee instead of less one. so it is working now. so  how can I modify it using Rank function to get the second most profited employee. (that could be the one that with second positive profit or negative profit)

select * from
(select * from
(SELECT   s.empid employeeid, s.ename employeename, to_char(SUM(i.price * oi.qty) - s.salary,'$99,999.99') profit
    FROM salespersons s
         LEFT JOIN orders o ON s.empid = o.empid
         LEFT JOIN orderitems oi ON o.orderid = oi.orderid
         LEFT JOIN inventory i ON oi.partid = i.partid
GROUP BY s.empid, s.ename, s.salary )
order by profit )
where rownum=1 AND PROFIT IS NOT NULL;
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 38862063
you shouldn't put the TO_CHAR on the inner query, that will cause the results to be sorted as text, rather than numerically
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 38871130
I assume you've gotten your answer, you've closed 3 times without asking for more information despite the attempts to open duplicate questions.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now