Seth Duncan Seth Duncan - 2 months ago 12
SQL Question

Recursive Oracle query

I have three tables:


  • Subscriptions

    SubscriptionID

    SubscriptionName

    Inherits (can be NULL)

  • SubscriptionOptions

    SubscriptionOptionID

    SubscriptionID

    OptionID

    OptionValue

  • Options

    OptionID

    OptionDefaultValue

    Description



I need to pass in a query with multiple options and a subscription ID, something along these lines:

SELECT optionid, optionvalue WHERE subscriptionid = x AND options IN (a, b, c, d, e, f)


Only I need to implement the inherits by logic via a recursive call like this:

if subscriptionoption exists for subscription id & optionid
use subscriptionoption.optionvalue in the row
else
if inherits is not NULL
call this function using inherited subscription id
else
use options.optiondefaultvalue for that optionid

Answer

To do it in SQL, I think you need to incorporate a hierarchical query. Here's a swing at it, but I haven't been able to test it out.

SELECT optionID, NVL( MAX(optionValue), MAX(optionDefaultValue) ) optionValue
FROM (SELECT optionID, optionDefaultValue, subscriptionID, inherits
        FROM options CROSS JOIN subscriptions
        WHERE optionID IN (a,b,c,d,e,f)
     )
     LEFT JOIN subscriptionOptions USING (optionId, subscriptionID)
START WITH subscriptionID = x
CONNECT BY PRIOR optionValue IS NULL
       AND subscriptionID = PRIOR inherits
       AND optionID = PRIOR optionID
GROUP BY optionID

Another approach would be to write a function that implements the recursive logic for a single subscriptionID and optionID, then call it like this:

SELECT optionID, NVL( getSubscriptionOption( x, optionID), optionDefaultValue )
  FROM options
  WHERE optionID IN (a,b,c,d,e,f)

The function could be something like:

FUNCTION getSubscriptionOption( pSubID NUMBER, pOptID NUMBER )
  RETURN subscriptionOptions.optionValue%TYPE
  IS
    l_optionValue subscriptionOptions.optionValue%TYPE;
    l_inherits    subscriptionOptions.inherits%TYPE;
  BEGIN
    SELECT optionValue
      INTO l_optionValue
      FROM subscriptionOptions
      WHERE subscriptionID = pSubID
        AND optionID = pOptID;
    RETURN l_optionValue;
  EXCEPTION
    WHEN no_data_found THEN
      SELECT inherits
        INTO l_inherits
        FROM subscriptions
        WHERE subscriptionID = pSubID;
      IF inherits IS NULL THEN
        RETURN NULL;
      ELSE
        RETURN getSubscriptionOption( l_inherits, pOptID );
      END IF;
  END;

or could be written to use a loop instead of recursion.

Comments