Home » SQL & PL/SQL » SQL & PL/SQL » Recursive select (Oracle 9i)
Recursive select [message #664008] Mon, 03 July 2017 04:47 Go to next message
banach600
Messages: 14
Registered: June 2017
Junior Member
Hi all,

In an oracle database, we have a table list_elm (id_list, id_elm). An element also groups several lists, so we also have table elm_list (id_elm, id_list).

I would like to select all the lines of list_elm and elm_list starting from a set of lists A. I explain:

For each list of A, it corresponds elements (via list_elm) which in turn corresponds to lists. And we continue until we get all the lists and all the elements.

I add that this query must be executed from a php application (oci_execute).

Thanks to anyone who can help me with this request.

[Updated on: Mon, 03 July 2017 04:49]

Report message to a moderator

Re: Recursive select [message #664012 is a reply to message #664008] Mon, 03 July 2017 06:56 Go to previous messageGo to next message
BlackSwan
Messages: 25745
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Recursive select [message #664024 is a reply to message #664008] Mon, 03 July 2017 11:07 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) 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.

Also always post your Oracle version, with 4 decimals, as solution depends on it.

Re: Recursive select [message #664029 is a reply to message #664024] Mon, 03 July 2017 14:13 Go to previous messageGo to next message
banach600
Messages: 14
Registered: June 2017
Junior Member
Thank you very much for your response. Here is the version of oracle :
SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE	9.2.0.3.0	Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
I have the table LISTE_ELM that links each list to its elements :
CREATE TABLE LISTE_ELM 
(
  COD_LISTE VARCHAR2(2 BYTE) NOT NULL 
, COD_ELM VARCHAR2(2 BYTE) NOT NULL 
, CONSTRAINT LRE_PK PRIMARY KEY 
  (
    COD_LISTE 
  , COD_ELM 
  )
  ENABLE 
) 

CREATE UNIQUE INDEX LRE_PK ON LISTE_ELM (COD_LISTE ASC, COD_ELM ASC) 

REM INSERTING into LISTE_ELM
SET DEFINE OFF;
Insert into LISTE_ELM (COD_LISTE,COD_ELM) values  ('LA','EA');
Insert into LISTE_ELM (COD_LISTE,COD_ELM) values  ('LB','EB');
Insert into LISTE_ELM (COD_LISTE,COD_ELM) values  ('LC','EC');
Insert into LISTE_ELM (COD_LISTE,COD_ELM) values  ('LC','ED');
Insert into LISTE_ELM (COD_LISTE,COD_ELM) values  ('LC','EE');
Insert into LISTE_ELM (COD_LISTE,COD_ELM) values  ('LD','EF');
Insert into LISTE_ELM (COD_LISTE,COD_ELM) values  ('LD','EG');
Insert into LISTE_ELM (COD_LISTE,COD_ELM) values  ('LD','EH');
Insert into LISTE_ELM (COD_LISTE,COD_ELM) values  ('LE','EG');
Insert into LISTE_ELM (COD_LISTE,COD_ELM) values  ('LF','EH');
Insert into LISTE_ELM (COD_LISTE,COD_ELM) values  ('LH','EH');
Since to every element we can associate lists, we have also the table :
CREATE TABLE ELM_LISTE 
(
  COD_ELM VARCHAR2(2 BYTE) NOT NULL 
, COD_LISTE VARCHAR2(2 BYTE) NOT NULL 
, CONSTRAINT ERL_PK PRIMARY KEY 
  (
    COD_ELM 
  , COD_LISTE 
  )
  ENABLE 
) 

CREATE UNIQUE INDEX ERL_PK ON ELM_LISTE (COD_ELM ASC, COD_LISTE ASC) 

REM INSERTING into ELM_LISTE
SET DEFINE OFF;
Insert into ELM_LISTE (COD_ELM,COD_LISTE) values ('EA','LB');
Insert into ELM_LISTE (COD_ELM,COD_LISTE) values ('EB','LC');
Insert into ELM_LISTE (COD_ELM,COD_LISTE) values ('EG','LF');
Insert into ELM_LISTE (COD_ELM,COD_LISTE) values ('EH','LH');
From a php application (using oci), I need to select (Perhaps with two queries) all lines of these two tables that contain elements and lists associated to the list 'LA'. More precisely, For the list 'LA' I want obtain

  • from LIST_ELM the lines : ('LA','EA'), ('LB','EB'), ('LC','EC'), ('LC','ED'), ('LC','EE'), ('LD','EF'), ('LD','EG'), ('LD','EH')
  • and from ELM_LIST the lines : ('EA','LB'), ('EB','LC').
In the oracle documentation I found that for recursive select one can use WITH Clause. But, as my knowledge of oracle is limited I could not make this request. Thank you for your help.


Re: Recursive select [message #664030 is a reply to message #664029] Mon, 03 July 2017 14:55 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I want obtain
Explain the rules to come to this result.

And what do you want to actually display?

Quote:
In the oracle documentation I found that for recursive select one can use WITH Clause.
This does not exist in Oracle 9i.

[Updated on: Mon, 03 July 2017 15:04]

Report message to a moderator

Re: Recursive select [message #664116 is a reply to message #664030] Tue, 04 July 2017 14:12 Go to previous messageGo to next message
banach600
Messages: 14
Registered: June 2017
Junior Member
We get these lines as follows : the lise LA contains element EA (from LIST_ELM) for which the list LB is associated (using ELM_LIST). By this we obtain (LA, EA) and (EA,LB). The list LB contains the element EB, and EB includes LC. Hence we have also (LB, EB) and (EB,LC). LC contains EC, ED, EE, and so we have (LC,EC), (LC,ED), (LC,EE). Now since there are no lists associated to the above tree elements, the selection stops here.

Thank you for your interest
Re: Recursive select [message #664117 is a reply to message #664116] Tue, 04 July 2017 14:35 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
And what do you want to actually display?
I think you don't want to display:
Quote:
from LIST_ELM the lines : ('LA','EA'), ('LB','EB'), ('LC','EC'), ('LC','ED'), ('LC','EE'), ('LD','EF'), ('LD','EG'), ('LD','EH')
and from ELM_LIST the lines : ('EA','LB'), ('EB','LC').
To give query we need to know what should be its result.

Re: Recursive select [message #664118 is a reply to message #664117] Tue, 04 July 2017 15:09 Go to previous messageGo to next message
banach600
Messages: 14
Registered: June 2017
Junior Member
I want to display the lines (LA,EA), (LB,EB), (LC,EC), (LC,ED), (LC,EE) from LIST_ELM, and the lines (EA,LB), (EB,LC) from ELM_LIST.

There was a mistake in the message you cited. The lines (LD, EF), (LD,EG), (LD, EH) should not be displayed.
Re: Recursive select [message #664119 is a reply to message #664118] Tue, 04 July 2017 15:11 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Are you sure you want to display the specific sentence "the lines (LA,EA), (LB,EB), (LC,EC), (LC,ED), (LC,EE) from LIST_ELM, and the lines (EA,LB), (EB,LC) from ELM_LIST."?

Re: Recursive select [message #664123 is a reply to message #664119] Tue, 04 July 2017 15:32 Go to previous messageGo to next message
banach600
Messages: 14
Registered: June 2017
Junior Member
Yes I want to select from the two tables list_elm and elm_list the lines I mentioned in the previous message.
Re: Recursive select [message #664124 is a reply to message #664123] Tue, 04 July 2017 15:36 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You don't understand my question.
Do you want your users have on their screen this exact result:
the lines (LA,EA), (LB,EB), (LC,EC), (LC,ED), (LC,EE) from LIST_ELM, and the lines (EA,LB), (EB,LC) from ELM_LIST.
NOT what it means but exactly written as it is here.

[Updated on: Tue, 04 July 2017 15:37]

Report message to a moderator

Re: Recursive select [message #664126 is a reply to message #664008] Tue, 04 July 2017 16:01 Go to previous messageGo to next message
banach600
Messages: 14
Registered: June 2017
Junior Member
Thanks for asking me. In reality, these lines :
Table LIST_ELM :
(LA,EA)
(LB,EB)
(LC,EC)
(LC,ED)
(LC,EE) 

Table ELM_LIST : 
(EA,LB), 
(EB,LC)
must be selected from a php application (using oci) and then injected into a MySql database.
Re: Recursive select [message #664127 is a reply to message #664126] Tue, 04 July 2017 16:54 Go to previous messageGo to next message
banach600
Messages: 14
Registered: June 2017
Junior Member
If you think that a sentence is easy to get that these lines, I suggest one of the form

(LA,EA)#(LB,EB)#(LC,EC)#(LC,ED)#(LC,EE)/(EA,LB)#(EB,LC)

After that I will use php explode to extract my lines.
Re: Recursive select [message #664128 is a reply to message #664127] Tue, 04 July 2017 18:03 Go to previous messageGo to next message
BlackSwan
Messages: 25745
Registered: January 2009
Location: SoCal
Senior Member
banach600 wrote on Tue, 04 July 2017 14:54
If you think that a sentence is easy to get that these lines, I suggest one of the form

(LA,EA)#(LB,EB)#(LC,EC)#(LC,ED)#(LC,EE)/(EA,LB)#(EB,LC)

After that I will use php explode to extract my lines.
>(LC,EE)/(EA,LB)
what significance of the "/" character above?
Re: Recursive select [message #664129 is a reply to message #664128] Tue, 04 July 2017 18:07 Go to previous messageGo to next message
banach600
Messages: 14
Registered: June 2017
Junior Member
The character "/" is used for separating lines of list_elm from lines of elm_list.
Re: Recursive select [message #664132 is a reply to message #664129] Wed, 05 July 2017 00:11 Go to previous messageGo to next message
bbob
Messages: 21
Registered: July 2017
Junior Member
so?
with
  list_elm(a, b) as (
    select 'LA' , 'EA' from dual
    union all select 'LB' , 'EB' from dual
    union all select 'LC' , 'EC' from dual
    union all select 'LC' , 'ED' from dual
    union all select 'LC' , 'EE' from dual
    ),
  elm_list(a, b) as (
    select 'EA' , 'LB' from dual
    union all select 'EB' , 'LC' from dual
    ),
  combine(t, a, b) as (
    select 'list_elm', a.* from list_elm a
    union all select 'elm_list', a.* from elm_list a)
select
  t,
  lpad(' ', level * 2, ' ') || a || ' - ' || b as r
  from combine a
  connect by prior b = a and t <> prior t
  start with t = 'list_elm' and a = 'LA'

result:
----------+-------------------
T         |R                  
----------+-------------------
list_elm  |  LA - EA          
elm_list  |    EA - LB        
list_elm  |      LB - EB      
elm_list  |        EB - LC    
list_elm  |          LC - EC  
list_elm  |          LC - ED  
list_elm  |          LC - EE  
Re: Recursive select [message #664135 is a reply to message #664132] Wed, 05 July 2017 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Stupid, why don't you rad what has been posted, this is invalid in 9i.
SQL> with
  2    list_elm(a, b) as (
  3      select 'LA' , 'EA' from dual
  4      union all select 'LB' , 'EB' from dual
  5      union all select 'LC' , 'EC' from dual
  6      union all select 'LC' , 'ED' from dual
  7      union all select 'LC' , 'EE' from dual
  8      ),
  9    elm_list(a, b) as (
 10      select 'EA' , 'LB' from dual
 11      union all select 'EB' , 'LC' from dual
 12      ),
 13    combine(t, a, b) as (
 14      select 'list_elm', a.* from list_elm a
 15      union all select 'elm_list', a.* from elm_list a)
 16  select
 17    t,
 18    lpad(' ', level * 2, ' ') || a || ' - ' || b as r
 19    from combine a
 20    connect by prior b = a and t <> prior t
 21    start with t = 'list_elm' and a = 'LA'
 22  /
  list_elm(a, b) as (
          *
ERROR at line 2:
ORA-32033: unsupported column aliasing
Re: Recursive select [message #664137 is a reply to message #664135] Wed, 05 July 2017 01:01 Go to previous messageGo to next message
bbob
Messages: 21
Registered: July 2017
Junior Member
sorry man, i don't have oracle 9 installed to test it.
you can modify sql yourself.
i posted it to make sure that the result is correct.

ps. "stupid" is correct usage here? ok, i'll use it for too Smile

[Updated on: Wed, 05 July 2017 01:02]

Report message to a moderator

Re: Recursive select [message #664138 is a reply to message #664127] Wed, 05 July 2017 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
After that I will use php explode to extract my lines.

Why don't you want to get several lines you actually want to inject into your MySql database instead of asking to make work to merge the columns and lines that your php program will have to explode?

Like that:
SQL> col col1 format a4
SQL> col col2 format a4
SQL> with
  2    data as (
  3      select 'LISTE_ELM' tab, cod_liste col1, cod_elm col2
  4      from liste_elm
  5      union all
  6      select 'ELM_LISTE', cod_elm, cod_liste
  7      from elm_liste
  8    )
  9  select tab, col1, col2
 10  from data
 11  connect by prior tab != tab and prior col2 = col1
 12  start with tab = 'LISTE_ELM' and col1 = 'LA'
 13  order by row_number() over (order by tab desc)
 14  /
TAB       COL1 COL2
--------- ---- ----
LISTE_ELM LA   EA
LISTE_ELM LB   EB
LISTE_ELM LC   EC
LISTE_ELM LC   ED
LISTE_ELM LC   EE
ELM_LISTE EA   LB
ELM_LISTE EB   LC

7 rows selected.
Re: Recursive select [message #664139 is a reply to message #664137] Wed, 05 July 2017 02:23 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i don't have oracle 9 installed to test it.

But you have the documentation inline. Wink

Quote:
i posted it to make sure that the result is correct.

You used a simplified example where all rows are returned so we can't be sure it is correct at first sight.
Why didn't you use the test case OP posted?

Quote:
ps. "stupid" is correct usage here? ok, i'll use it for too

1/ It refers to the answer not to you.
2/ When it becomes some constant across the posts, sure, it is a way to give a shock and make think before posting. It is my way, many don't approve it.
So observe the forum before using it. Wink

[Updated on: Wed, 05 July 2017 02:30]

Report message to a moderator

Re: Recursive select [message #664140 is a reply to message #664138] Wed, 05 July 2017 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or, just for fun (hard to do with all 9i restrictions and bugs), a single line:
SQL> with
  2    data as (
  3      select 'LISTE_ELM' tab, cod_liste col1, cod_elm col2
  4      from liste_elm
  5      union all
  6      select 'ELM_LISTE', cod_elm, cod_liste
  7      from elm_liste
  8    ),
  9    result1 as (
 10      select tab, col1, col2,
 11             row_number() over (partition by tab order by null) rn
 12      from data
 13      connect by prior tab != tab and prior col2 = col1
 14      start with tab = 'LISTE_ELM' and col1 = 'LA'
 15    ),
 16    merge1 as (
 17      select tab, sys_connect_by_path('('||col1||','||col2||')','#') res, level lvl
 18      from result1
 19      connect by prior tab = tab and prior rn = rn-1
 20      start with rn = 1
 21    ),
 22    merge2 as (
 23      select tab, substr(res,2) res, lvl, max(lvl) over (partition by tab) max_lvl
 24      from merge1
 25    ),
 26    result2 as (
 27      select tab, res res
 28      from merge2
 29      where lvl = max_lvl and rownum > 0
 30    )
 31  select (select res from result2 where tab = 'LISTE_ELM')||'/'||
 32         (select res from result2 where tab = 'ELM_LISTE') res
 33  from dual
 34  /
RES
---------------------------------------------------------------------------------------
(LA,EA)#(LC,ED)#(LC,EE)#(LC,EC)#(LB,EB)/(EA,LB)#(EB,LC)

1 row selected.
Re: Recursive select [message #664145 is a reply to message #664138] Wed, 05 July 2017 05:14 Go to previous message
banach600
Messages: 14
Registered: June 2017
Junior Member
Many thanks Michel, it works! Really appreciate it.
Previous Topic: Extracting number
Next Topic: Search in concatenated string (merged)
Goto Forum:
  


Current Time: Thu Dec 14 04:59:40 CST 2017

Total time taken to generate the page: 0.01485 seconds