| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> hierarchical queries in place of "self-column-join" queries.
Hi,
I'm working for a major italian telecommunication company.
We are using Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit
Production
We do maintenance and configuration tasks on the software systems
used to schedule technician and personnel activities on the italian
territory.
On one of the database instances used by the applications, we have a
table
describing the geographic areas hierarchy, data that are relevant for
the corresponding
organizational units.
The table (compositeareas) structure is as follows:
Name Type
COMPOSITEAREANAME VARCHAR2(20)
COMPOSITEAREATYPE CHAR(1)
AREANAME VARCHAR2(20) AREATYPE CHAR(1) SUPERAREA VARCHAR2(20) Nullable, not relevant
The hierarchy has 4 levels, and at the time, the database analysts
decided, presumably because various
different pieces of software (also legacy) use these data, to limit the
number of table columns,
so using only one column (COMPOSITEAREANAME) to store 3 out of 4 levels
of hierarchy, and another
column (AREANAME) to store the areas at the lowest level (at this time,
they have added one more
column 'SUPERAREA', for future uses, therefore still not being relevant
for these purposes).
To explain this better, let's consider an example table with the
following data.
COMPOSITEAREANAME COMPOSITEAREATYPE AREANAME AREATYPE
level1-area1 A lowestlevel-area1 T level1-area2 A lowestlevel-area2 T
..
level1-area10 A lowestlevel-area10 T level2-area1 A lowestlevel-area1 T
..
level2-area10 A lowestlevel-area10 T level3-area1 A lowestlevel-area1 T
..
level3-area10 A lowestlevel-area10 T level4-area1 A lowestlevel-area1 T
..
level4-area10 A lowestlevel-area10 T morelevel1-area1 A lowestlevel-area11 T
..
morelevel1-area10 A lowestlevel-area20 T
..
morelevel2-area1 A lowestlevel-area11 T
..
morelevel2-area10 A lowestlevel-area20 T
..
morelevel3-area1 A lowestlevel-area11 T
..
morelevel3-area10 A lowestlevel-area20 T
..
morelevel4-area1 A lowestlevel-area11 T
..
morelevel4-area10 A lowestlevel-area20 T
In order to extract the hierarchy structure output, we are using
queries like this:
select
a.compositeareaname,b.compositeareaname,c.compositeareaname,c.areaname
from compositeareas a, compositeareas b, compositeareas c
where a.areaname = b.areaname
and b.areaname = c.areaname and a.compositeareaname like 'level1-%' --OR 'morelevel1-%' and so on and b.compositeareaname like 'level2-%' --OR 'morelevel2-%' and so on and c.compositeareaname like 'level3-%' --OR 'morelevel3-%' and so onorder by 1,2,3,4
This yields the results:
COMPOSITEAREANAME COMPOSITEAREANAME COMPOSITEAREANAME AREANAME
level1-area1 level2-area1 level3-area1 lowestlevel-area1 level1-area10 level2-area10 level3-area10 lowestlevel-area10 level1-area2 level2-area2 level3-area2 lowestlevel-area2 level1-area3 level2-area3 level3-area3 lowestlevel-area3 level1-area4 level2-area4 level3-area4 lowestlevel-area4 level1-area5 level2-area5 level3-area5 lowestlevel-area5 level1-area6 level2-area6 level3-area6 lowestlevel-area6 level1-area7 level2-area7 level3-area7 lowestlevel-area7 level1-area8 level2-area8 level3-area8 lowestlevel-area8 level1-area9 level2-area9 level3-area9 lowestlevel-area9
What I'm trying to do is, while maintaining some stored procedures, to substitute this self-join query with a 'select...starting with...connect by...'
QUESTION 1 (A MYSTERIOUS BEHAVIOUR):
given the following query:
select LEVEL,compositeareaname,compositeareatype,areaname,areatype
from compositeareas
start with compositeareaname like 'level1-%'
or compositeareaname like 'level2-%'
or compositeareaname like 'level3-%'
connect by prior compositeareaname=areaname --or
areaname=compositeareaname
Probably due to my ignorance, I expected this query to return *no*
rows,
since there is no areaname value matching any of the compositeareaname
values and vice-versa.
Instead it outputs the following:
LEVEL COMPOSITEAREANAME COMPOSITEAREATYPE AREANAME AREATYPE
1 level1-area1 A lowestlevel-area1 T 1 level1-area10 A lowestlevel-area10 T 1 level1-area2 A lowestlevel-area2 T 1 level1-area3 A lowestlevel-area3 T 1 level1-area4 A lowestlevel-area4 T 1 level1-area5 A lowestlevel-area5 T 1 level1-area6 A lowestlevel-area6 T 1 level1-area7 A lowestlevel-area7 T 1 level1-area8 A lowestlevel-area8 T 1 level1-area9 A lowestlevel-area9 T 1 level2-area1 A lowestlevel-area1 T 1 level2-area10 A lowestlevel-area10 T 1 level2-area2 A lowestlevel-area2 T 1 level2-area3 A lowestlevel-area3 T 1 level2-area4 A lowestlevel-area4 T 1 level2-area5 A lowestlevel-area5 T 1 level2-area6 A lowestlevel-area6 T 1 level2-area7 A lowestlevel-area7 T 1 level2-area8 A lowestlevel-area8 T 1 level2-area9 A lowestlevel-area9 T 1 level3-area1 A lowestlevel-area1 T 1 level3-area10 A lowestlevel-area10 T 1 level3-area2 A lowestlevel-area2 T 1 level3-area3 A lowestlevel-area3 T 1 level3-area4 A lowestlevel-area4 T 1 level3-area5 A lowestlevel-area5 T 1 level3-area6 A lowestlevel-area6 T 1 level3-area7 A lowestlevel-area7 T 1 level3-area8 A lowestlevel-area8 T 1 level3-area9 A lowestlevel-area9 T
Could you explain why these rows are returned ?
QUESTION 2)
Given the following query (but this time I expected some trouble!):
select LEVEL,compositeareaname,compositeareatype,areaname,areatype
from compositeareas
start with compositeareaname like 'level1-%'
or compositeareaname like 'level2-%'
or compositeareaname like 'level3-%'
connect by prior compositeareaname=compositeareaname
I get the following error message:
ORA-01436: CONNECT BY loop in user data
Is there any way to properly use a hierarchical query in this context ?
Thank you in advance for help, and excuse me for my not-so-perfect English.
Bruno
Telecom Italia SpA
Received on Wed Jul 13 2005 - 10:34:27 CDT
 |
 |