Questions tagged [hierarchical-query]
146 questions
0
votes
1 answer
SQL Tree structure parent child
I have the following data:
id parent_id
------------------ ------------------
Editor null
Printer Editor
…
somejkuser
- 8,856
- 20
- 64
- 130
0
votes
1 answer
LEFT OUTER JOIN with CONNECT_BY_ROOT
I've got
SELECT
CONNECT_BY_ROOT TAXY.IDCATEGORY AS IDBRANCH
, TAXY.IDCATEGORY
, TAXY.IDPARENTCATEGORY
, TAXY.IDCONTENT
, TAXY.CATEGORYNAME
, LEVEL AS LVL
FROM TAXONOMY TAXY
CONNECT BY TAXY.IDCATEGORY = PRIOR TAXY.IDPARENTCATEGORY
and…
NotGaeL
- 8,344
- 5
- 40
- 70
0
votes
0 answers
Add where and and clause in connect with - ORACLE
I have a table X with columns a, seq_no, dept_no, status where column a contains values like "1,2,3,4,5,6". To change the row to column I have used the query as:
SELECT DISTINCT TO_NUMBER(regexp_substr(a,'[^,]+',1,level)) numbers
FROM x
CONNECT…
user3195273
- 193
- 1
- 5
- 13
0
votes
1 answer
Hierarchical Query with two tables
I have this table named "AFP":
ID || NAME || AFP_FUSION_ID
10 || afp1 ||
20 || afp2 ||
30 || afp3 || 90
40 || afp4 || 30
50 || afp5 || 30
90 || afp6 ||
where "ID" is a primary key and "AFP_FUSION_ID" is a foreign key that…
Felipe
- 1
- 2
0
votes
0 answers
Using 'start with' on a set of values
I'm very rusty on my SQL, and I have to work on a query that looks something like the following:
select * from (
-- whole bunch of code here
where
-- more bits and pieces
connect by prior x = y
start with z = 'VALUE'
I want to run this query on a…
jeeves90
- 75
- 6
0
votes
1 answer
get child items of a recursive table in sql
I have a table with some level (up to 5 level) as shown below. I want to create a procedure that gets some id and returns the items and their children... I have no idea how to handle it!
(when refid is 1, it means that the node is a parent in first…
mjyazdani
- 2,110
- 6
- 33
- 64
0
votes
0 answers
Hierarchical Query showing generation
I need to do a hierarchical query, and as an added output, I need to show the generation of each node in the data tree.
The maximal number of generations is known, but a node may be in different generations for different ancestors.
In the end, I…
JLN
- 3
- 3
0
votes
1 answer
SQL Subquery Using Parent Value In "Start With"
Trying to combine several queries but running into an issue no matter which way I try to hook them together. Here is a mockup of the two:
Query 1 (Get all Gs and related Rs):
select distinct
a.g_id,
a.g_name,
b.r_name,
b.r_id
from…
Cood
- 105
- 2
- 7
0
votes
1 answer
Does progress 4GL language support hierarchical queries?
The simple question subscribed on progress site:
Does progress 4GL language support hierarchical queries like Oracle (Connect by clause) or Sql Server(CTE)?
I have the following table:
Name parent
-----------------------
Elizabeth II …
Adrian Stanculescu
- 1,040
- 2
- 13
- 21
0
votes
2 answers
Oracle CONNECT_BY query.
SELECT LPAD('*', 2*level-1)||SYS_CONNECT_BY_PATH(unit_data, '/') "battle_unit_id"
FROM battle_units
START WITH battle_unit_id= 600
CONNECT BY PRIOR parent_id = battle_unit_id;
returns
/Doctrine
/Doctrine/Air
/Doctrine/Air/Jet powered…
bouncingHippo
- 5,940
- 21
- 67
- 107
0
votes
1 answer
Finding last child in a hierarchy in oracle
I have a table like
prnt_id child_id
-----------------
1 2
2 4
3
5
6 7
7 8
8 9
My result should contain
prnt_id child_id
-----------------
1 4
3
5
6 9
I am using oracle 10g.
I…
user3408663
- 3
- 1
- 3
0
votes
1 answer
A connect by query on all children
Is there any way to have the connect prior... start with statement in Oracle to start with the first "child" in the table and then run the query for all the remaining children as well? I know how to use the statement for 1 child and get its…
RebeccaK375
- 871
- 3
- 17
- 28
0
votes
1 answer
Query with cycle (Oracle 10 XE and 11 XE)
There is roadmap (list of connected cities):
drop table aaa;
create table aaa(a varchar2(10), b varchar2(10));
insert into aaa values ('Rome','Berlin');
insert into aaa values ('London','Rome');
insert into aaa values ('London','New-York');
insert…
potapuff
- 1,839
- 4
- 18
- 36
0
votes
1 answer
How do I do a WHERE NOT IN for Hierarchical data?
I have a table that is a list of paths between points. I want to create a query to return a list with pointID and range(number of point) from a given point. But have spent a day trying to figure this out and haven't go any where, does any one know…
user802599
- 787
- 2
- 12
- 35
0
votes
1 answer
Hierarchical Query - Capturing the parts of the path
I have a hierarchical query in Oracle 11g that gets me all my leaf nodes out of my tree, and it works great. However, I need to try and capture each part of the path separately while I traverse to my leaf node. Is there some way to do that? either…
Jackie
- 83
- 1
- 7