Questions tagged [hierarchical-query]
146 questions
0
votes
1 answer
How to insert a new row based on previous row
Given table:
Name
Date
Last Date
Frequency
Person1
1-Jan-2022
2-May-2022
30 Days
Expected output:
Name
Updated Date
Some Other Column
Person1
1-Jan-2022
Value 1
Person1
31-Jan-2022
Value 2
Person1
2-Mar-2022
Value…
Ghatothkachh
- 21
- 6
0
votes
2 answers
Oracle Oracle hierarchical query to disable foreign keys
I have some code (see below), which uses a hierarchical query that traverses down a table to find all the related Foriegn keys. This seems to be working fine.
Can this be modified not to display the top level table, in this case the…
Beefstu
- 804
- 6
- 11
0
votes
0 answers
Oracle Start With In Limit
In a complex query, if I enter a small number of records in the startwith, the result returns quickly, but when I manually fill in around 180 records in "in", it seems as if it is locked and the result is not returned for hours.
SELECT * FROM…
Burak Kartal
- 21
- 2
0
votes
2 answers
Hierarchy formatting
I am trying to create a query that shows the different levels of the hierarchy in different columns
For now it looks like this
Parent
Child
1
2
1
3
2
4
2
5
3
5
I want the output to look like…
Enxhi
- 1
0
votes
1 answer
Oracle SQL hierarical query connected by sequential field
I have a scheme of hierarchical tasks such as this:
|-- task_1
|---- task_1_1
|------ task_1_1_1
|------ task_1_1_2
|---- task_1_2
|------ task_1_2_1
|------ task_1_2_2
|-- task_2
etc.
The number of tasks at each level can vary as can vary the…
a.d
- 3
- 2
0
votes
4 answers
Oracle SQL hierarchical query from bottom to top
I have a table where I want to go from bottom to top using hierarchical queries.
The problem is that I need the get the value of one column from root (top) using CONNECT_BY_ROOT, but since I reverse the way the hierarchical query works (reverse the…
akadri01
- 25
- 7
0
votes
0 answers
Create materialized view with hierarchical query and ANSI join
I am unable to create a materialized view and getting a ORA-904 error. This looks like the Bug 17551261 , but this should already be fixed. I tested it on Oracle 12.2, 19 and 21.
In the view i use a connect by statement and ANSI join.
When i rewrite…
christris
- 31
- 3
0
votes
1 answer
Oracle table hierarchy level and grouping
I have a oracle database where foreign key is enabled and I have tables A,B,C,D,E,F,AB,ABC,EF
Attaching the picture for hierarchy
'B' has parent 'D',
'AB' has parent 'A' as well as 'B',
'ABC' has parent 'AB' and 'C',
'EF' has parent 'E' & 'F',
Now…
user3016635
- 39
- 3
0
votes
2 answers
Join with relation to parent and grandparent
Given the relation:
Teacher -> Students -> Advisors
There can also be
Teacher -> Advisors
How can I write a join statement that shows me null values for the students where advisors have their FK pointing towards teachers and not students? Without…
Pontus Ekengren
- 67
- 1
- 8
0
votes
1 answer
hierarchical query without side rows
I have "id-parent_id related" data, like this:
1
/ \
/ \
2 4
/
/
3
I have the code, that returns data for all rows related to particular (related to condition in the start with clause) tree - in both sides ("up" and…
archjkeee
- 13
- 4
0
votes
0 answers
oracle connect by prior,how to indicate the highest score from the path- using sys_connect_by_path
I try to use sys_connect_by_path in connect by prior, to create a path for all the model, I want to indicate for any path the highest score that the client gets in the models.
this is my table
the query that I use to create the paths
select id,…
Gal Bialostozki
- 1
- 1
0
votes
2 answers
SQL Oracle - Multiprocessor Scheduling: Greedy Number Partitioning
Is there an SQL statement to perform greedy number partitioning? (Oracle 19c)
I want to divide jobs among N processors.
Example,
Given the following workload data set:
job
---
4
60
50
1
100
6
Expected result set (assuming just N=2 where ties go to…
Alex Bartsmon
- 471
- 4
- 9
0
votes
2 answers
Hierarchical Query Level
The below query is returning data as expected but we have a requirement that where there is level 1, it should be the last level and last level should be represented as 1st.
I am running this in Oracle database.
Data for TABLE_1:-
FK_1 |FK_2 |…
Anjali Sinha
- 11
- 3
0
votes
1 answer
How to write this recursive CTE for SQL Server?
For simplicity my schema:
Folders table (FolderId, ParentFolderId)
DeviceFolderProperties table (FolderId, LogDataRetentionDaysEvent)
Not every folder has a retention day. However this is an inherited value. How can you write something in SQL to…
flux
- 1,518
- 1
- 17
- 31
0
votes
1 answer
Employee Manager Hierarchy Query in Oracle
I have a table with the structure as below:
+-------+-------+
| EMPID | MGRID |
+-------+-------+
| A | B |
| B | C |
| C | D |
| I | J |
| J | D |
| K | L |
| L | O |
| O | D …
Karthik Viz
- 115
- 13