Home » SQL & PL/SQL » SQL & PL/SQL » Recursive select (Oracle 9i)
Recursive select [message #664008] |
Mon, 03 July 2017 04:47 |
|
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 #664029 is a reply to message #664024] |
Mon, 03 July 2017 14:13 |
|
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 |
|
Michel Cadot
Messages: 68693 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 |
|
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 |
|
Michel Cadot
Messages: 68693 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 |
|
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 |
|
Michel Cadot
Messages: 68693 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 #664124 is a reply to message #664123] |
Tue, 04 July 2017 15:36 |
|
Michel Cadot
Messages: 68693 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 |
|
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 #664128 is a reply to message #664127] |
Tue, 04 July 2017 18:03 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
banach600 wrote on Tue, 04 July 2017 14:54If 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 #664132 is a reply to message #664129] |
Wed, 05 July 2017 00:11 |
|
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 |
|
Michel Cadot
Messages: 68693 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 #664140 is a reply to message #664138] |
Wed, 05 July 2017 02:49 |
|
Michel Cadot
Messages: 68693 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.
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:40:34 CDT 2024
|