• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 506
  • Last Modified:

Convert SQL server query to Oracle 10g query

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
ocdc
Asked:
ocdc
  • 11
  • 6
1 Solution
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
Steve WalesSenior Database AdministratorCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sdstuberCommented:
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
 
ocdcAuthor Commented:
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
 
sdstuberCommented:
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
 
ocdcAuthor Commented:
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
 
sdstuberCommented:
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
 
ocdcAuthor Commented:
yes, only need the mots profitable salesperson.
0
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
ocdcAuthor Commented:
qty should be in the sum:
0
 
sdstuberCommented:
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
 
ocdcAuthor Commented:
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
 
sdstuberCommented:
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
 
ocdcAuthor Commented:
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
 
sdstuberCommented:
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
 
sdstuberCommented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 11
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now