Re: Split Query

From: Michael D O'Shea <michael.oshea_at_tessella.com>
Date: Thu, 10 Jul 2008 08:18:29 -0700 (PDT)
Message-ID: <98f439d0-57b7-4a22-a7a9-642c846163fe@x35g2000hsb.googlegroups.com>

On Jul 8, 4:59 pm, Mtek <m..._at_mtekusa.com> wrote:

> Hi,
>
> My requirements have been changed.  I want to know if a query can be
> created that does the following, or am I stuck creating a function in
> PL/SQL.
>
> A string will be available in this format:  A:3,B:2,C:5
>
> Basically, I need to query using the left side of the colon as a
> value, returning the number of records on the right side.
>
> So, in the example above I would get the records matching A, returning
> 3 of them.  Then get records matching B, returning 2 of them.........
>
> I was trying to see if any analytical function is available to do this
> nasty thing.......
>
> Thank you,
>
> John

Hi again John, as Sybrand outlined in the previous post, your approach isn't the right approach and you to think about redesigning your model. If you can't redesign your model however, and this most often happens when other people design things for you, here is something to get you started.

As I understand it, your requirements are:

For a given string of regular format such as 'A:3,B:2,C:5'

From a single table,

    return at most 3 rows that have A as a value     return at most 2 rows that have B as a value     return at most 5 rows that have C as a value

Q. How do you convert between your regular format string like 'A:3,B: 2,C:5' into something relational that you can use in SQL A. Through an SQL construct not unlike the following.

SQL>
SQL>
SQL>
SQL> COLUMN leftHand FORMAT A10
SQL> COLUMN rightHand FORMAT A10
SQL> COLUMN leftHand FORMAT A10
SQL> COLUMN rightHand FORMAT A10
SQL>
SQL> WITH sourceString AS
  2    (
  3       SELECT 'A:3,B:2,C:5' ss
  4        FROM DUAL
  5     )
  6    SELECT REGEXP_SUBSTR(ss,'[A-Z]',1,LEVEL) leftHand,
  7           REGEXP_SUBSTR(ss,'[1-9]',1,LEVEL) rightHand
  8      FROM sourceString
  9          CONNECT BY TRIM(REGEXP_SUBSTR(ss,'[A-Z]+:[0-9]+',

1,LEVEL)) IS NOT NULL; LEFTHAND RIGHTHAND
---------- ----------

A          3
B          2
C          5

SQL>
SQL> To continue with your requirement, I need some test data. Below see the test data I have created.

SQL>
SQL>
SQL> SELECT *

  2 FROM tblTest;

TESTVALUE1 TESTVALUE2
---------- ----------

A          one
A          three
A          four
A          two
A          five
B          one
C          one
C          two
C          three
D          one
D          two

11 rows selected.

SQL>
SQL>
SQL>
SQL>

Building on the example code above to lex out the required predicates, you can then create a sequence order "rn" as shown below using Oracle Analytic Functions. "rn" will be used to filter the result set down further in a Top N like query meeting the threshold being the right hand side of your string, ie in A:3, B:2 the right hand side is 3 and 2 respectively.

SQL>
SQL>
SQL>
SQL>
SQL>  WITH sourceString AS
  2        (
  3           SELECT 'A:3,B:2,C:5' ss
  4            FROM DUAL
  5         ),
  6        relationalView AS
  7        (
  8          SELECT REGEXP_SUBSTR(ss,'[A-Z]',1,LEVEL) leftHand,
  9                 REGEXP_SUBSTR(ss,'[1-9]',1,LEVEL) rightHand
 10            FROM sourceString
 11                CONNECT BY TRIM(REGEXP_SUBSTR(ss,'[A-Z]+:[0-9]+',
1,LEVEL)) IS NOT NULL
 12        )
 13          SELECT t1.testValue1,
 14                 t1.testValue2,
 15                 ROW_NUMBER()
 16                   OVER (
 17                          PARTITION BY t1.testValue1
 18                            ORDER BY t1.testValue1
 19                        ) rn
 20           FROM tblTest t1,
 21                relationalView t2
 22             WHERE t1.testValue1=t2.leftHand;

TESTVALUE1 TESTVALUE2         RN
---------- ---------- ----------
A          one                 1
A          three               2
A          four                3
A          two                 4
A          five                5
B          one                 1
C          one                 1
C          two                 2
C          three               3

9 rows selected.

SQL>
SQL> And finally, use this intermediary result set to filter things down to what you really want.

SQL>
SQL>
SQL>
SQL>
SQL>   WITH sourceString AS
  2         (
  3            SELECT 'A:3,B:2,C:5' ss
  4             FROM DUAL
  5          ),
  6         relationalView AS
  7         (
  8           SELECT REGEXP_SUBSTR(ss,'[A-Z]',1,LEVEL) leftHand,
  9                  REGEXP_SUBSTR(ss,'[1-9]',1,LEVEL) rightHand
 10             FROM sourceString
 11                 CONNECT BY TRIM(REGEXP_SUBSTR(ss,'[A-Z]+:[0-9]+',
1,LEVEL)) IS NOT NULL
 12         ),
 13         intermediary AS
 14          (
 15           SELECT t1.testValue1,
 16                  t1.testValue2,
 17                  t2.rightHand,
 18                  ROW_NUMBER()
 19                    OVER (
 20                           PARTITION BY t1.testValue1
 21                             ORDER BY t1.testValue1
 22                         ) rn
 23            FROM tblTest t1,
 24                 relationalView t2
 25              WHERE t1.testValue1=t2.leftHand
 26          )
 27          SELECT t3.testValue1,
 28                 t3.testValue2
 29           FROM intermediary t3
 30            WHERE rn<=rightHand;

TESTVALUE1 TESTVALUE2
---------- ----------

A          one
A          three
A          four
B          one
C          one
C          two
C          three

7 rows selected.

SQL> Note:

1. No rows for D exist (it wasn't specified in your string) in the result set
2. Only 3 of the 5 rows for A have been selected (as specified in your string A:3)
3. B & C have been included in the result set as the right hand threshold in your string has not been exceeded.

The query is a bit long winded and to fully address your requirements, the requirements need to be detailed. There's enough here to get you started through.

hth

Mike

TESSELLA Michael.OShea_at_tessella.com

__/__/__/  Tessella Support Services plc
__/__/__/  3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/  Tel: (44)(0)1235-555511  Fax: (44)(0)1235-553301

www.tessella.com Registered in England No. 1466429 Received on Thu Jul 10 2008 - 10:18:29 CDT