This discussion is archived
2 Replies Latest reply: Feb 1, 2013 2:34 PM by Vorlon1 RSS

The best solution to an ugly problem pt 2

Vorlon1 Newbie
Currently Being Moderated
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:
leg:eye
eye

I need construct a query that will return records that meet <=l the minimum criteria. For example, if the request is:
eye:foot
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 Guru
    Currently Being Moderated
    Hi,

    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
    Vorlon1 Newbie
    Currently Being Moderated
    wow....

    {long pause for respect}

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points