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

What is the difference between connect by level and connect by prior in oracle?

Plz any one explain me about "connect by level" and "connect by prior" in oracle with basic example.. qn 1) select to_date('&fromdate','DD-MM-YYYY') + level -1 from dual connect by…
Karthick
  • 1
  • 1
  • 1
0
votes
2 answers

Getting parent data if child data is null in Oracle hierarchical table

In Oracle 10g I have the following hierarchical table: corp_id, parent_corp_id, col1, col2, col3 I want to flatten out the structure such that we get the first row's data where col1 OR col2 OR col3 is not null. So for example, suppose I…
mlnyc
  • 2,636
  • 2
  • 24
  • 29
0
votes
0 answers

convert cte to connect by in oracle

I have below cte query which works in 11g but not in 10g. How can I write the same for 10g using connect by. not sure if its possible.. or I need to write the cursor/while loop to for the same. The sample data is here Recursive sql server query…
Ahmad Osama
  • 91
  • 1
  • 11
0
votes
1 answer

Need "connect by" type query with MySQL for pagination

I've got a submissions_comments table with has parents and children and each child has a 1:1 relationship and each parnet has a 1:many relationship. That is, all parents can have unlimited (1 level deep children) and all children can have only 1…
bob_cobb
  • 2,229
  • 11
  • 49
  • 109
0
votes
1 answer

General syntax error raised from CONNECT BY query in Informix when using quoted table identifiers

When running the following query on an Informix database, the database reports a general syntax error (without any indication with respect to what causes the problem). The same query runs perfectly on CUBRID or Oracle databases, both of which also…
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
0
votes
1 answer

Retrieve all children and parents of a given record

I want to retrieve all the parents of the given record and then get all the child records of those parent records. For example: if the Table is something like: table 1: Child_Id | Parent_Id --------------------- 23 4 23 …
Lolly
  • 34,250
  • 42
  • 115
  • 150
0
votes
0 answers

Recreating views using hierarchical retrieval where no hierarchy is apparent

As part of my SQL course (am a mere novice student) I have a database which is based around students and tests that they can take as part of a study course. So I have a table of students, a table of tests, a table of authors, a table of questions…
user3704497
  • 21
  • 2
0
votes
1 answer

How to translate "connect by" ORACLE to SQL Server

When using a RECURSIVE CTE in SQL Server, GROUP BY, LEFT OUTER JOIN and ORDER BY etc should not be used. How can I translate this ORACLE query to SQL Server? SELECT * FROM (SELECT COALESCE(a.tree_id, ' ') MNUID, …
user2427306
  • 39
  • 1
  • 7
0
votes
2 answers

oracle - Use connect by statement with CLOB parameter

My procedure CREATE OR REPLACE PROCEDURE my_procedure(res OUT SYS_REFCURSOR , p_LstKH CLOB) AS CURSOR c_dsKH IS SELECT TO_NUMBER(REGEXP_SUBSTR(p_LstKH,'[^,]+', 1, level)) value FROM dual CONNECT BY REGEXP_SUBSTR(p_LstKH, '[^,]+', 1, level)…
Leo Le
  • 815
  • 3
  • 13
  • 33
0
votes
2 answers

START WITH....CONNECT BY PRIOR

Does anybody know of a way to know the depth of the CONNECT BY? For example if I had; PARENT_ID PARENT_KEY CHILD_ID CHILD_KEY 1 BOB 2 CHRIS 1 BOB 3 TODD 2 CHRIS 4 …
cbm64
  • 1,059
  • 2
  • 12
  • 24
0
votes
1 answer

ORA-01436 when trying to export CONNECT BY PRIOR result

I have a table in Oracle that has two columns: CODIGO_DA_CONTA_PAI and CODIGO_DA_CONTA_FILHO (basically means Parent and Child). For the Parent CT-0000000000, the Children and the Children of Children, another column needs to be updated (but for now…
Lucas Rezende
  • 2,516
  • 8
  • 25
  • 34
0
votes
1 answer

Hierarchical import single query solution - Oracle

I have a interesting problem, at least I think so. So, I have a table (codes_table) with the above structure (it is a tree menu) the tree is defined by two integers plus a dot pattern (always) like this: COD_MENU NAME 01. …
Jorge Campos
  • 22,647
  • 7
  • 56
  • 87
0
votes
1 answer

A connect by query on all children

Is there any way to have the connect prior... start with statement in Oracle to start with the first "child" in the table and then run the query for all the remaining children as well? I know how to use the statement for 1 child and get its…
RebeccaK375
  • 871
  • 3
  • 17
  • 28
0
votes
3 answers

USING CONNECT BY clause in ORACLE

I have the following table: CREATE TABLE TASK_2 ( ROLE_NAME VARCHAR2(50 BYTE), MIN_CNT NUMBER, MAX_CNT NUMBER ) WITH THE FOLLOWING DATA: INSERT INTO TASK_2 VALUES ( 'SE', 3, 5); INSERT INTO TASK_2 VALUES ( 'SSE', 2, 6); INSERT INTO TASK_2…
Naveen_Raj_B
  • 3
  • 1
  • 3
0
votes
1 answer

Oracle - junction tables and connect to prior

I have problem that I think is only solvable with a Connect-to-prior statement. My data model is, simplified, as follows: create table TASK ( TASK_ID number ); create table ITEM ( TASK_ID number, NEXT_TASK_ID number ); create table…
phareim
  • 261
  • 5
  • 9
1 2 3
…
16
17