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
3 answers

How to get to the "final predecessor" with a CONNECT BY query?

I have a table, SELECT * FROM data id pred name visual link, for your convenience -------------------- 1 null One 20 null Two <--+ 21 20 Three -^ 30 null Four <--+ 31 …
towi
  • 21,587
  • 28
  • 106
  • 187
1
vote
2 answers

Oracle - theoretical sql query for create intervals

Is it possible to solve this situation by sql query in ORACLE? I have a table like this: TYPE UNIT A 230 B 225 C 60 D 45 E 5 F 2 I need to separate units to the three(variable) 'same'(equally sized) intervals…
Petr
  • 1,193
  • 1
  • 15
  • 27
1
vote
2 answers

Oracle hierarchical selective Query

I'm trying to write a hierarchical Oracle 11g query that would select all nodes from a hierarchy from the top level to the second last level. For the last level or leaf nodes, the query should only select one random node, so that is, not all the…
sergiopuy
  • 43
  • 2
  • 10
1
vote
2 answers

Can't use CONNECT BY and GROUP BY together

I have two tables - employee and manager. One manager has many employees. I want to show it in parent-child relationship using CONNECT BY and GROUP BY command. Structure for EMPLOYEE table: CREATE TABLE employee ( employee_id INTEGER, manager_id…
saurabhk
  • 140
  • 1
  • 4
  • 14
1
vote
2 answers

Summarize in ORACLE hierarchical query

I need some advise because I am very new to hierarchical queries :( I.e. I have the following table: CREATE TABLE routes ( from VARCHAR2(15), to VARCHAR2(15), price NUMBER ); INSERT INTO routes VALUES('San Francisco',…
user1777122
  • 77
  • 1
  • 5
1
vote
0 answers

Hierarchical Query: Get the direct children count for each node

I am new to hierarchical queries and I have gone through most of the posts on it. I have a query that i would like to improve. My Scenario is something like this: Sample ------- ID, NAME, PARENT_ID Table Data as follows: ID NAME …
Monikka
  • 518
  • 6
  • 12
1
vote
1 answer

Extremely slow query using CONNECT BY Oracle 10

I have a table challenge containing about 12000 rows. Every point connects to the four points around it, for example 100 connects to 99 101 11 and 189. I tried this with a small scale table and it worked just fine but as I increased the size of the…
Alex Beebe
  • 327
  • 1
  • 3
  • 7
1
vote
1 answer

"Connect By" to generate rows from multiple delimited string

we can use "Connect By" to generate rows from a delimited string in oracle. like: SELECT Rn ,Regexp_Substr(data, '[^,]+', 1, LEVEL) Data FROM (SELECT 1 Rn ,'id:a,val:b,desc:c' data FROM Dual) Idata CONNECT BY Regexp_Substr(data, '[^,]+', 1, LEVEL)…
Plymouth Rock
  • 472
  • 2
  • 6
  • 20
1
vote
0 answers

Huge condition with connect by in Oracle

I have some optimization problem with Oracle 11g database. I got query with structure as below: select (....) from (select ...) where (...) CONNECT BY PRIOR J.JDN_ID_POD = J.JDN_ID_NAD and (HUGE_CONDITION) START WITH …
Pablo
  • 11
  • 2
1
vote
1 answer

Switch data between columns

Take the following example code: with test as ( select to_date('01/2012', 'mm/yyyy') as dt, '1' as value from dual union all select to_date('02/2012', 'mm/yyyy') as dt, '10' as value from dual union all select to_date('03/2012',…
filippo
  • 5,583
  • 13
  • 50
  • 72
1
vote
3 answers

oracle connect by multiple parents

I am facing an issue using connect by. I have a query through which I retrieve a few columns including these three: ID ParentID ObjectID Now for the same ID and parentID, there are multiple objects associated e.g. ID ParentID ObjectID 1    0…
Riddhi Shah
  • 73
  • 1
  • 2
  • 12
1
vote
1 answer

CONNECT BY and START WITH clauses in oracle sql

I want hierarchical data for the test data given below. For user 'jones' it should fetch the below records. I am having problem with CONNECT BY and START WITH clauses. I also think there is some JOIN problem between menu_items AND role_menu_items…
user739115
  • 1,117
  • 5
  • 20
  • 41
1
vote
1 answer

Search in Path which created by SYS_CONNECT_BY_PATH in Connect By Prior Query

I'm using Connect By query to show hierarchical groups on a group, on this page I have a Combobox for selecting parent groups. But I want to hide all rows which contain group that currently being edited. This is my…
Tufan Barış Yıldırım
  • 3,952
  • 26
  • 36
0
votes
1 answer

SQL Optimization: CONNECT BY ... START WITH

I have a query using CONNECT BY and START WITH statement like below. The query in the IN clause took less than 5 seconds to run and returns 3000 rows. fact_table contains 20M of records. How can I optimize this SQL below because this query takes…
Ianthe
  • 5,559
  • 21
  • 57
  • 74
0
votes
0 answers

Using columns from two different tables in connect by prior clause in SQL

I am facing trouble in writing a recursive SQL query in aws redshift using different columns from two tables , in most of the connect by prior tutorials i have seen the recursion is done on the columns of the same table but i am unsure of how to do…
dsnoob27
  • 31
  • 8
1 2 3
…
16 17