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
28
votes
4 answers

What is the equivalent PostgreSQL syntax to Oracle's CONNECT BY ... START WITH?

In Oracle, if I have a table defined as … CREATE TABLE taxonomy ( key NUMBER(11) NOT NULL CONSTRAINT taxPkey PRIMARY KEY, value VARCHAR2(255), taxHier NUMBER(11) ); ALTER TABLE taxonomy ADD CONSTRAINT taxTaxFkey FOREIGN…
dacracot
  • 22,002
  • 26
  • 104
  • 152
15
votes
4 answers

Why does CONNECT BY LEVEL on a table return extra rows?

Using CONNECT BY LEVEL seems to return too many rows when performed on a table. What is the logic behind what's happening? Assuming the following table: create table a ( id number ); insert into a values (1); insert into a values (2); insert into a…
Ben
  • 51,770
  • 36
  • 127
  • 149
13
votes
2 answers

Calculate the percentage of the root owned by its parents

In simplified terms, I'm trying to calculate the percentage of the root of a tree owned by its parents, further up the tree. How can I do this in SQL alone? Here's my (sample) schema. Please note that though the hierarchy itself is quite simple…
Ben
  • 51,770
  • 36
  • 127
  • 149
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 select asterisk connect by join sql-92 combination

Following query shows that select * combined with connect by and left join doesn't return all columns, but only columns used in these conditions. This behavior was useful for me, given that select * should not be used in release, it is useful…
Nahuel Fouilleul
  • 18,726
  • 2
  • 31
  • 36
8
votes
3 answers

CONNECT BY or hierarchical queries in RDBMS other than Oracle

Oracle ships with a very handy feature. You can create hierarchical queries (recursive behaviour) using the following clause: CONNECT BY [NOCYCLE] {condition [AND condition...]} [START WITH condition] As documented…
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
8
votes
3 answers

SQL Server Equivalent of Oracle 'CONNECT BY PRIOR', and 'ORDER SIBLINGS BY'

I've got this Oracle code structure I'm trying to convert to SQL Server 2008 (Note: I have used generic names, enclosed column names and table names within square brackets '[]', and done some formatting to make the code more readable): SELECT…
user1058946
  • 243
  • 2
  • 3
  • 14
8
votes
3 answers

How to get all values in hierarchy linked to some value using Oracle CONNECT BY

Relationship model is 1 3 \ / \ 2 4 \ 7 5 8 \ / / 6 9 Table is : select 2 child, 1 father from dual union all select 2 child, 3 father from dual union all select 4 child, 3 father from dual union all select…
InterloperUA
  • 83
  • 1
  • 5
8
votes
3 answers

Joining other tables in oracle tree queries

Given a simple (id, description) table t1, such as id description -- ----------- 1 Alice 2 Bob 3 Carol 4 David 5 Erica 6 Fred And a parent-child relationship table t2, such as parent child ------ ----- 1 2 1 3 4 …
dland
  • 4,319
  • 6
  • 36
  • 60
5
votes
4 answers

Oracle: get countries separated by N borders

I would like to get all countries separated by N (1,2,3,4 ...) borders from a specified country. N should also be specified. For example I have the table "borders" and "country": border | neighbor ----------------- FR | DE FR | IT …
Hayk
  • 71
  • 6
5
votes
2 answers

oracle 9i get highest member of tree with given child

I have a parent-child relationship in an Oracle 9i database-table like: parent | child 1 | 2 2 | 3 2 | 4 null | 1 1 | 8 I need to get the absolute parent from a given child. Say, I have child 4, it has to give me parent:…
jwdehaan
  • 1,445
  • 3
  • 13
  • 25
5
votes
2 answers

Hierarchical Query Needs to Pull Children, Parents and Siblings

Can now pull the data, but am wondering if there is a better way to optimize the query for large data sets. http://sqlfiddle.com/#!4/0ef0c/5 So basically I want to be able to supply the query a given org id and have it recursively pull its parents,…
Gabe Ortiz
  • 147
  • 7
5
votes
1 answer

How to get the final parent id column in oracle connect by sql

1 Create table like this CREATE TABLE oracle_connet_by_test ( item_id NUMBER PRIMARY KEY, parent_id NUMBER, item_desc VARCHAR2(8)); INSERT INTO oracle_connet_by_test VALUES(1, 0, 'AAA'); INSERT INTO oracle_connet_by_test…
Kedron
  • 343
  • 1
  • 3
  • 9
4
votes
1 answer

'START WITH' equivalent expression in MS-SQL

Oracle SQL supports START WITH expression. For instance, CREATE VIEW customers AS SELECT LEVEL lvl, customer_code, customer_desc, customer_category FROM customers_master START WITH some_column = '0' CONNECT BY PRIOR CUSTOMER_CODE =…
Giorgos Myrianthous
  • 36,235
  • 20
  • 134
  • 156
4
votes
2 answers

Oracle connect by including a stop criteria

There is a table articles including hierarchical articel structures. 1 assembly consists out of n components. So we are able to browse the structure and usages (up and down) for an article. Using Oracles hierarchical queries this can be done very…
wenzul
  • 3,948
  • 2
  • 21
  • 33
1
2 3
…
16 17