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
1
vote
1 answer

Correlated row-generating query in Oracle

Given this starting CTE: WITH Sections AS ( SELECT 1 Section, 1 StartUnit, 5 EndUnit FROM DUAL UNION ALL SELECT 2, 0, 2 FROM DUAL UNION ALL SELECT 3, 1, 1 FROM DUAL ), How do I generate a result set that has as many rows per row in…
ErikE
  • 48,881
  • 23
  • 151
  • 196
1
vote
1 answer

i want to get just the end parent for each child using oracle connect by , start with statement

I am using start with , connect by statement to get data recursivly, i am getting all parent - child but i just want to get the end parent for each child. for eg i have following data child --> parent a ------> b, b ------> c, c ------> d, c…
sandeep dhaliwal
  • 47
  • 2
  • 8
1
vote
2 answers

"OR" operator in CONNECT BY PRIOR

The rows in the database are: DATAID OWNERID PARENTID 111 123 133 976 346 111 987 976 657 I want to display all these rows but by using the following query only the top 2 rows are getting displayed and I need all the 3…
ghostrider
  • 2,046
  • 3
  • 23
  • 46
1
vote
2 answers

Oracle PL/SQL switch between raw and hierarchical select

I need in one Oracle PL/SQL select to switch between raw select and hierarchical select statements depending on some variable. But, cannot use If statement in select. For example, I have hierarchical statement select a.* from myTable a start with…
dragy
  • 187
  • 5
  • 22
1
vote
0 answers

SQL Oracle hierarchy query to get all parents AND children

I am trying to build one SQL query that returns both all parents and all children. This first SQL returns all parents given a base SELECT BASE, LISTAGG (PARENT_ENTITY_ID, ';') WITHIN GROUP (ORDER BY BASE) AS PARENTS …
Erick
  • 823
  • 16
  • 37
1
vote
2 answers

Insert a range of dates and should be unique with another column

I have table CREATE TABLE T_TEST ( KURS_NUMBER NUMBER PRIMARY KEY, KURS_ID NUMBER NOT NULL, DATEKURS DATE NOT NULL, CONSTRAINT UNIQUE2 UNIQUE (KURS_ID,DATEKURS) ); TRIGGER for kurs_number create or replace trigger TR_INSERT_TEST01 before…
vesperkg
  • 133
  • 7
1
vote
2 answers

Create multiple rows based off a date range

I have a calendar query and a table below. I have a StartDate and end date for a member. Also on my calendar table I have captured a "Weekof" based on the startDate. I would like to capture if a member is active anytime during that weekof. See…
John
  • 109
  • 1
  • 5
  • 12
1
vote
2 answers

Getting Oracle SQL top level parent record from a number of parent/child records

I'm trying to create an Oracle SQL statement to get the top root level parent record from a number of parent, child records on different levels. The table structure is below. The top level root parent in the below is parent_membership_id 53887, this…
Shaun Kinnair
  • 495
  • 2
  • 10
  • 27
1
vote
1 answer

Oracle multi-hierarchical query

I have several comma seperated lists (A,B,..) in different scopes (102,103,...): SCOPE_ID LIST_NAME LIST_OF_NODES 102 list_A 02086,02087,02089,02131,02210,02211 102 list_B 00004,06003,06338 103 list_A …
Jakub P
  • 542
  • 4
  • 21
1
vote
1 answer

SQL Query Returning only 5 records instead of 6 rows

I am trying to fetch the data present in a single column which is delimited with double hash (##). As per my query mentioned below, i am able to fetch only 5 records instead of 6 lines. I could think there is some issue with my connectby…
JustCoder
  • 327
  • 1
  • 4
  • 13
1
vote
2 answers

Oracle SQL - select rows with the highest LEVEL

I have query as follows: SELECT CONNECT_BY_ROOT(name), LEVEL FROM Workers CONNECT BY PRIOR boss=nick START WITH function IN ('programmer', 'designer'); I get: BELLA 1 BELLA 2 BELLA 3 MICKEY 1 MICKEY 2 BOB 1 BOB 2 DUDE …
michalsol
  • 752
  • 8
  • 29
1
vote
2 answers

how to fetch start by attribute in a connect by sql

I am using recursive sql and I have a query like this SELECT REVERSE(SYS_CONNECT_BY_PATH(REVERSE(name), '\')) as Path,parentfolderid FROM FOLDER_Table START WITH FOLDERID=12345 CONNECT by PRIOR PARENTFOLDERID=FOLDERID how can I display the…
dreambigcoder
  • 1,859
  • 4
  • 22
  • 32
1
vote
1 answer

show the parent of a data using connect by prior?

i have this this table where i have parentitem | childitem ---table name dinner | steak dinner | wine dinner | mashed potato dinner | coffee coffee | sugar coffee | …
robin g
  • 61
  • 7
1
vote
0 answers

Connect by prior with changing rules

I have a table like this: dataid | parentid | name ---------------------------- 1 | 99 | root ---------------------------- 2 | -1 | folder1 ---------------------------- 3 | -1 | folder2…
ifuwannaride
  • 121
  • 3
  • 13
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
1 2 3
…
16 17