Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Below is a version that <strike>only gets the <code>max</code> once and </strike> should remove the correlated sub-queries. It does still use sub-queries, but, as they're in the <code>FROM</code> clause rather than the <code>SELECT</code> clause, the database should do a better job of resolving them. It's probably possible to remove those sub-queries as well, but it's more readable this way. This version also uses the SQL-99 syntax for joins, which is generally considered preferable.</p> <pre><code>SELECT table_4.incident_type, table_4.poc_contact, t2.sum_of_shortage, t3.help_notes, table_4.report_num FROM table_4 LEFT JOIN (SELECT table_2.rec_id, table_2.note_date || ' ' || table_1.user_first_name || ' ' || table_1.user_last_name || ' : ' || table_2.other_help_notes AS sum_of_shortage FROM table_1 JOIN table_2 ON table_2.user_id = table_1.user_id WHERE table_2.note_date = (SELECT MAX(table_2.note_date) AS max_date FROM table_2 WHERE table_2.rec_id = table_4.rec_id AND table_2.note_date &lt;= table_4.report_date)) t2 ON t2.rec_id = table_4.rec_id LEFT JOIN (SELECT table_3.rec_id, table_3.note_date || ' ' || table_1.user_first_name || ' ' || table_1.user_last_name || ' : ' || table_3.other_help_notes AS help_notes FROM table_1 JOIN table_3 ON table_3.user_id = table_1.user_id WHERE table_2.note_date = (SELECT MAX(table_3.note_date) AS max_date FROM table_3 WHERE table_3.rec_id = table_4.rec_id AND table_3.note_date &lt;= table_4.report_date)) t3 ON t3.rec_id = table_4.rec_id WHERE table_4.site_id = '1'; </code></pre> <hr> <p>@shawno: You're right, the <code>with</code> clause was flawed because I misread your initial query. Above is a corrected version. Because the <code>max</code> values are specific to each row, the method that you were already using to get those values is probably the most efficient. Your best option for optimizing this appears to just be moving the sub-queries from the <code>select</code> clause to the <code>from</code> clause.</p> <p>Also, this is an untested solution, as I have neither your table structure nor your data. The best I can do without putting far too much work into it is to verified that the syntax is valid.</p>
    singulars
    1. This table or related slice is empty.
    1. PORewrite Query without using correlated subqueries
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USAllan
    1. USAllan
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO+1 for the SQL-99 syntax. IMHO nobody should still be using the old SQL-86 syntax any more.
      singulars
      1. PO
      1. USOllie
    2. CO@Oliie - there are sound reasons for still choosing the old syntax rather than the ANSI syntax. As an example, check out this detailed blog post from Jonathan Lewis: http://jonathanlewis.wordpress.com/2010/12/03/ansi-argh/
      singulars
      1. PO
      1. USAPC
    3. COThanks for your help. However, I don't see how your solution works -- shouldn't you also reference table_4 in the with clause? If i don't then I get an ORA-00904 error - invalid identifier. Also, it seems like that would only produce the max date for all the dates in table_3.note_date (i.e. returns only a single date). I need the max date for every rec_id in table 4 that equals a rec_id in table_3 (where there are many of the same rec_id's, but I only want to match up against the ones with the max date). That's why there is that nested correlated subquery to find the max date. Any ideas?
      singulars
      1. PO
      1. USshawno
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload