-
Notifications
You must be signed in to change notification settings - Fork 24
/
two_tables.sql
114 lines (92 loc) · 3.62 KB
/
two_tables.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
create table tq84_hier_relation (
id number primary key,
id_parent references tq84_hier_relation
);
create table tq84_hier_description (
id_rel number, -- references tq84_hier_relation,
lang varchar2( 2),
text varchar2(30),
--
primary key(lang, text)
);
-- {
insert into tq84_hier_relation values ( 1, null);
insert into tq84_hier_relation values ( 2, 1);
insert into tq84_hier_relation values ( 3, 1);
insert into tq84_hier_relation values ( 5, 3);
insert into tq84_hier_relation values ( 7, 5);
insert into tq84_hier_relation values ( 6, 3);
insert into tq84_hier_relation values ( 4, 1);
insert into tq84_hier_relation values ( 8, null);
insert into tq84_hier_relation values (10, 8);
insert into tq84_hier_relation values (11, 10);
insert into tq84_hier_relation values (12, 10);
insert into tq84_hier_relation values (13, 8);
insert into tq84_hier_relation values ( 9, null);
--}
-- {
insert into tq84_hier_description values ( 1, 'de', 'Wurzel A');
insert into tq84_hier_description values ( 1, 'en', 'Root A' );
insert into tq84_hier_description values ( 8, 'de', 'Wurzel B');
insert into tq84_hier_description values ( 8, 'en', 'Root B' );
insert into tq84_hier_description values ( 9, 'de', 'Wurzel C');
insert into tq84_hier_description values ( 9, 'en', 'Root C' );
-- insert into tq84_hier_description values ( 2, 'de', 'Stufe 1 A a');
insert into tq84_hier_description values ( 2, 'en', 'Level 1 A a');
insert into tq84_hier_description values ( 3, 'de', 'Stufe 1 A b');
-- insert into tq84_hier_description values ( 3, 'en', 'Level 1 A b');
-- insert into tq84_hier_description values ( 4, 'de', 'Stufe 1 A c');
-- insert into tq84_hier_description values ( 4, 'en', 'Level 1 A c');
insert into tq84_hier_description values (10, 'de', 'Stufe 1 B d');
insert into tq84_hier_description values (10, 'en', 'Level 1 B d');
insert into tq84_hier_description values (13, 'de', 'Stufe 1 B e');
insert into tq84_hier_description values (13, 'en', 'Level 1 B e');
insert into tq84_hier_description values ( 5, 'de', 'Stufe 2 A b f');
insert into tq84_hier_description values ( 5, 'en', 'Level 2 A b f');
-- insert into tq84_hier_description values ( 6, 'de', 'Stufe 2 A c g');
insert into tq84_hier_description values ( 6, 'en', 'Level 2 A c g');
insert into tq84_hier_description values (11, 'de', 'Stufe 2 B d i');
insert into tq84_hier_description values (11, 'en', 'Level 2 B d i');
insert into tq84_hier_description values (12, 'de', 'Stufe 2 B e j');
insert into tq84_hier_description values (12, 'en', 'Level 2 B e j');
insert into tq84_hier_description values ( 7, 'de', 'Stufe 3 B e j k');
insert into tq84_hier_description values ( 7, 'en', 'Level 3 B e j k');
-- }
with descr as (
select
id,
id_parent,
text
from
tq84_hier_relation rel left join
tq84_hier_description des on rel.id = des.id_rel and
des.lang = 'de'
),
hier (id, id_parent, text, lvl) as (
select
id,
id_parent,
text,
0
from
descr
where
id_parent is null
union all
select
descr.id,
descr.id_parent,
descr.text,
hier.lvl+1
from
hier join
descr on hier.id = descr.id_parent
)
search depth first by id set xyz
select
substr(lpad(' ', 2*lvl) || nvl(text, '?'), 1, 50),
xyz
from
hier;
drop table tq84_hier_description purge;
drop table tq84_hier_relation purge;