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

Accessing ancestors in Oracle hierarchical query (CONNECT BY PRIOR)

I'm currently working on building a Bill of materials for a few projects and have run into an issue. I have a source table with the following columns: Part Component qty Naturally there are other columns, but for the sake of simplicity... The…
Henrik Poulsen
  • 935
  • 2
  • 13
  • 32
1
vote
1 answer

how to select all children and parents of a node in sql / oracle

I have a table like below MyTable | ID | PARENT_ID | ----------------------------- | 20 | null | | 40 | null | | 50 | null | | 70 | 122 | | 100 | 40 | | 102 | 4 | | 126…
Elsayed
  • 2,712
  • 7
  • 28
  • 41
1
vote
1 answer

Connect By clause works on 11g but not on Oracle 8i : "ORA-01436: CONNECT BY loop in user data"

I found the code for a row generator from this question Create View with 365 days CREATE VIEW year_days (the_day) AS SELECT TRUNC(SYSDATE, 'YYYY') + (LEVEL-1) AS the_day FROM DUAL CONNECT BY LEVEL <=…
mach128x
  • 320
  • 3
  • 13
1
vote
1 answer

Oracle SQL/PLSQL: Hierarchical query to split and sum value based on currency

With any luck I can describe my requirement without confusing myself... I have a multi-level dataset where users can flag which records to summarise into different totals. Data Entry Rules: IF 'Base Unit' column (True/False) is checked (i.e. True)…
pwlm
  • 174
  • 2
  • 2
  • 19
1
vote
2 answers

Oracle SQL - Derive multiple rows from column combination of single row

I have this kind of data on a Oracle SQL DB: personId lastEvent currentEvent nextEvent 1 null 1 2 1 1 2 3 1 2 3 4 1 3 4 null For a single row…
VahnIronSky
  • 25
  • 5
1
vote
1 answer

Oracle nested CONNECT BY clauses causing poor performance

The query below is taking about a minute. I believe the poor performance is caused by the two "IN (SELECT..." clauses. I have a table of terms where one may be connected to another via the term_relationship table. These relationships can be…
Dan Newman
  • 43
  • 7
1
vote
1 answer

CONNECT BY never ending

I've got a little trouble with a recursion using Oracle START WITH / CONNECT BY. Given a table id_string, id, ordre, prec with id_string being a concatenation of id_ordre. and prec the link between two elements having the same id (1_1,1,1,2 is…
vorko
  • 13
  • 4
1
vote
2 answers

Oracle "connect by prior" along with "max() over partition by" to remove duplicate subtree

I am trying to remove duplicate subtree returned by "connect by prior" clause. I wish to check top level node of tree hierarchy, where user can enter child IDs which are already part of subtree. Take a look at following example: SELECT * FROM ( With…
user613114
  • 2,731
  • 11
  • 47
  • 73
1
vote
2 answers

Oracle: Connect by Level & regexp_substr

Hi I have an Oracle query that I did not write and I also do not have access to run in an Oracle environment. Could someone please explain what this query is doing please? WITH tableName AS ( SELECT regexp_substr(fieldName,'[^,]+',1,level) as…
Amy
  • 591
  • 3
  • 10
  • 23
1
vote
2 answers

What is a 'level' used with the date in SQL?

I have this query that has a '+level' with the date. I am not sure what it is but executing the query gives the list of dates in an ascending order. Here's how the query looks like: select date '2018-01-01' + level -1 dt from dual connect by level…
Super.Sam
  • 37
  • 1
  • 5
1
vote
2 answers

How do I get top element of a certain type?

Let's say I have something similar to this (hierarchy of folders on drive C:). I want to get top folder of a given folder (in this case I chose '1'), not the disc itself, how do I do this? Hierarchy can have various levels. with data as ( select…
Bobby
  • 534
  • 3
  • 7
  • 21
1
vote
2 answers

Oracle Connect_is_leaf similar in SQL server

Here is my query which is in Oracle PL/SQL syntax, How can I Change it to SQL server format? Any alternatives for Connect_by_isleaf? ( select PARTY_KEY, ltrim(sys_connect_by_path(alt_name, '|'), '|') AS alt_name_list from (select…
Addy
  • 36
  • 4
1
vote
3 answers

Oracle generate Data

I tried to generate data using the "by level" expression. I want to generate GROUPNR, starting with 1, meanwhile each group contains 4 items with nr 1 to 4 This is, how the result should look like groupnr itemnr 1 1 1 2 1 3 1 …
user3711142
  • 63
  • 1
  • 8
1
vote
1 answer

SQL ORACLE - BOM explosion with duplicated lines - start with...connected by prior...order sibling by

Could you please support me to identify how to remove duplication in BOM explosion done by statement start with ... connected by prior...order sibling by due to Child item repeated in BOM structure itself ? The starting table "BOM_table" is a…
gualfab
  • 11
  • 1
1
vote
2 answers

how to use connect by method dynamically

How to achieve the following I've got a query: select sr.source_rel_id, sr.source_rel_start, sr.source_rel_end, sr.source_rel_end-sr.source_rel_start+ 1 as daycount from (SELECT RELEASES.REL_ID as source_rel_id, …
imi36
  • 49
  • 5
1 2 3
…
16 17