Home » SQL & PL/SQL » SQL & PL/SQL » A hierarchical query problem (Oracle 11.2.0.3, MS Windows Server 2003)
| A hierarchical query problem [message #632523] |
Mon, 02 February 2015 07:41  |
 |
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Hello everyone!
I'm afraid I need assistance. Here's now the story goes: there are electrical substations (those "houses" that usually have one thick electric cable going in and several thin cables going out). In there, there is usually one transformer. However, in large substations, you can have up to 8 transformers.
I'm trying to create a check-list so that people, who perform maintenance, know what they did.
Here's a test case:with test as (
-- Common values for the whole substation
select null id_parent, 0 id_child, '0' ecode, 'Electricity' title, 1 transformer_no from dual union all
select 0 id_parent, 1 id_child, '1' ecode, 'Substation' , 1 transformer_no from dual union all
select 1 id_parent, 3 id_child, '1.1' ecode, 'Common values' , 1 transformer_no from dual union all
select 3 id_parent, 6 id_child, '1.1.1' ecode, 'Building' , 1 transformer_no from dual union all
select 6 id_parent, 25 id_child, '1.1.1.1' ecode, 'Walls' , 1 transformer_no from dual union all
select 6 id_parent, 26 id_child, '1.1.1.2' ecode, 'Windows' , 1 transformer_no from dual union all
-- Transformers
select 0 id_parent, 2 id_child, '2' ecode, 'Transformers' , 1 transformer_no from dual union all
-- Values regarding the 1st transformer
select 2 id_parent, 5 id_child, '2.1' ecode, 'Transformer #' , 1 transformer_no from dual union all
select 5 id_parent, 24 id_child, '2.1.1' ecode, 'Mechanical' , 1 transformer_no from dual union all
select 24 id_parent, 149 id_child, '2.1.1.1' ecode, 'Temperature' , 1 transformer_no from dual union all
select 24 id_parent, 150 id_child, '2.1.1.2' ecode, 'Oil level' , 1 transformer_no from dual union all
-- Values regarding the 2nd transformer
select 2 id_parent, 5 id_child, '2.1' ecode, 'Transformer #' , 2 transformer_no from dual union all
select 5 id_parent, 24 id_child, '2.1.1' ecode, 'Mechanical' , 2 transformer_no from dual union all
select 24 id_parent, 149 id_child, '2.1.1.1' ecode, 'Temperature' , 2 transformer_no from dual union all
select 24 id_parent, 150 id_child, '2.1.1.2' ecode, 'Oil level' , 2 transformer_no from dual
)
Output I'd like to get is as follows: every substation has certain "common" values (such as building walls or windows). Every transformer has its own set of values (such as transformer temperature, voltage, oil level etc.). So:
0 Electricity
1 Substation
1.1 Common values
1.1.1 Building
1.1.1.1 Walls
1.1.1.2 Windows
2 Transformers
2.1 Transformer 1
2.1.1 Mechanical
2.1.1.1 Temperature
2.1.1.2 Oil level
2.1 Transformer 2
2.1.1 Mechanical
2.1.1.1 Temperature
2.1.1.2 Oil level
<my test case ends here, but you can have up to 8 transformers>
...
2.1 Transformer 8
2.1.1
2.1.1.1
2.1.1.2
Query - which works fine if there's only one transformer - doesn't work for two or more transformers. Obvious mistakes: there are too many rows, many of them are duplicates. I suspect it is because of the CONNECT BY clause as I have multiple children joining multiple parents. I think that I should include TRANSFORMER_NO in there, somewhere, somehow, but I'm just too stupid - whatever I tried didn't work.
Output which returns correct record set can be achieved by SELECT DISTINCT ..., but that's not the way hierarchical query is to be written (I could live with that). Unfortunately, DISTINCT cancels effect of the ORDER BY SIBLINGS (which is a horrible side-effect and I can't use such an output).
Besides, as you can see, ORDER BY SIBLINGS doesn't do its job as I'd expect it to (can you have more than a single value in ORDER BY SIBLINGS?):
with test as (...)
22 select t.transformer_no, t.id_parent, t.id_child, t.ecode,
23 case when level = 3 then replace(t.title, '#', t.transformer_no)
24 else t.title
25 end title, level
26 from test t
27 start with t.id_parent is null
28 connect by prior t.id_child = t.id_parent
29 order siblings by t.transformer_no, to_number(replace(t.ecode, '.', ''));
TRANSFORMER_NO ID_PARENT ID_CHILD ECODE TITLE LEVEL
-------------- ---------- ---------- ------- -------------------- ----------
1 0 0 Electricity 1
1 0 1 1 Substation 2
1 1 3 1.1 Common values 3
1 3 6 1.1.1 Building 4
1 6 25 1.1.1.1 Walls 5
1 6 26 1.1.1.2 Windows 5
1 0 2 2 Transformers 2
1 2 5 2.1 Transformer 1 3
1 5 24 2.1.1 Mechanical 4
1 24 149 2.1.1.1 Temperature 5
1 24 150 2.1.1.2 Oil level 5
2 24 149 2.1.1.1 Temperature 5
2 24 150 2.1.1.2 Oil level 5
2 5 24 2.1.1 Mechanical 4
1 24 149 2.1.1.1 Temperature 5
1 24 150 2.1.1.2 Oil level 5
2 24 149 2.1.1.1 Temperature 5
2 24 150 2.1.1.2 Oil level 5
2 2 5 2.1 Transformer 2 3
1 5 24 2.1.1 Mechanical 4
1 24 149 2.1.1.1 Temperature 5
1 24 150 2.1.1.2 Oil level 5
2 24 149 2.1.1.1 Temperature 5
2 24 150 2.1.1.2 Oil level 5
2 5 24 2.1.1 Mechanical 4
1 24 149 2.1.1.1 Temperature 5
1 24 150 2.1.1.2 Oil level 5
2 24 149 2.1.1.1 Temperature 5
2 24 150 2.1.1.2 Oil level 5
29 rows selected.
SQL>
Using SELECT DISTINCT: this is data set I need, but looks awful:SQL> l22
22* select t.transformer_no, t.id_parent, t.id_child, t.ecode,
SQL> c/select/select distinct/
22* select distinct t.transformer_no, t.id_parent, t.id_child, t.ecode,
SQL> /
TRANSFORMER_NO ID_PARENT ID_CHILD ECODE TITLE LEVEL
-------------- ---------- ---------- ------- -------------------- ----------
1 0 1 1 Substation 2
1 1 3 1.1 Common values 3
1 0 2 2 Transformers 2
2 24 149 2.1.1.1 Temperature 5
1 24 149 2.1.1.1 Temperature 5
2 2 5 2.1 Transformer 2 3
1 6 25 1.1.1.1 Walls 5
2 5 24 2.1.1 Mechanical 4
1 3 6 1.1.1 Building 4
1 6 26 1.1.1.2 Windows 5
1 2 5 2.1 Transformer 1 3
2 24 150 2.1.1.2 Oil level 5
1 0 0 Electricity 1
1 24 150 2.1.1.2 Oil level 5
1 5 24 2.1.1 Mechanical 4
15 rows selected.
SQL>
Help (before I drown)!
|
|
|
|
|
|
| Re: A hierarchical query problem [message #632529 is a reply to message #632523] |
Mon, 02 February 2015 08:48  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
with t as (
select ecode,
title,
rownum rn
from test
start with id_parent is null
connect by id_parent = prior id_child
)
select ecode,
case title
when 'Transformer #'
then 'Transformer ' || count(*) over(partition by title order by rn)
else title
end title
from t
order by rn
/
ECODE TITLE
------- --------------------
0 Electricity
1 Substation
1.1 Common values
1.1.1 Building
1.1.1.1 Walls
1.1.1.2 Windows
2 Transformers
2.1 Transformer 1
2.1.1 Mechanical
2.1.1.1 Temperature
2.1.1.1 Temperature
ECODE TITLE
------- --------------------
2.1.1.2 Oil level
2.1.1.2 Oil level
2.1.1 Mechanical
2.1.1.1 Temperature
2.1.1.1 Temperature
2.1.1.2 Oil level
2.1.1.2 Oil level
2.1 Transformer 2
2.1.1 Mechanical
2.1.1.1 Temperature
2.1.1.1 Temperature
ECODE TITLE
------- --------------------
2.1.1.2 Oil level
2.1.1.2 Oil level
2.1.1 Mechanical
2.1.1.1 Temperature
2.1.1.1 Temperature
2.1.1.2 Oil level
2.1.1.2 Oil level
29 rows selected.
SQL>
SY.
|
|
|
|
| Re: A hierarchical query problem [message #632532 is a reply to message #632529] |
Mon, 02 February 2015 11:00 |
|
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Thank you for replies!
@Michel: it means nothing in that context. Its value could have been 0, -1, NULL or whatever else. I just decided to put 1 in there. If you think that it matters, I'll change it.
There's a table that contains codes for all values that should be checked. Depending on the substation type, I select subset of the previously mentioned table and insert it into another table (which is represented by my WITH TEST factoring clause). Therefore, I can modify the INSERT statement if necessary.
Until now, I have dealt with substations that contain only one transformer. Now, the requirement has changed and I have to support up to 8 transformers. The "TEST" table is base for a form, several reports and a mobile (Android) application so I can't modify it too much.
That's why I altered the table and added the TRANSFORMER_NO column and put 1 into it by default. A PL/SQL procedure inserts additional rows for additional transformers. So far, so good.
But, at "representation" level (Application Express form and reports), I have difficulties to properly display new hierarchy.
As of a primary key column: the TEST table contains results of inspections of many substations, so there's [INSPECTIONS_ID + TRANSFORMER_NO + ID_CHILD] combination that now uniquely identifies every record.
@Solomon: right! That sorts them correctly, but there are still duplicates. The result set (based on test case) should contain only 15 rows, not 29. Due to ROWNUM, DISTINCT doesn't do any good (if applied to your query).
[Updated on: Mon, 02 February 2015 11:02] Report message to a moderator |
|
|
|
| Re: A hierarchical query problem [message #632533 is a reply to message #632532] |
Mon, 02 February 2015 11:51 |
 |
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
If you already have the hierarchy in ECODE and putting NULL where transformer_no is meaningless:
SQL> with test as (
2 -- Common values for the whole substation
3 select null id_parent, 0 id_child, '0' ecode, 'Electricity' title, to_number(null) transformer_no from dual union all
4 select 0 id_parent, 1 id_child, '1' ecode, 'Substation' , to_number(null) transformer_no from dual union all
5 select 1 id_parent, 3 id_child, '1.1' ecode, 'Common values' , to_number(null) transformer_no from dual union all
6 select 3 id_parent, 6 id_child, '1.1.1' ecode, 'Building' , to_number(null) transformer_no from dual union all
7 select 6 id_parent, 25 id_child, '1.1.1.1' ecode, 'Walls' , to_number(null) transformer_no from dual union all
8 select 6 id_parent, 26 id_child, '1.1.1.2' ecode, 'Windows' , to_number(null) transformer_no from dual union all
9 -- Transformers
10 select 0 id_parent, 2 id_child, '2' ecode, 'Transformers' , to_number(null) transformer_no from dual union all
11 -- Values regarding the 1st transformer
12 select 2 id_parent, 5 id_child, '2.1' ecode, 'Transformer #' , 1 transformer_no from dual union all
13 select 5 id_parent, 24 id_child, '2.1.1' ecode, 'Mechanical' , 1 transformer_no from dual union all
14 select 24 id_parent, 149 id_child, '2.1.1.1' ecode, 'Temperature' , 1 transformer_no from dual union all
15 select 24 id_parent, 150 id_child, '2.1.1.2' ecode, 'Oil level' , 1 transformer_no from dual union all
16 -- Values regarding the 2nd transformer
17 select 2 id_parent, 5 id_child, '2.1' ecode, 'Transformer #' , 2 transformer_no from dual union all
18 select 5 id_parent, 24 id_child, '2.1.1' ecode, 'Mechanical' , 2 transformer_no from dual union all
19 select 24 id_parent, 149 id_child, '2.1.1.1' ecode, 'Temperature' , 2 transformer_no from dual union all
20 select 24 id_parent, 150 id_child, '2.1.1.2' ecode, 'Oil level' , 2 transformer_no from dual
21 )
22 select rpad(decode(id_child, 0,'', lpad(' ',2*(regexp_count(ecode,'\.')+1)))||ecode, 17) ||
23 decode(regexp_count(ecode,'\.'), 1, replace(title, '#', transformer_no), title)
24 line
25 from test
26 order by transformer_no nulls first, ecode
27 /
LINE
----------------------------------------
0 Electricity
1 Substation
1.1 Common values
1.1.1 Building
1.1.1.1 Walls
1.1.1.2 Windows
2 Transformers
2.1 Transformer 1
2.1.1 Mechanical
2.1.1.1 Temperature
2.1.1.2 Oil level
2.1 Transformer 2
2.1.1 Mechanical
2.1.1.1 Temperature
2.1.1.2 Oil level
15 rows selected.
|
|
|
|
| Re: A hierarchical query problem [message #632534 is a reply to message #632532] |
Mon, 02 February 2015 12:35 |
|
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
The problem with your query is that the couple (child_id,parent_id) which defines a unique row when there was only one transformer, no more does it with several, you have to add the transformer_no to the CONNECT BY condition:
SQL> with test as (
2 -- Common values for the whole substation
3 select null id_parent, 0 id_child, '0' ecode, 'Electricity' title, to_number(null) transformer_no from dual union all
4 select 0 id_parent, 1 id_child, '1' ecode, 'Substation' , to_number(null) transformer_no from dual union all
5 select 1 id_parent, 3 id_child, '1.1' ecode, 'Common values' , to_number(null) transformer_no from dual union all
6 select 3 id_parent, 6 id_child, '1.1.1' ecode, 'Building' , to_number(null) transformer_no from dual union all
7 select 6 id_parent, 25 id_child, '1.1.1.1' ecode, 'Walls' , to_number(null) transformer_no from dual union all
8 select 6 id_parent, 26 id_child, '1.1.1.2' ecode, 'Windows' , to_number(null) transformer_no from dual union all
9 -- Transformers
10 select 0 id_parent, 2 id_child, '2' ecode, 'Transformers' , to_number(null) transformer_no from dual union all
11 -- Values regarding the 1st transformer
12 select 2 id_parent, 5 id_child, '2.1' ecode, 'Transformer #' , 1 transformer_no from dual union all
13 select 5 id_parent, 24 id_child, '2.1.1' ecode, 'Mechanical' , 1 transformer_no from dual union all
14 select 24 id_parent, 149 id_child, '2.1.1.1' ecode, 'Temperature' , 1 transformer_no from dual union all
15 select 24 id_parent, 150 id_child, '2.1.1.2' ecode, 'Oil level' , 1 transformer_no from dual union all
16 -- Values regarding the 2nd transformer
17 select 2 id_parent, 5 id_child, '2.1' ecode, 'Transformer #' , 2 transformer_no from dual union all
18 select 5 id_parent, 24 id_child, '2.1.1' ecode, 'Mechanical' , 2 transformer_no from dual union all
19 select 24 id_parent, 149 id_child, '2.1.1.1' ecode, 'Temperature' , 2 transformer_no from dual union all
20 select 24 id_parent, 150 id_child, '2.1.1.2' ecode, 'Oil level' , 2 transformer_no from dual
21 )
22 select t.transformer_no, t.id_parent, t.id_child, t.ecode,
23 case when level = 3 then replace(t.title, '#', t.transformer_no)
24 else t.title
25 end title, level
26 from test t
27 start with t.id_parent is null
28 connect by prior t.id_child = t.id_parent
29 AND ( transformer_no IS NULL OR NVL(PRIOR transformer_no, transformer_no) = transformer_no )
30 order siblings by t.transformer_no NULLS FIRST, to_number(replace(t.ecode, '.', ''))
31 /
TRANSFORMER_NO ID_PARENT ID_CHILD ECODE TITLE LEVEL
-------------- ---------- ---------- --------------- --------------- ----------
0 0 Electricity 1
0 1 1 Substation 2
1 3 1.1 Common values 3
3 6 1.1.1 Building 4
6 25 1.1.1.1 Walls 5
6 26 1.1.1.2 Windows 5
0 2 2 Transformers 2
1 2 5 2.1 Transformer 1 3
1 5 24 2.1.1 Mechanical 4
1 24 149 2.1.1.1 Temperature 5
1 24 150 2.1.1.2 Oil level 5
2 2 5 2.1 Transformer 2 3
2 5 24 2.1.1 Mechanical 4
2 24 149 2.1.1.1 Temperature 5
2 24 150 2.1.1.2 Oil level 5
15 rows selected.
|
|
|
|
|
|
| Re: A hierarchical query problem [message #632536 is a reply to message #632535] |
Mon, 02 February 2015 13:38 |
|
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
If you indeed have the hierarchy, it'd be far faster (as the number of lines grows) to use the first one than to rebuild the hierarchy on the fly using CONNECT BY.
In the last query, the new condition would be clearer with:
AND ( transformer_no IS NULL OR PRIOR transformer_no IS NULL OR PRIOR transformer_no = transformer_no )
In this query, a value of 0 instead of NULL might be better to take profit of an index on it, although it should be an index on (id_child,transformer_no) or (id_parent,transformer_no) I never remember in which way the test is done in a hierarchical query.
[Updated on: Mon, 02 February 2015 13:42] Report message to a moderator |
|
|
|
|
|
| Re: A hierarchical query problem [message #632538 is a reply to message #632537] |
Mon, 02 February 2015 14:55 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
And if you want to use hierarchical solution (which obviously will be slower that plain select Michel suggested):
with t1 as (
select ecode,
title,
transformer_no,
rownum rn
from test
start with id_parent is null
connect by id_parent = prior id_child
),
t2 as (
select ecode,
title,
greatest(count(case title when 'Transformer #' then 1 end) over(order by rn),1) tn,
transformer_no,
rn
from t1
),
t3 as (
select ecode,
case title
when 'Transformer #' then 'Transformer ' || tn
else title
end title,
tn,
rn
from t2
where transformer_no = tn
)
select ecode,
title
from t3
group by ecode,
title,
tn
order by max(rn)
/
ECODE TITLE
------- --------------------
0 Electricity
1 Substation
1.1 Common values
1.1.1 Building
1.1.1.1 Walls
1.1.1.2 Windows
2 Transformers
2.1 Transformer 1
2.1.1 Mechanical
2.1.1.1 Temperature
2.1.1.2 Oil level
ECODE TITLE
------- --------------------
2.1 Transformer 2
2.1.1 Mechanical
2.1.1.1 Temperature
2.1.1.2 Oil level
15 rows selected.
SQL>
SY.
|
|
|
|
|
|
| Re: A hierarchical query problem [message #632554 is a reply to message #632539] |
Tue, 03 February 2015 01:57 |
|
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
This part of Michel's code:
AND ( transformer_no IS NULL OR NVL(PRIOR transformer_no, transformer_no) = transformer_no )
ORDER SIBLINGS BY t.transformer_no NULLS FIRST, to_number(replace(t.ecode, '.', ''))
did the job perfectly regarding number of changes I had to do in existing code (a stored procedure, two Apex pages, a report).
Currently, I don't have enough time to investigate other options you guys provided, but nevertheless, thank you very much for all the help.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 20:36:53 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
