Home » SQL & PL/SQL » SQL & PL/SQL » Query based on the other query data (Oracle 10g)
Query based on the other query data [message #357899] Fri, 07 November 2008 01:47 Go to next message
Messages: 1
Registered: November 2008
Junior Member

I have a situation where first query is executed and based upon this query data I am executing second query to get multiple records. These second query returns multiple records and I need to show these multiple records with first query record using some colspan in HTML. So basically all records contains another queries multiple records.

The problem is, let's say If I have 5000 records in first query then second query getting executed 5000 times and it is badly hitting the performance. Another way to achieve this using JOINs but in join query I will get records with data for all attributes of first query.

Please suggest if there any other good way to achieve this to boost the performance.

- Fuel
Re: Query based on the other query data [message #357905 is a reply to message #357899] Fri, 07 November 2008 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 65154
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it possible but you have to post a test case: create table and insert statements along with the result you want with these data as requested in OraFAQ Forum Guide.
Before read "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Re: Query based on the other query data [message #357911 is a reply to message #357899] Fri, 07 November 2008 03:00 Go to previous message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd use analytics.
Specifically, I'd use the LAG function on the parent table to compare the current primary key value with the previous primary key value, and if they're different, then display the values for that row.

Have a try at writing a query like that, and if you have difficulties, we'll give you some more pointers.
Previous Topic: data split by range
Next Topic: dbms_job to run for every 30 seconds daily
Goto Forum:

Current Time: Wed Aug 23 07:07:02 CDT 2017

Total time taken to generate the page: 0.04605 seconds