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
0
votes
1 answer

oracle sql find top level part in multilevel structure

I would like to ask you to help me to find top level id from multilevel structure. my code is: SELECT level,id_component, CONNECT_BY_ROOT id_part FROM structure START WITH id_component = '123' CONNECT BY PRIOR id_part = id_component this give…
denn
  • 137
  • 1
  • 5
  • 14
0
votes
2 answers

Connect by prior get parents and children

I'm working on a query which use connect by prior. I have written a query which retrieves all children of an entity. What I want is to retrieve both children and parents rows. Here is my SQL : Select * From myTable tab Connect By Prior tab.id=…
Ananta
  • 660
  • 1
  • 7
  • 19
0
votes
0 answers

Connect BY + Join Full Node with Oracle

I get a caller, callee path from a table by using a CONNECT BY query. Then using the result, I get info which is in another table so, I use left join. When I use left join, I used callee as a condition. At this case, the root node's info is…
Ui-Gyun Jeong
  • 143
  • 1
  • 4
  • 14
0
votes
1 answer

Oracle sql - hierarchical query

I am struggling to do a hierarchical query on the following table. CLASSSTRUCTUREID PARENT CLASSIFCATIONID 1688 FLT 1689 1688 ASSET 1690 1688 PMFLT 1691 1688 …
jax
  • 840
  • 2
  • 17
  • 35
0
votes
1 answer

Access path between parent and child tables in oracle

When a table_name (T9) and a column_name (C1) is given, I need to find out nearest parent table which is having column_name (C1) in it. For example: Parent table path for T9 T9(C9,C8) -> T8(C8,C7) -> T7(C7,C1) -> T6(C1,C2) -> T5(C1,C3) T9(C9,C11) ->…
Kishor Kumar
  • 153
  • 1
  • 14
0
votes
2 answers

SQL Connect clause - generate all data by dates

The data in by table is stored by effective date. Can you please help me with an ORACLE SQL statement, that replicates the 8/1 data onto 8/2, 8/3,8/4 and repeat the 8/5 value after? DATE VALUE1 VALUE2 8/1/2017 x …
user3498646
  • 25
  • 6
0
votes
0 answers

Performance issue on request with "connect by" + "not in"

I'm trying to extract a list of IDs from a hierarchical table (non-cyclic trees) where all of the entries in the tree are matching a condition for deletion. The structure of the table is similar to this : ╔════╦═══════════╦═══════════╗ ║ id ║…
Pilou
  • 1,398
  • 13
  • 24
0
votes
1 answer

How To make a branch Connect by's condition in Sql

Hi I am trying to make a query which draws the full call-graph of a code. the table's columns are caller class, caller method, callee class, callee method but some of callee classes starts with un-necessery words. so when it detected, I have to…
Ui-Gyun Jeong
  • 143
  • 1
  • 4
  • 14
0
votes
1 answer

How to improve the performance of a connect-by query in Oracle

I have the following connect by query which takes approximately 50 to 60 seconds for a record count of 500 in my_table_name. Can any one of you please suggest a better way of writing this query to improve the performance. SELECT DISTINCT…
Kalyan
  • 113
  • 2
  • 10
0
votes
2 answers

How can I subtract two row's values within DIFFERENT columns using SQL

I need help calculating downtime in between processes. It needs to be grouped by IMPORTID and then IMPORTREQUESTID. IMPORTREQUESTID corresponds to different phases within the IMPORTID request. So, for example in below data set I need my SQL query to…
Reza Taksokhan
  • 1
  • 1
  • 4
0
votes
3 answers

CONNECT BY allowing only one cycle

Let's say that I have the following table Row_ID | SourceId | TargetId ---------|----------|---------- 1 | 1 | 2 2 | 2 | 3 3 | 2 | 4 4 | 4 | 5 5 | 5 | 6 6 …
José Carlos Ribeiro
  • 121
  • 2
  • 9
0
votes
2 answers

Change select with connect by prior from Oracle to SQL Server

Hello I have this part of a view in an Oracle database and I must change it on Microsoft Sql Server. with V_LOCHIERARHY_N (nr, nivel, location, parent, systemid, siteid, orgid, count_a, count_wo, children) AS SELECT LEVEL, LPAD (' ', 2 * (LEVEL…
Madalina
  • 85
  • 2
  • 15
0
votes
0 answers

ORACLE: Range between Weeks

How would one create my expected results. Any help would be appreciated, Thanks in advance! Master Calendar: SELECT DISTINCT --CA.CALENDAR_DATE, TO_CHAR(CALENDAR_DATE,'MM/DD/YYYY') AS CALENDAR_DATE …
Toby
  • 135
  • 2
  • 17
0
votes
1 answer

Hierarchical Query with Correlation

I've 2 tables IndexTab, ChildNodes. Index Tab has a coljumn called "Index" which gives unique value Index 150 160 170 These values are truncated and randomly populated each execution. This table is related to below child…
user1089783
  • 63
  • 2
  • 8
0
votes
2 answers

Oracle Connect By seems to produce too many rows

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 I expect I'm just missing something, but if I run this query without the "connect by", I get 2 rows. When I add "connect by level <= 4", I would expect to get each of those 2 rows 4 times. …
Matt Knowles
  • 387
  • 2
  • 12
1 2 3
…
16 17