2 Replies Latest reply on Feb 1, 2013 10:34 PM by Vorlon1

    The best solution to an ugly problem pt 2

      I posted a very similar question earlier this week, and got a GREAT solution from Frank Kulash [earlier post|https://forums.oracle.com/forums/message.jspa?messageID=10823001#10823001] , but now I have a different requirement, after trying again and again, I turn to the pros:

      I am looking for the best way to put together a query on a table that has a partial construction like the following:

      Key loss
      --- ---
      1 arm
      2 leg
      3 eye
      4 leg/eye
      5 leg/eye/arm/foot
      6 foot/eye
      7 hand/foot

      (I know, I know. Don't shoot the messenger! this is the table I have to deal with.)
      I have to accept the value from an Apex shuttle object that returns selected values in a colon delimited string. Here are two examples of a potential query string:

      I need construct a query that will return records that meet <=l the minimum criteria. For example, if the request is:
      My query needs to return only records *3* and 6.
      Crazy, right?
      As you can see the order does not matter, but {in this case} the records returned from the query cannot contain any values that were not specifically requested. They may contain less, so long as they too were specifically requested.

      The database is 10.2
      Thanks in advance!
        • 1. Re: The best solution to an ugly problem pt 2
          Frank Kulash

          Here's one way:
          WITH      targets     AS
               SELECT     '%/' || REGEXP_SUBSTR ( :str
                                          , '[^:]+'
                                          , 1
                                          , LEVEL
                         || '/%'          AS target
               ,     target_cnt
               FROM     (
                        SELECT  LENGTH (         :str       || 'ab')
                              - LENGTH (REPLACE (:str, ':') || 'a')     AS target_cnt
                        FROM  dual
               CONNECT BY     LEVEL     <= target_cnt
          SELECT       l.key
          FROM       limbs        l
          JOIN       targets  t  ON   '/' || l.loss
                                        || '/'     LIKE t.target
          GROUP BY  l.key, l.loss
          HAVING       COUNT (*)     = 1 + LENGTH (         l.loss      )
                               - LENGTH (REPLACE (l.loss, '/'))
          You'll notice that this is identical to the solution for your previous question, except that the HAVING clause has changed, and since the HAVING clause now references l.loss, I added l.loss to the GROUP BY clause.

          This assumes that the items in :str and in l.loss are unique. That is, if the user enters 'eye:eye', it will match l.keys 4 and 6, because those keys match 2 times, and there are 2 items in l.loss. If that's a problem, the query above will have to changed a little, but only a little.

          The HAVING clause now says that the number of items that match must be the number of items in l.loss. The way the number of items in l.loss is computed is similar to the way the number of items in the parameter :str is computed. However, the earlier query went to a little trouble in case :str was :NULL. I'm not sure that's necessary, and I assume it's not necessary for l.loss, either.
          • 2. Re: The best solution to an ugly problem pt 2

            {long pause for respect}

            I really owe you the 'adult beverage' of your choice and hope I get the chance sometime.
            Many thanks.