Stack Overflow
Stack Overflow

Questions tagged [hierarchical-query]

146 questions
1
vote
2 answers

Hierarchical Query to Self-Join Table Max 3 Times

I have an Oracle DB with a table with the following columns: ID | PARENTID | DETAIL1 ------------------------ 1 | NULL | BLAH1 2 | 1 | BLAH2 3 | 2 | BLAH3 4 | 2 | BLAH4 5 | NULL | BLAH5 …
kfkhalili
  • 996
  • 1
  • 11
  • 24
1
vote
0 answers

Finding all relationships within an SQL table - Mysql

I have a table called entity relation. Which maps a grantee to its role. In a way a user can have multiple roles, and that a role can also have roles, For a small example: +-----------+-----------+--+ | Grantee | Role | …
StationaryTraveller
  • 1,449
  • 2
  • 19
  • 31
1
vote
1 answer

Oracle hierarchical query execution steps

Here's the documentation: http://download-east.oracle.com/docs/cd/B12037_01/server.101/b10759/queries003.htm Here's the execution order it describes: A join, if present, is evaluated first, whether the join is specified in the FROM clause or with…
aw crud
  • 8,791
  • 19
  • 71
  • 115
1
vote
2 answers

how to navigate in self loop tables?

Consider the following table create table EMPLOYEE ( empno NUMBER not null, ename VARCHAR2(100), salary NUMBER, hiredate DATE, manager NUMBER ); alter table EMPLOYEE add constraint PK_EMP primary key (EMPNO); alter table…
kolah ghermezi
  • 91
  • 2
  • 10
1
vote
1 answer

Oracle Sibling Structure

I have a structure that I store equal records in a database table. You can think that these records are siblings. For example I have two records in this table; 1=2 and 1=3. And I need a query that will return all siblings of a given record. Let me…
Mustafa Şimşek
  • 13
  • 2
1
vote
0 answers

Hierarchical Query: Get the direct children count for each node

I am new to hierarchical queries and I have gone through most of the posts on it. I have a query that i would like to improve. My Scenario is something like this: Sample ------- ID, NAME, PARENT_ID Table Data as follows: ID NAME …
Monikka
  • 518
  • 6
  • 12
1
vote
1 answer

PLSQL - Connect By Prior (many to many)

I have a query that uses a connect by statement to order the recursive data. The problem I have is that there is occasionally a one to many or a many to one relationship and I dont know how to deal with it. SELECT * FROM (SELECT * FROM…
user2542663
  • 11
  • 3
0
votes
0 answers

Nested tables comparison in a hierarchical query

Oracle nested tables are allowed to be compared for equality, and two nested tables consider equal if they differ only in order of their elements. This post is about my attempt to use nested tables comparison in a hierarchical query. First, let's…
ESkri
  • 1,461
  • 1
  • 1
  • 8
0
votes
1 answer

What exactly does Oracle NOCYCLE parameter do in hierarchical queries?

Years ago Oracle introduced an optional NOCYCLE parameter to hierarchical queries. However, there is little clarity as to what exactly it does, besides avoiding throwing an error when a cycle is detected. Most of the material that I have seen on the…
Dmitry E.
  • 93
  • 8
0
votes
0 answers

Oracle, comma separated list to rows for every row in table

I have data looking like this: ID CONTENT 1474 1442,1522,1420 1472 1442,1419 1473 1442,1418 1674 1442,1493,1523,1421 I want output like ID ID2 1474 1442 1474 1522 1474 1420 etc. etc. Im trying to use connect by: SELECT…
q4za4
  • 630
  • 4
  • 12
0
votes
2 answers

Compute the child record value based on the parent value in Oracle PLSQL

I am trying to formulate a query in Oracle DB such that it computes the start_date value for the rows having it as null based on the numoddays , lvl (level), and the previous level's start_date column. For an example: Linenumber 3 and item…
TheDS
  • 101
  • 2
  • 11
0
votes
0 answers

Hierarchical caching with expiry

Requirements: We have a list of users. For every user let us say, there are 2 types of rewards applicable. For each type there is a list of the rewards that need to be given out to the specific user. Every reward has a specific expiry, after which…
Gurucharan Sharma
  • 369
  • 2
  • 14
0
votes
1 answer

View to return the root node given any children (in Oracle)

We manufacture equipment, and we give every unit we produce a serial number (s/n). We have a parent/child table which we use to establish the relationship between the top-level assemblies and its children. I am providing a sample layout of such…
Leoncio Sobrino
  • 73
  • 1
  • 7
0
votes
0 answers

Hierarchical queries, is this example correct?

I'm trying to understand this concept, but this example gives me a headache. I'd expect that it should behave somehow similarly like resursion/mathematical induction. On every step you have a current value and a rule based on which you can calculate…
Pan Tomima
  • 1
  • 1
0
votes
1 answer

Re-writting the query without "Connect By "

I am rewriting the query to replace to remove CONNECT BY: SELECT * FROM ADM_TRT AT INNER JOIN UTILISATEUR U ON U.UTI_ID = AT.UTI_ID INNER JOIN ( SELECT CM.MAI_ID FROM CON_MAI CM CONNECT BY…
Yogus
  • 2,307
  • 5
  • 20
  • 38
1 2 3
…
9 10