StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Oracle isn't able to push the value of <code>m.pk_1</code> into a subquery two levels down. However, given that you're supplying the value for <code>m.pk_1</code> in your top-level where clause, you could just put this directly into your scalar sub-query like so:</p> <pre><code>SELECT m.pk_1 ,m.pk_2 ,m.fld_3 ,(SELECT ch.pk_1 FROM (SELECT COUNT(*) over() cnt ,c.pk_1 FROM child_tab c WHERE c.pk_1 = '018' AND rownum <= 2) ch WHERE cnt = 1 AND c.pk_1 = m.pk_1) c_pk_1 FROM master_tab m WHERE m.pk_1 = '018' AND m.pk_2 = 'value'; </code></pre> <p>Alternatively, given that <code>m.pk_1 = c.pk_1</code>, you don't need to select <code>c.pk_1</code> and can just check whether the rows exist using a case statement, returning <code>m.pk_1</code> if there's only one row:</p> <pre><code>SELECT m.pk_1 ,m.pk_2 ,m.fld_3 ,CASE WHEN (SELECT COUNT(*) FROM child_tab c WHERE c.pk_1 = m.pk_1 AND rownum <= 2) = 1 THEN m.pk_1 END c_pk_1 FROM master_tab m WHERE m.pk_1 = '018' AND m.pk_2 = 'value'; </code></pre> <p>Finally, you could always create a PL/SQL function to do the lookup for you, returning null when there's <code>too_many_rows</code>. If you have a small number of different values for <code>m.pk_1</code> and you're using 11g, this could be a <a href="http://www.oracle-developer.net/display.php?id=504" rel="nofollow">result cache function</a>, which should perform well:</p> <pre><code>CREATE FUNCTION get_id(m_pk master_tab.pk_1%TYPE) RETURN child_tab.pk_1%TYPE AS retval child_tab.pk_1%TYPE; BEGIN SELECT t.pk_1 INTO retval FROM child_tab t WHERE t.pk_1 = m_pk AND ROWNUM <= 2; RETURN retval; EXCEPTION WHEN TOO_MANY_ROWS THEN RETURN NULL; END; SELECT m.pk_1 ,m.pk_2 ,m.fld_3 ,get_id(m.pk_1) c_pk_1 FROM master_tab m WHERE m.pk_1 = '018' AND m.pk_2 = 'value'; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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