Questions tagged [hierarchical-query]
146 questions
1
vote
2 answers
Total salary from a hierarchical sql query in Oracle
I am trying to find the total salary by using oracle hierarchical SQL query but I do not get the desired output.
I use Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit.
Below are the sample input tables and the hierarchical…
anishjp
- 115
- 3
- 13
1
vote
4 answers
Sort Hierarchical table in SQL Server?
I have a table Address and I want to sort rows like parent-1 => all-child-parent-1, parent-2 => all-child-parent-2 so on ....
Address Table
ID Caption Parent
---------------------
1 A NULL
2 B NULL
3 a 1
4 …
Zanyar Jalal
- 1,407
- 12
- 29
1
vote
1 answer
How to calculate number of hops between sourcee and destination?
I have two columns in a table source and destination as below.
source delivery
s1 d1
d1 d2
d2 f1
s2 d3
d3 d4
d4 d5
d5 f2
source - s1 destination f1
source - s2 destination f2
How do I get the…
Metadata
- 2,127
- 9
- 56
- 127
1
vote
1 answer
Why does my Oracle hierarchical query stop after one level only
My table has a breadcrumb not unlike a filesystem to show page navigation. For example / / . I want to start with any the level and navigate to the child / breadcrumb. I don't care whether it…
Woodsman
- 901
- 21
- 61
1
vote
1 answer
Is it possible to write a hierarchical query in Oracle SQL using only joins instead of using connect by clause
Given:
object_table
------------
ID
Parent
Sample Data:
ID : Parent
1 : null
22 : 1
45 : 22
32 : 45
Query using connect by clause:
select * from object_table
start with id = 45
connect by prior parent = id;
Result:
ID : Parent
45 : 22
22…
Shashank112
- 11
- 1
1
vote
2 answers
Confusion in flow of sql output in oracle
I have table T as:
So,I got sql from somewhere as:
select * from (
SELECT start_range,(LEVEL + START_RANGE)-1 NUM,end_range
FROM offc.T
CONNECT BY (LEVEL +START_RANGE ) <= END_RANGE+1) order by start_range,num,end_range;
I got output as:
I…
Random guy
- 883
- 3
- 11
- 32
1
vote
1 answer
Oracle BOM query returns disabled assemblies if any of its components are not
I have this more or less common query to get an exploded BOM with the path to the child:
select distinct
LEVEL "level"
,sys_connect_by_path(msib.segment1, '|') as "PATH"
,msib2.segment1 as "CHILD ITEM"
,msib2.description
…
Gabriel
- 5,453
- 14
- 63
- 92
1
vote
2 answers
Oracle SQL - Derive multiple rows from column combination of single row
I have this kind of data on a Oracle SQL DB:
personId lastEvent currentEvent nextEvent
1 null 1 2
1 1 2 3
1 2 3 4
1 3 4 null
For a single row…
VahnIronSky
- 25
- 5
1
vote
2 answers
May I retrieve a hierarchical data structure from a SQL Server stored procedure?
I have a Web Service one of whose methods returns a list of items, each of which possesses another list of items:
isekaijin
- 19,076
- 18
- 85
- 153
1
vote
0 answers
SQL Server: Hierarchical data
I have multiple tables with table names as TableA (Primary Key column: ID1), TableB (Primary Key column: ID2), TableC (Primary Key column: ID3).
CREATE TABLE TableA(
ID1 INTEGER NOT NULL PRIMARY KEY
,TableA_Name VARCHAR(14) NOT NULL
…
sharu
- 21
- 1
1
vote
1 answer
Oracle sql hierarchical or recursive query on table data with graph nodes
Here is my table "graphtable" having graph nodes. Each tuple represents an undirected edge.
╔═════════╦═════════╗
║ NODEONE ║ NODETWO ║
╠═════════╬═════════╣
║ A ║ A ║
║ A ║ A ║
║ A ║ B ║
║ A ║ B ║
║ A…
user9562401
- 13
- 4
1
vote
5 answers
How to select highest level from 2 different trees in same command
I have a dataset that has the following appearence.
A 1
A 2
A 3
B 1
B 2
B 3
B 4
Which is a result from using the following command
select
connect_by_root id as root,
level lvl
from
dbset
start with id in ('A','B')
connect by nocycle…
Rasmus Ekman
- 13
- 3
1
vote
1 answer
Oracle SQL hierarchical query with two tables
I have two tables with the following structure
Table 1:
PRODID | PSTID |
___________________
1 | 4
2 |
3 | 2
4 |
5 |
Table 2:
PSTID | PRODID
_______________
1 | 4
2 | 1
3 | 1
…
wasp256
- 5,943
- 12
- 72
- 119
1
vote
3 answers
How do I query for all the nodes between two nodes in a tree?
I have a hierarchical database strucutre, e.g. columns ID and PARENT_ID defined for each row, with the top level rows having a NULL PARENT_ID.
I have all the relationships from this table flattened into another table, e.g. if there were three…
aw crud
- 8,791
- 19
- 71
- 115
1
vote
1 answer
How do I get both the child and one upper level parent information by using oracle connect by prior?
I want to get both the child and one upper level parent information by using oracle connect by prior?
For example the folowing query retrieve child info and parent id,
SELECT last_name, employee_id, manager_id, LEVEL
FROM employees
START WITH…
user6493966
- 73
- 2
- 8