Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Technique to getting a Top-N query to work as a sub-select in a larger query?, or is there another way to generate Top-N like results?
shaffer911
post Mar 10 2012, 07:29 AM
Post #1


Newbie
*

Group: Members
Posts: 2
Joined: 10-March 12
Member No.: 46,909



Good Morning!!!

Ultimately trying to solve this question…and my brain just is processing correctly this morning.

Question:
Is there a technique to getting a Top-N query to work as a sub-select in a larger query -or- is there another way to generate Top-N like results that works as a sub-select?

Background:

We have a large query that is being used to build an export from a legacy HR system to a new one. Amount the data needed in the export is the employees primary phone number.

The legacy HR system allows multiple phone numbers to be stored in a simple table structure:

SELECT emp_id, phone_type, phone_number
FROM employee_phones

emp_id phone_type phone_number
------- --------------- -------------------
46021 CELL 2222222222
46021 HOME 1111111111
46021 WORK 3333333333

The new HR system does allow for multiple phone numbers, however they need a primary phone number identified and stored with the employee master information. (Subsequent phone numbers get stored in alternate table.)

From a business perspective, we have decided that if they have a HOME phone in the legacy system that should be the primary in the new system, if no HOME phone, then WORK, if no WORK then CELL.

That can be represented as:

SELECT *
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')

emp_id phone_type phone_number
------- --------------- -------------------
46021 HOME 1111111111
46021 WORK 2222222222
46021 CELL 3333333333

Or similarly with Top N concept:

SELECT *
FROM (SELECT *
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1

emp_id phone_type phone_number
------- --------------- -------------------
46021 HOME 1111111111

Or really what I want in my export:

SELECT phone_number
FROM (SELECT phone_number
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1

phone_number
-------------------
1111111111

However, when the Top-N query is added as a sub-select in a larger query using the employee id from the larger query (WHERE emp_id = export.emp_id), it fails saying that “export.emp_id” is not a valid id.

(SELECT phone_number
FROM (SELECT phone_number
FROM employee_people_phones
WHERE emp_id = export.emp_id
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1)

1. Any way around this? Is it possible to put a Top-N (with a WHERE clause using data from the main query) in a sub-select?
2. Any alternatives (other than Top-N) to delivering a ROWNUM=1 result with a “custom” ORDER BY statement?

Other Notes:
Yes, we know we could do two queries in the data conversion first deliver the bulk data to the target table, and then update with the phone numbers. However, for multiple reasons, that is less than desirable.

THANKS!

Steve

Go to the top of the page
 
+Quote Post
burleson
post Mar 10 2012, 09:15 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,623
Joined: 26-January 04
Member No.: 13



Hi Steve,

>> when the Top-N query is added as a sub-select in a larger query using the employee id from the larger query (WHERE emp_id = export.emp_id), it fails saying that “export.emp_id” is not a valid id.

Try using another method than ROWNUM

>> Is there a technique to getting a Top-N query to work as a sub-select in a larger query

Sure. You could put most of these top-n examples as a subquery:

http://www.dba-oracle.com/t_top_n_sql.htm

http://www.dba-oracle.com/oracle_select_top_ten_tips.htm


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
shaffer911
post Mar 10 2012, 12:18 PM
Post #3


Newbie
*

Group: Members
Posts: 2
Joined: 10-March 12
Member No.: 46,909



I am in the process of giving that a try...I've got the rank query working singularly...

Still getting "Invalid Identifer p.emp_id" when I work it into the larger query...(issue with the WHERE p.emp_id = em.emp_id)

SELECT
em.emp_id,
em.first_name,
em.last_name,
em.address,
em.city,
em.state,
em.zip,
(SELECT phone_number
FROM (SELECT phone_number, row_number()
OVER (ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) rnk
FROM employee_phones p
WHERE p.emp_id = em.emp_id)
WHERE rnk = 1) primary_phone
FROM employee_master em


But I fiddle around with some other options.

Thanks,

Steve
Go to the top of the page
 
+Quote Post
rastl
post Mar 14 2012, 04:27 AM
Post #4


Advanced Member
***

Group: Members
Posts: 56
Joined: 12-October 06
Member No.: 5,309



Hello Steve,

according to Oracle support document [ID 374632.1]
"Problem : ORA-904 when using correlated variables in a FROM clause"
this seems to be expected behavior (unsupported mix-up of inline views and correlated subqueries):
QUOTE
So here we have the important line, which is that subqueries in the FROM clause (i.e., "inline views") can only use correlation variables defined within the subquery itself, and not from outer references.

So this is a big difference between correlated subqueries (subquery in a WHERE clause, where this is possible) and inline views (subquery in a FROM clause, where this is not possible).


Try something like this (untested) with analytical functions:

CODE
SELECT * FROM (
SELECT
em.emp_id,
em.first_name,
em.last_name,
em.address,
em.city,
em.state,
em.zip,
primary_phone,
rank () over (partition by em.emp_id order by decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')),p.rownum) rnk
FROM employee_master em
LEFT OUTER JOIN employee_phones p ON p.emp_id = em.emp_id
) WHERE rnk=1


Best regards,
Rainer Stenzel
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 25th October 2014 - 12:27 PM