CODEWITHSUNDEEP
sqloracle-databaseanalyticscorrelated-subquery
Stalin Gino
Stalin Gino

Reputation: 622

Oracle SQL correlated subquery to fetch exactly one row

In an Oracle SQL correlated subquery,

I require to have a condition on the subquery to fetch exactly one row(if there are more rows, it shouldn't fetch any rows).

And the below query works just fine. But it involves querying the same table twice.

SELECT m.pk_1
      ,m.pk_2
      ,m.fld_3
      ,(SELECT c.pk_1
        FROM   child_tab c
        WHERE  (SELECT COUNT(*)
                FROM   child_tab c2
                WHERE  c2.pk_1 = m.pk_1
                AND    rownum <= 2) = 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'

By redesigning the sub-query using analytic function,the below query gives error

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 = m.pk_1
                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'

ORA-00904 m.pk_1 is an invalid identifier

Is there any better way to do this using Oracle SQL?

UPDATE

My real requirement is that I generate this SQL as a recursion process which leads many level like this. And the data I select will not be limited to the key. And I need only SQL solution, so PLSQL is not a choice.

Upvotes: 1

Views: 5534

Answers (2)

Chris Saxon
Chris Saxon

Reputation: 9865

Oracle isn't able to push the value of m.pk_1 into a subquery two levels down. However, given that you're supplying the value for m.pk_1 in your top-level where clause, you could just put this directly into your scalar sub-query like so:

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';

Alternatively, given that m.pk_1 = c.pk_1, you don't need to select c.pk_1 and can just check whether the rows exist using a case statement, returning m.pk_1 if there's only one row:

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';

Finally, you could always create a PL/SQL function to do the lookup for you, returning null when there's too_many_rows. If you have a small number of different values for m.pk_1 and you're using 11g, this could be a result cache function, which should perform well:

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';

Upvotes: 1

Santhosh
Santhosh

Reputation: 1791

Consider this. I do not have any way to execute and check this one. So, please let me know for any issues

   SELECT m.pk_1, m.pk_2, m.fld_3, C.PK1
   FROM MASTER_TAB M, CHILD_TAB C
   WHERE c.pk_1 = m.pk_1 AND m.pk_1 = '018'
   AND    m.pk_2 = 'value'
   AND EXISTS
   (
   SELECT *
   FROM CHILD_TAB C2
   c2.pk_1 = m.pk_1
   HAVING COUNT(*) = 1
   )

Edited:

   SELECT m.pk_1, m.pk_2, m.fld_3, C.PK1
   FROM MASTER_TAB M, (
   SELECT c2.pk_1
   FROM CHILD_TAB C2
   HAVING COUNT(*) = 1
   ) C
   WHERE c.pk_1 = m.pk_1 AND m.pk_1 = '018'
   AND    m.pk_2 = 'value'

Upvotes: 1

Related Questions