Questions tagged [hierarchical-query]
146 questions
23
votes
7 answers
Cycle detection with recursive subquery factoring
Oracle SQL can do hierarchical queries since v2, using their proprietary CONNECT BY syntax. In their latest 11g release 2, they added recursive subquery factoring, also known as the recursive with clause. This is the ANSI standard, and if I…
Rob van Wijk
- 17,555
- 5
- 39
- 55
20
votes
1 answer
How to traverse a hierarchical tree-structure structure backwards using recursive queries
I'm using PostgreSQL 9.1 to query hierarchical tree-structured data, consisting of edges (or elements) with connections to nodes. The data are actually for stream networks, but I've abstracted the problem to simple data types. Consider the example…
Mike T
- 41,085
- 18
- 152
- 203
15
votes
2 answers
Connect by in Oracle SQL
Suppose that we have following tables
create table Employee(
2 EMPNO NUMBER(3),
3 ENAME VARCHAR2(15 BYTE),
4 HIREDATE DATE,
5 ORIG_SALARY NUMBER(6),
6 CURR_SALARY NUMBER(6),
7 REGION …
user466534
12
votes
3 answers
Does PostgreSQL have a pseudo-column like "LEVEL" in Oracle?
Does PostgreSQL have a pseudo-column like "LEVEL" in Oracle?
If not, then how can we create a column similar to "LEVEL"?
Neha
- 201
- 1
- 4
- 10
9
votes
2 answers
Oracle CONNECT BY clause after GROUP BY clause
I have just run across this interesting article here, showing how to simulate wm_concat() or group_concat() in Oracle using a hierarchical query and window functions:
SELECT deptno,
LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
KEEP…
Lukas Eder
- 211,314
- 129
- 689
- 1,509
5
votes
1 answer
Why does Oracle pseudo column CONNECT_BY_ISLEAF seems broken?
First a short description of my Issue
I have a table containing data which may be represented in this pseudo directed graph:
I say it is a pseudo-graph because I have some «edges» which are only connected on 1 node.
Each «edges» are labelled, and…
Ckln
- 195
- 3
- 10
5
votes
2 answers
get ALL last level children (leafs) from a node (hierarhical queries Oracle 11G)
I am trying and searching the way to get ALL last level children (leafs) from a node, in a hierchical query in Oracle 11g database.
I have 2 tables: "Nodes" (A list of all nodes with their respective value), and "Relation" which specify…
AlexAcc
- 601
- 2
- 10
- 28
5
votes
2 answers
How to do WHERE-CLAUSE in hierarchical query in Oracle
In Oracle hierarchical query, the WHERE-CLAUSE should be evaluated after the Connect-By operator in oracle document says.
But there are complex situations: if the WHERE-CLAUSE contains JOIN-style qualification, as oracle says, the Join-Style…
user1527818
- 51
- 1
- 3
4
votes
9 answers
Find the second highest salary
Well it is a well known question. Consider the below
EmployeeID EmployeeName Department Salary
----------- --------------- --------------- ---------
1 T Cook Finance 40000.00
2 D Michael Finance…
user1025901
- 1,849
- 4
- 21
- 28
4
votes
2 answers
Connect by prior for a data series
I have a table with arrivals and exits from a system with its time period. I also have a forecast of arrivals and exits. I would like to compute the starting and ending count of heads for the incomplete forecasted periods (ideally using merge and…
James Paul
- 41
- 1
4
votes
2 answers
SQL syntax for update query with connect by prior
I start to work with SQL and I faced up with hierarchical queries. meanwhile I success to select the rows with connect by prior command but failed to update. Here is my update query:
update HTABLE set status = 'INACTIVE'
WHERE STATUS <> 'CLOSE'
…
AsfK
- 3,328
- 4
- 36
- 73
4
votes
1 answer
How to convert oracle hierarchical queries to postgresql?
I want to convert below mentioned oracle hierarchical query to postgresql
SELECT catalog_id, sub_tree_id
FROM my_catalog
CONNECT BY PRIOR catalog_id = sub_tree_id;
I have tried using the following postgresql query but not getting the expected…
Nayan Arora
- 79
- 1
- 3
4
votes
2 answers
Oracle 10g Connect By Prior - Performance Issues
I have the following SQL statement:
SELECT
CONNECT_BY_ROOT ANIMAL_ID "ORIGINAL_ANIMAL" ,
ANIMAL_ID, LINE_ID, SIRE_ANIMAL_ID, DAM_ANIMAL_ID,
LEVEL -1 "LEVEL" FROM ANIMALS
START WITH ANIMAL_ID IN( '2360000002558' )
CONNECT BY
((PRIOR…
Paul Brower
- 41
- 1
- 2
4
votes
1 answer
Infinite loop in H2 query
I am executing the following query on an H2 database through a test ran using JUnit, on a table with 3 entries:
WITH ancestors(ID, PARENT_ID) AS
(
SELECT ID, PARENT_ID FROM PEOPLE WHERE ID =
UNION ALL
SELECT P1.ID, P1.PARENT_ID…
Vlad Schnakovszki
- 8,434
- 6
- 80
- 114
4
votes
3 answers
Connect by query
I'm storing hierarchical data in a table. When a resource is accessed by its hierarchical path (grantParent/parent/resource), I need to locate the resource using a CONNECT BY query.
Note: SQL commands are exported from EnterpriseDB, but it should…
Karthik Murugan
- 1,429
- 3
- 17
- 28