Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Convert SQL server query to Oracle 10g query

Posted on 2013-02-05
Medium Priority
500 Views
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
Question by:ocdc
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 11
• 6

LVL 74

Expert Comment

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 74

Expert Comment

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 23

Expert Comment

ID: 38858157

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
``````
0

LVL 74

Expert Comment

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

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 74

Expert Comment

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

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 74

Expert Comment

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

ID: 38860115
yes, only need the mots profitable salesperson.
0

LVL 74

Expert Comment

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 74

Expert Comment

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

ID: 38860168
qty should be in the sum:
0

LVL 74

Expert Comment

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

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 74

Accepted Solution

sdstuber earned 2000 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

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 74

Expert Comment

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 74

Expert Comment

ID: 38871130
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For manyâ€¦
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates? Â They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", andâ€¦
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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
###### Suggested Courses
Course of the Month8 days, 19 hours left to enroll