Questions tagged [hierarchical-query]
146 questions
0
votes
1 answer
Use only the most recent row in oracle start with/connect by prior query
My hierarchical query looks like this:
select ID, PARENT_ID, START_TIME
FROM PROCESS
START WITH ID='X'
CONNECT BY ID=PRIOR PARENT_ID
My problem is that there are rows with the same ID 'X' and I only want the most recent one returned (when a process…
Mike
- 609
- 12
- 36
0
votes
1 answer
Query to retrieve manager before employee
We have list of resources with their managers (not the complete hierarchy). We now need to retrieve the resource list such that resource's manager row should always appear before the resource row. I already tried hierarchical query, however given…
Vishal
- 198
- 1
- 3
- 11
0
votes
2 answers
Tree Structure query in oracle , with one integer field and on varchar field
Here I need the tree structure query, with one int and other is varchar field. I don't want to modify varchar to int.
Table structure:
ID INT
SUB_TASK_ID INT
TASK_ID INT
PID INT
WBS_ID VARCHAR
WBS_TYPE VARCHAR
Here I'm trying to cast the…
ankita lembhe
- 161
- 1
- 11
0
votes
1 answer
Oracle START WITH clause returns no root rows
I'm performing the following query in Oracle 10g to retrieve data in hierarchical form (the complete query is a bit more complex):
SELECT LEVEL AS lvl, a.*
FROM (
WITH temp
AS (...)
SELECT id_request, subj_type, id_subj, name,…
Alessandro Cucina
- 148
- 1
- 12
0
votes
1 answer
Rebuild oracle hierarchical query from two tables
I am working on oracle hierarchical query for the below table
Classification_Product and Order_details tables
Classification Product has the Classification_id which will be stored in the Order_detailsaccording to user selection
Classification…
sam
- 2,493
- 6
- 38
- 73
0
votes
1 answer
MySQL FULLTEXT returning only best answer
I'm trying to modify the code for fullpath retrieval from this post:
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ in nested-set model to use FULLTEXT instead of exact match.
Here's what I want: I want to obtain best match for…
Klemens
- 53
- 6
0
votes
2 answers
Oracle Connect By seems to produce too many rows
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
I expect I'm just missing something, but if I run this query without the "connect by", I get 2 rows. When I add "connect by level <= 4", I would expect to get each of those 2 rows 4 times. …
Matt Knowles
- 387
- 2
- 12
0
votes
1 answer
How to maintain the displayorder of oracle and postgreSQL?
I want the same display order in postgreSQL as generated in oracle output.
I have tried using the following oracle query and output shown below
create table student(
std_id int, std_roll int
);
SELECT std_id, std_roll
FROM student
CONNECT BY…
neo
- 126
- 1
- 13
0
votes
1 answer
Is there an easy way to apply REGEXP_LIKE to path coming from connect by prior query in Oracle?
I have a query including connect by prior like;
SELECT SYS_CONNECT_BY_PATH(ENAME, '/') as path
FROM EMP
WHERE CONNECT_BY_ISLEAF=1
START WITH MGR IS NULL CONNECT BY
PRIOR EMPNO=MGR;
The result is following
…
user6493966
- 73
- 2
- 8
0
votes
0 answers
Hierarchical queries in MySQL - find all decendants
The answer of this post here provides one single query to find all ancestors of a node in a adjacency list.
Now, how do I find all decendants of a node using similar query? I tried:
SELECT @id :=
(
SELECT receiverid
FROM …
Shawn
- 2,675
- 3
- 25
- 48
0
votes
2 answers
MySQL Query to get all Children in tree when we pass parent_id
I have been looking at the query resulting total number of employees under an superior emp_id that has been passed to the query.
Example : for emp_id 1 he has '2,3,4,5' as reportees and under again for
emp_id 3 there are '6,7' are reportess and…
user93068
- 31
- 4
0
votes
2 answers
Not able to refer column from inner query in hierarchical query?
I am using hierarchical query to fetch out details from my table structure. I am referrring column from inner query in 'START WITH' condition, but its throwing unknown column error please check
Query :
select
(select obj.name
from (select…
rohansr002
- 107
- 7
0
votes
1 answer
Identifying Keys for new hierarchies
Assume my source looks like
X1 H_ID l1_no l2_no l3_no
20 1 2 12 42
20 1 6 16 42
20 1 1 19 41
20 1 5 15 41
And my lookup table looks like
X1 H_ID l1_no l1_key …
Srini V
- 11,045
- 14
- 66
- 89
0
votes
1 answer
Why CONNECT_BY_ISCYCLE doesn't give 1 when table has a cycle?
As you all know scott.emp table of Oracle doesn't have any cycle in hierarchy.
But I changed emp table a little (UPDATE emp SET mgr=7654 WHERE empno=7698) where I changed Martin's manager as Blake and Blake's manager as Martin and fired following…
Debapriya Dutta
- 13
- 4
0
votes
1 answer
Hierarchical query with one to many relationship
I have three tables; d_algorithme:
ID_ALGO VERSION_ALGO LIBELLE_ALGO
---------- ------------ --------------------------------------------------
300 A300V1 Algo_300_V1 …
abderrahim_05
- 445
- 1
- 4
- 21