Re: Split Query
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:
- No rows for D exist (it wasn't specified in your string) in the result set
- Only 3 of the 5 rows for A have been selected (as specified in your string A: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-553301www.tessella.com Registered in England No. 1466429 Received on Thu Jul 10 2008 - 10:18:29 CDT