Home » SQL & PL/SQL » SQL & PL/SQL » Trouble with reqexp_substr and connect by (oracle 11)
| Trouble with reqexp_substr and connect by [message #576621] |
Wed, 06 February 2013 11:21  |
 |
vtrapani
Messages: 2
Registered: February 2013
Location: New York
|
Junior Member |
|
|
Hello,
I am a mid level programmer of Java, sql and other scripting languages.
In my company's db we have a few non-normalized fields that contain pipe delimited data which are the primary keys of other tables.
EX. |2345|3456|6789|
I have been looking for as simple a way as possible to iterate through the values between the pipes to get the values that they are mapped to in the table where each is a primary key.
I found regexp_substr and connect by which works only on all the rows in a table with the non-normalized data.
I want to extract only a specific set of rows in that table.
Here is my code that works:
select u.cli_id, user_id, regexp_substr(location_ids,'[^\|]+', 1, level) locId from user_client uc, user u where uc.user_id = u.id
connect by regexp_substr(location_ids, '[^\|]+', 1, level) is not null;
This query takes a few seconds on a table that has thousands of rows
I want to filter out and only return a particular client's rows
What doesn't work is
select u.cli_id, user_id, regexp_substr(location_ids,'[^\|]+', 1, level) locId from user_client uc, user u where uc.user_id = u.id and u.cli_id = 75
connect by regexp_substr(location_ids, '[^\|]+', 1, level) is not null;
This query never stops running and I had to kill the session.
Honestly I have read about connect by and level and don't really understand how they work.
But my goal is to 'split' the field, extract each individual primary key, so I can then get the values for the corresponding table.
I think I am close to a solution which would help me greatly run quick queries on the non-normalized data by creating a view.
I have tried different code combinations and it seems that the only one that works is without filtering the where clause.
I am open to feedback and help.
Thanks for you time.
Vic
|
|
|
|
|
|
|
|
| Re: Trouble with reqexp_substr and connect by [message #576625 is a reply to message #576623] |
Wed, 06 February 2013 12:08  |
 |
Littlefoot
Messages: 16997
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Quote:What doesn't work is ...
Is there any improvement if you add a START WITH clause to that query? Such as
select
u.cli_id,
user_id,
regexp_substr(location_ids,'[^\|]+', 1, level) locId
from user_client uc,
user u
where uc.user_id = u.id
and u.cli_id = 75
start with uc.user_id = u.id
adn u.cli_id = 75
connect by regexp_substr(location_ids, '[^\|]+', 1, level) is not null; |
|
|
|
| Re: Trouble with reqexp_substr and connect by [message #576638 is a reply to message #576625] |
Wed, 06 February 2013 15:44 |
|
vtrapani
Messages: 2
Registered: February 2013
Location: New York
|
Junior Member |
|
|
It does start with 75 returning just a single row for 75 but also the result set includes every other cli_id in the table.
Create table testsplit ( cliID number, usr_id number, locations varchar2(2000));
Insert into testsplit values (12, 22, '|22342|33453|444456|');
Insert into testsplit values (13, 23,'|221342|76578|567894|');
Insert into testsplit values (14, 56,'|214563|23458|456778|908765|');
Insert into testsplit values (13, 67, '|69876|456783|897654|1134|');
Insert into testsplit values (14, 87,'|222145|13458|456778|90965|');
Insert into testsplit values (12, 91, '|9922342|9933453|99444456|');
select regexp_substr((Select locations from testsplit where cliid = 13 and usr_id = 23),'[^\|]+', 1, level) locId from dual --- testsplit where cliid = 13
--start with cliid = 13 --end with cliid = 13 ----and u.client_id = 67 --order by uc.id ----where id = 777
connect by regexp_substr((Select locations from testsplit where cliid = 13 and usr_id =23), '[^\|]+', 1, level) is not null;
returns:
LOCID
-----------------------------------
221342
76578
567894
Which is the beginning of what I want.
My goal is to get a result set that looks like this
cli_id usr_id loc_id
12 22 22342
12 22 33453
12 22 444456
13 23 221342
13 23 76578
13 23 567894
13 67 69876
13 67 456783
13 67 897654
13 67 1134
Any help would be much appreciated.
thanks
|
|
|
|
|
|
| Re: Trouble with reqexp_substr and connect by [message #576654 is a reply to message #576650] |
Thu, 07 February 2013 00:20 |
|
Littlefoot
Messages: 16997
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Test case helps a lot.
select
t.cliid,
t.usr_id,
regexp_substr(locations,'[^\|]+', 1, column_value) locId
from testsplit t,
table(cast(multiset(select level
from dual
connect by level <= length(locations) - length(replace(locations, '|', ''))
) as sys.odcinumberlist))
where regexp_substr(locations, '[^\|]+', 1, column_value) is not null
order by t.cliid, t.usr_id; |
|
|
|
|
|
Goto Forum:
Current Time: Sat May 25 06:35:17 CDT 2013
Total time taken to generate the page: 0.13433 seconds
|
] 
Members
Search
Help
Register
Login
Home
