Questions tagged [hierarchical-query]
146 questions
0
votes
0 answers
PLSQL- How to get the values of separate flow in a hierarchical flow of records
There is a set of records in a table which are in hierarchical order. I have to get the flows separately
As per the image, the separate flows will be like,
A -> B-> D
A -> B-> E
A->C-> F
A->C-> G
but the number of levels are dynamic and no. of…
user2431205
- 81
- 1
- 1
- 7
0
votes
2 answers
How to get the root record in Oracle database
Starting from the generic node (or leaf) how to get the root node?
SELECT
ID,MSG_ID,PARENT_ID
FROM
TABLE_X
CONNECT BY PRIOR PARENT_ID = ID;
ID MSG_ID PARENT_ID
4 3 NULL
5 …
0
votes
2 answers
How can i get a child record's parent, grandparent, and so on and itself along with the distance from root in SQL
I have a table as shown below -
ID | NAME | PARENT
1 | Global | null
2 | USA | 1
3 | Canada | 1
4 | USA-1 | 2
5 | USA-11 | 4
The column parent refers to the column id i.e., Global…
rameez md
- 15
- 1
- 5
0
votes
2 answers
Infinite loop with recursive SQL query
I can't seem to find the reason behind the infinite loop in this query, nor how to correct it.
Here is the context :
I have a table called mergesWith with this description :
mergesWith: information about neighboring seas. Note that in this…
CSstudZ
- 101
- 10
0
votes
0 answers
How do I connect a hierachical query by a substring of the prior record
So essentially I have hierarchical data saved to a table with the standard parent/ child relationship.
I want to remove part of the child's name (for example the first letter) and use that to connect to the next level. Example: Each Parent has a…
ZakZ
- 1
- 1
0
votes
1 answer
Sorting Table in hierarchical order
Is it possible to sorting queries table in hierarchical order like this:
Expected
+----+--------+-----------+-------+--------+-----------+-----------+---------+
| ID | Code | Name | Qty | Amount | is_parent | parent_id | remarks…
Joe Wilson
- 25
- 1
- 11
0
votes
1 answer
Another Combination
Very similar to my last question, now I want only the, "full combination," for a group in order of priority. So, from this source table:
+-------+-------+----------+
| GROUP | State | Priority |
+-------+-------+----------+
| 1 | MI | 1 …
anonimitie
- 39
- 9
0
votes
1 answer
Hierarchical Query connect by using start with clause
The data I am working with is sequenced like the below example:
Example
What I want to know is this:
Want
What I get from my query is this (the actual query with actual data is posted at the end of this post) :
Get
This is the code I have now, which…
thors123
- 15
- 1
0
votes
1 answer
How to get list of all manager using hierarchical query
I would like to pull list of employees who are managers of me.
Have a look at below query:
SELECT SYS_CONNECT_BY_PATH (username, ':') AS "Liste Membres",LEVEL
FROM employees
WHERE CONNECT_BY_ISLEAF = 1
AND username = '150') --…
dpccrt87
- 53
- 1
- 8
0
votes
1 answer
Identify root node with group by
I need to identify the primary channel (which is highest in hierarchy) with only one set of flag set under it's hierarchy. In below case, US is not a valid primary channel since there are two flags underneath. Only valid primary channels are US1,…
Vishal
- 198
- 1
- 3
- 11
0
votes
2 answers
Getting the days between two dates for multiple IDs
This is a follow up question from How to get a list of months between 2 given dates using a query?
really. (I suspect it's because I don't quite understand the logic behind connect by level clauses !)
What I have is a list of data like so
ID |…
C. Coles
- 3
- 1
0
votes
0 answers
Moving Recursive queries from Oracle Sql to Redshift
I am trying to move this recursive query from Oracle Sql to Redshift. In order to so, I should be able to get rid of the commands "CONNECT BY", "LEVEL" and " PRIOR" within the code written below. I was wondering to move the recursive pattern within…
BubbleBeat
- 13
- 2
0
votes
1 answer
Hierarchical multiple update query on one table - oracle
I have a table person with these columns, dependent on each other.
person_id, related_person_id, beginner_related_person_id
Example of my data
person_id related_person_id beginner_related_person_id
1 null null
2 1 …
Pino 3
- 1
- 1
0
votes
2 answers
Output records that match the condition, as well as their parent records up to the root record. Oracle SQL
There is such an Oracle table:
+----+-----+--------+
| ID | PID | NAME |
+----+-----+--------+
| 1 | | testX |
| 2 | | test2 |
| 3 | 2 | test3 |
| 4 | 3 | testX |
| 5 | 3 | test5 |
| 6 | 3 | test6 |
| 7 | 4 | test7 …
archjkee
- 41
- 4
0
votes
1 answer
Oracle hierarchical query with hierarchy applied to subsets
I am creating customized tool for DB lock monitoring for Oracle SE.
Basically i have scheduler job which takes snapshot of gv$session info every 10 seconds and stores it in custom made ashstat_data table.
And now i am trying to create a view to list…
Jānis Krišāns
- 3
- 4