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
redfuel
Messages: 1
Registered: November 2008
Junior Member
Hi,

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: 64103
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).

Regards
Michel
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
JRowbottom
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: ORA-39775: direct path API commit not allowed due to previous fatal error
Next Topic: Urgent-ORA-01445
Goto Forum:
  


Current Time: Sat Dec 03 01:14:02 CST 2016

Total time taken to generate the page: 0.08609 seconds