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 Go to next message
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 #576622 is a reply to message #576621] Wed, 06 February 2013 11:29 Go to previous messageGo to next message
Michel Cadot
Messages: 58573
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: Trouble with reqexp_substr and connect by [message #576623 is a reply to message #576622] Wed, 06 February 2013 11:30 Go to previous messageGo to next message
Michel Cadot
Messages: 58573
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also have a look at this post.

Regards
Michel
Re: Trouble with reqexp_substr and connect by [message #576625 is a reply to message #576623] Wed, 06 February 2013 12:08 Go to previous messageGo to next message
Littlefoot
Messages: 19314
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 Go to previous messageGo to next message
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 #576650 is a reply to message #576638] Thu, 07 February 2013 00:12 Go to previous messageGo to next message
Michel Cadot
Messages: 58573
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
My goal is to get a result set that looks like this


Is this not what the post I pointed you to gives?

Regards
Michel
Re: Trouble with reqexp_substr and connect by [message #576654 is a reply to message #576650] Thu, 07 February 2013 00:20 Go to previous messageGo to next message
Littlefoot
Messages: 19314
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;
Re: Trouble with reqexp_substr and connect by [message #576655 is a reply to message #576654] Thu, 07 February 2013 00:21 Go to previous message
Michel Cadot
Messages: 58573
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which is exactly what is in the link I posted.

Regards
Michel
Previous Topic: EXCEL LEADING ZERO
Next Topic: Backup and Purge Data
Goto Forum:
  


Current Time: Fri Jul 25 22:47:28 CDT 2014

Total time taken to generate the page: 0.26498 seconds