Stack Overflow
Stack Overflow

Questions tagged [connect-by]

A hierarchical query clause in Oracle.

CONNECT BY is a hierarchical query clause used in . It specifies the relationship between the parent and child rows in the hierarchy.

Questions tagged should usually also be tagged .

Further Reading:

252 questions
3
votes
2 answers

NOCYCLE in Postgres

I have a Oracle query with a NOCYCLE clause which I have to translate to Postgres: SELECT FG_ID,CONNECT_BY_ROOT FG_ID as Parent_ID FROM FG t START WITH t.Parent_filter_group_id is null CONNECT BY NOCYCLE PRIOR t.FILTER_GROUP_ID =…
SarthAk
  • 1,628
  • 3
  • 19
  • 24
3
votes
1 answer

How to build hierarchy paths with hierarchical subqueries

EDIT: I provided additinal information by introducing location entity to make it clear why I tried to use subqueries In oracle 11g database I have hierarchical structured table of elements which will eventually contain few millions of rows. Each row…
Pavle Gartner
  • 659
  • 1
  • 7
  • 21
3
votes
2 answers

Oracle 10g PL/SQL Connect By Prior returning Child and Parent on same row

Table1: Child Parent a Bob Chris 2 Chris Kate 1 Shane Lana 3 Nala Bob 4 Table2: b Talent 1 'something' 2 'nothing' 3 'something' 4 …
RebeccaK375
  • 871
  • 3
  • 17
  • 28
3
votes
1 answer

Oracle "connect by" - multiple hierarchies

We're on Oracle 11g R1. Here's the code: CREATE TABLE T1 (ID NUMBER, PARENT_ID NUMBER, LEFT_SIBLING_ID NUMBER); INSERT INTO T1 VALUES (1,NULL,NULL); INSERT INTO T1 VALUES (3,1,NULL); INSERT INTO T1 VALUES (2,1,3); INSERT INTO T1 VALUES…
SKY
  • 83
  • 1
  • 8
3
votes
1 answer

Generate subsequent numbers between number range with SQL

I have a table containing the following: ID low_value high_value 1 3270200000 3270210000 2 3270210000 3270220000 3 3270220000 3270230000 4 3270230000 3270231000 5 3270231000 3270232000 6 3270232000 …
tijz
  • 33
  • 3
3
votes
1 answer

Oracle Self-Join on multiple possible column matches - CONNECT BY?

I have a query requirement from ----. Trying to solve it with CONNECT BY, but can't seem to get the results I need. Table (simplified): create table CSS.USER_DESC ( USER_ID VARCHAR2(30) not null, NEW_USER_ID VARCHAR2(30), …
James King
  • 6,233
  • 5
  • 42
  • 63
3
votes
2 answers

Finding the "deepest" child in a hierarchical query

I need some help querying hierarchical data. Here is the single, simple, table, where parent_id references id and may be null for root entries. create table edition ( id NUMBER(20), parent_id NUMBER(20) ); For each…
andbi
  • 4,426
  • 5
  • 45
  • 70
2
votes
2 answers

Oracle CONNECT BY - only returning first level item

I'm trying to select a parent product and all of it's child products; explode the Bill Of Materials (BoM), if you will. This should be a be simple enough task, but I'm not getting the expected result. SELECT LEVEL, serial_no, part_no FROM…
CJM
  • 11,908
  • 20
  • 77
  • 115
2
votes
1 answer

Formatting a hierarchical query in a nested tree syntax (Oracle)

I am working with data that is represented using the following basic syntax: a→b→c EDIT This is used to describe a spatiotemporal relation of alteration assemblages around an ore deposit. An alteration assemblage can either be spatially or…
Michael
  • 47
  • 5
2
votes
1 answer

Multi column hierarchical query in Oracle SQL

I have a assignment table containing employees with employee id and project id, with primary key as assignment id. A project can have multiple employees, and an employee can be in multiple projects. Given an employee id, it's required to get a…
Crushinator
  • 21
  • 2
2
votes
1 answer

Oracle SQL: to count the records based on fixed time frame (say 15 or 30 minutes)

I have a table similar to Start time | End Time | User | 09/02/2021 03:01:13 | 09/02/2021 03:45:15 | ABC | 09/02/2021 03:15:20 | 09/02/2021 05:03:20 | XYZ | 09/02/2021 06:03:12 | …
teja akula
  • 21
  • 1
2
votes
2 answers

ORA-30009: Not enough memory for CONNECT BY operation / ORA-01788: CONNECT BY clause required in this query block

Oracle newbie here - I am trying to run an insert statement to generate a very large amount of data. The original query is this: INSERT INTO HR.fastData (ID) SELECT 1 + (Level -1) * 1 FROM dual connect by Level < 100000000; First error…
slsu
  • 133
  • 3
  • 13
2
votes
1 answer

Retrieve ascendant rows of a hierarchy

I have a sample table that contains 3 columns: id, parent_id and name Let's say that the hierarchy that is present in my table is as follows: I I.A I.A.a I.A.b I.A.b1 I.A.b2 I.B I.C II II.A My goal is to retrieve all…
Strider
  • 3,539
  • 5
  • 32
  • 60
2
votes
1 answer

Postgres - Select rownumber without a table but data from a function

I'm trying to get some result like this: rownumber | value 1 | a 2 | b 3 | c Without any tables I'm doing something like this: WITH RECURSIVE t(rownumber, value) AS ( select 1, regexp_split_to_table('a, b, c',…
some_coder
  • 23
  • 1
  • 2
2
votes
2 answers

Performance issues in SQL query with a hierarchical relationship

I have an Oracle table that represents parent-child relationships, and I want to improve the performance of a query that searches the hierarchy for an ancestor record. I'm testing with the small data set here, though the real table is much…
Chris Farmer
  • 24,974
  • 34
  • 121
  • 164
1 2
3
…
16 17