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

On Tue, 8 Jul 2008 08:59:39 -0700 (PDT), Mtek <mtek (AT) mtekusa (DOT) com>
wrote:

The basic guideline in relational databases is that it is always more
easy to concatenate as opposed to 'parse'.
You have a repeating group with 2 attributes per group. There is a
'table' hiding in your group.
Oracle supports nested tables.
The 'design' needs to be designed urgently.
Otherwise Garbage In is Garbage Out.

--
Sybrand Bakker
Senior Oracle DBA

On Tue, 8 Jul 2008 08:59:39 -0700 (PDT), Mtek <mtek (AT) mtekusa (DOT) com>
wrote:

The basic guideline in relational databases is that it is always more
easy to concatenate as opposed to 'parse'.
You have a repeating group with 2 attributes per group. There is a
'table' hiding in your group.
Oracle supports nested tables.
The 'design' needs to be designed urgently.
Otherwise Garbage In is Garbage Out.

--
Sybrand Bakker
Senior Oracle DBA

On Tue, 8 Jul 2008 08:59:39 -0700 (PDT), Mtek <mtek (AT) mtekusa (DOT) com>
wrote:

The basic guideline in relational databases is that it is always more
easy to concatenate as opposed to 'parse'.
You have a repeating group with 2 attributes per group. There is a
'table' hiding in your group.
Oracle supports nested tables.
The 'design' needs to be designed urgently.
Otherwise Garbage In is Garbage Out.

--
Sybrand Bakker
Senior Oracle DBA

On Tue, 8 Jul 2008 08:59:39 -0700 (PDT), Mtek <mtek (AT) mtekusa (DOT) com>
wrote:

The basic guideline in relational databases is that it is always more
easy to concatenate as opposed to 'parse'.
You have a repeating group with 2 attributes per group. There is a
'table' hiding in your group.
Oracle supports nested tables.
The 'design' needs to be designed urgently.
Otherwise Garbage In is Garbage Out.

--
Sybrand Bakker
Senior Oracle DBA

On Jul 8, 4:59*pm, Mtek <m... (AT) mtekusa (DOT) com> wrote:


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 (DOT) 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

On Jul 8, 4:59*pm, Mtek <m... (AT) mtekusa (DOT) com> wrote:


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 (DOT) 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

On Jul 8, 4:59*pm, Mtek <m... (AT) mtekusa (DOT) com> wrote:


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 (DOT) 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

On Jul 8, 4:59*pm, Mtek <m... (AT) mtekusa (DOT) com> wrote:


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 (DOT) 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