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

Snowflake: PRIOR keyword is missing in Connect By statement

I am trying to implement a stored procedure to return the result set of a CONNECT BY query in Snowflake create or replace procedure test(email varchar(100)) RETURNS TABLE (email_address varchar(100)) LANGUAGE SQL AS BEGIN let res RESULTSET :=…
crkuchlenz
  • 33
  • 1
  • 1
  • 4
1
vote
1 answer

How to make CONNECT BY parameter optional

I have a procedure that uses Connect By SELECT FROM Group g WHERE CONNECT BY PRIOR g.ID = g.ParentID START WITH g.ID = 1337 ORDER SIBLINGS BY g.Name ; The number 1337 is a parameter on this procedure, if this…
BrunoLM
  • 97,872
  • 84
  • 296
  • 452
1
vote
2 answers

Oracle: Trying to split a string using REGEXP_SUBSTR, CONNECT BY LEVEL functions and getting duplicates

I have a table that has a column that holds an html string that may or may not be well formed (tried to use xmltable route and it didn't work) which is why I am trying to use the following sql syntax. I am trying to create a query that uses…
CreationSL
  • 55
  • 1
  • 11
1
vote
1 answer

Oracle INSERT and CONNECT by

I'm trying to load some test data into table t1 by calling 2 functions but I can't seem to get the INSERT and CONNECT by to work. Any help would be greatly appreciated. ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS.FF'; ALTER…
Pugzly
  • 844
  • 3
  • 14
1
vote
4 answers

Oracle - using connect by prior in Hierarchical Queries and put them into rows

I am new in Oracle, I am going to use connect by prior to implement the flat table instead of Hierarchical one. but I am a little bit confuse. My table is like…
Sara Moradi
  • 35
  • 1
  • 7
1
vote
2 answers

Oracle - Connect By Prior

I know I have to use CONNECT BY PRIOR in this query, but I'm not sure how to implement it. We have customers who purchase monthly subscriptions, and those get auto-renewed each month. We have a log table which can show what your current order ID is…
Landon Statis
  • 683
  • 2
  • 10
  • 25
1
vote
1 answer

Is there any to add one loop row in connect by oracle with nocycle?

Just like Oracle continues to follow a path beyond a cyclical loop when the cycle occurs at the top node (root node connected right back to root node), is there any way to do the same with in between cycle. Like if i have some data like below create…
Raminder Singh
  • 13
  • 2
1
vote
1 answer

Extract a sub-tree from a hierarchy tree based on a leaf in Oracle

I have a table users representing a hierarchical tree like this: Column Type Comment user_id integer sequence user_type integer 1 for group of users 2 for normal user group_id integer Reference to a user in the same table with user_type…
nico
  • 127
  • 1
  • 13
1
vote
2 answers

ORA-01436: CONNECT BY loop in user data in Toad Used by Tab for tables

As you know in Toad, we can see where a table is used (in a package or in a view). But when I try to see where my table is used it is showing the error :ORA-01436: CONNECT BY loop in user data. Other tables are showing up their used status…
mehfuz ayub
  • 11
  • 1
1
vote
1 answer

Oracle SQL Recursive Query When FieldType is Subpage

I have a table PanelDefn which has list following fields PanelID FieldID RecordName FieldName LableText Subpanel FieldType PRV1 1 REC1 FLD1 Name 1 PRV1 2 REC1 FLD2 Address …
Rohit Prasad
  • 135
  • 2
  • 12
1
vote
1 answer

CONNECT BY NOCYCLE generating millions of child nodes

I have a database where from_node and to_node are the columns. Trying to find all the reachable nodes from from_node to to_node. There are cycles. Few from_node using connect by nocycle, is generating millions of children nodes. How can this be…
Raghav
  • 11
  • 4
1
vote
1 answer

Hierarchical query with reference to previous row, not parent row

I have a table detailing a list of objects with the current state and the list of operations needed to do before being finished. ProductId CurrentWeight RemainingRoute 001 50 M1-M7-M5 002 48 …
kkrapic
  • 15
  • 3
1
vote
0 answers

Find root parent from child in oracle sql

I have to find the top parent in a hierarchy where I provide the child. the top most parent will be when the id = ref table example: +--------------------+ | id parent_id ref | +--------------------+ | 1 1 1 | | 2 1 1 …
kevin cutajar
  • 11
  • 1
1
vote
0 answers

oracle connect by prior using with the part of the composite key make malformed result?

SELECT xml_tag,TAG_ID,tag_id,parent_id,position_id,LEVEL //position_id for identify siblings FROM Intrastat_Xml_Template_TAB WHERE country_code = countrycode_ //country_code(varchar) and TAG_ID(number) makes the composite key START WITH…
Osura Weerasinghe
  • 73
  • 7
1
vote
2 answers

Oracle to select all matched occurrences by a regular expression to one column in one row with separator

I know to select all matched occurrences by a regular expression to one column in multiple rows as below: SELECT REGEXP_SUBSTR(str, '\+?\d{3,11}', 1, LEVEL) AS substr FROM ( SELECT 'Txa233141b Ta233141 Ta233142 Ta233147 Ta233148' AS str FROM…
mikezang
  • 2,291
  • 7
  • 32
  • 56
1 2 3
…
16 17