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

How to count group by a tree use ORACLE?

Right now I have a dataset like this; (8 records) cid pid 108 100 108 100 423 400 423 400 100 0 100 0 100 0 200 0 the tree like as follows: root -- 0 --child 100 --sub child 108 ---sub ...(maybe…
alardear
  • 3
  • 5
0
votes
0 answers

Create a sequence of Links

Imagine I have paths made out of one or more segments. Every path has an id and you can check if a segment is from a path using its id. Every segment has a begin point and an end point. The problem is that the segments are not ordered as…
fgallinari
  • 133
  • 8
0
votes
2 answers

How to get full children branches with given ids in connect by

I want to get branch from parent_id = null to childs with given child ids select * from company s start with s.parent_id is null and s.id = 56 connect by prior s.id = s.parent_id result is full branch with element id: 56 How to select branch with…
Adam Michalski
  • 1,722
  • 1
  • 17
  • 38
0
votes
3 answers

Oracle sorting connect by prior hierarchically

I have a table where there is a parent/child relationship defined, but there is no way to sort via hierarchical level. For example consider this: empid managerid name js hd George hd ab Mary hs js …
Abhi1988
  • 159
  • 2
  • 3
  • 14
0
votes
1 answer

Not sure how to write connect by clause

I have the following query to find employees who are not managers in the EMP table of oracle select * from emp e1 where not exists (select null from emp e2 where e2.mgr=e1.empno) I need output using start with connect by…
Lara
  • 49
  • 6
0
votes
1 answer

Oracle "start with" bottom to top recursion SQL Server equivalent

I have seen many posts that address how to convert an Oracle "connect by prior" statement to a SQL Server common table expression. However, I have an Oracle "connect by prior" statement that has a "start with fieldname in…
Jeremy
  • 175
  • 2
  • 10
0
votes
2 answers

Use of Prior in Hierarchical Query

I am trying to implement hierarchical query in my project. I have read most of the documents on internet but unable to get information related to use of PRIOR in it. SELECT EMPNO,ENAME,MGR,LEVEL FROM TMP_PCH CONNECT BY PRIOR EMPNO=MGR START WITH…
Pratsam
  • 162
  • 1
  • 12
0
votes
0 answers

is Recursive query supported in Greenplum

There is one query in oracle which is using CONNECT BY, my requirement is i need to migrate it to Greenplum Sql I found n tried with recursive class, but getting below error ********** Error ********** ERROR: RECURSIVE option in WITH clause is…
Gurupreet Singh Bhatia
  • 708
  • 6
  • 17
0
votes
1 answer

Oracle connect by with nested subqueries

I've 2 tables. 1st contains a reference to 2nd table, whereas 2nd table is self referencing. Below is the sample tables Table_P ----------------------------- P_ID P_TOKEN FID ----------------------------- 1 P1 F1 2 P2 …
masT
  • 804
  • 4
  • 14
  • 29
0
votes
1 answer

LEFT OUTER JOIN with CONNECT_BY_ROOT

I've got SELECT CONNECT_BY_ROOT TAXY.IDCATEGORY AS IDBRANCH , TAXY.IDCATEGORY , TAXY.IDPARENTCATEGORY , TAXY.IDCONTENT , TAXY.CATEGORYNAME , LEVEL AS LVL FROM TAXONOMY TAXY CONNECT BY TAXY.IDCATEGORY = PRIOR TAXY.IDPARENTCATEGORY and…
NotGaeL
  • 8,344
  • 5
  • 40
  • 70
0
votes
0 answers

Add where and and clause in connect with - ORACLE

I have a table X with columns a, seq_no, dept_no, status where column a contains values like "1,2,3,4,5,6". To change the row to column I have used the query as: SELECT DISTINCT TO_NUMBER(regexp_substr(a,'[^,]+',1,level)) numbers FROM x CONNECT…
user3195273
  • 193
  • 1
  • 5
  • 13
0
votes
2 answers

CONNECT BY NOCYCLE PRIOR 10G Optimiser Mode

question for today; if the RBO is enabled in 10.2.0.3 and one attempts to use a hierarchical approach; CONNECT BY PRIOR for example, does the optimiser get switched to CBO for execution? I have a large RBO 10GR2 (Don't ask!!), I know the stats are…
cbm64
  • 1,059
  • 2
  • 12
  • 24
0
votes
1 answer

Carry value from between rows oracle

I have a table like: ID MONTH VALUE 1 06/2014 3 1 07/2014 -2 1 08/2014 1 2 03/2014 1 2 04/2014 -1 (...) What I want is to create a new column which hierarchically sum the values, like: ID MONTH VALUE …
filippo
  • 5,583
  • 13
  • 50
  • 72
0
votes
1 answer

Hierarchial Query to return child rows SQL : Connect By

I have three table that look(just an example) as follows Artifact Id Name 1 abc 2 xyz 3 pqr 4 mno Classification Id Artifact_Id Node_id c1 1 n3 c2 1 n4 c3 3 n5 c4 4 n3 c5 4 …
krs8785
  • 1,097
  • 2
  • 14
  • 24
0
votes
1 answer

Can column storing number be expanded in Oracle

Can we expand a column storing number somehow in the output. I am trying to expand one column with other column having same value. column which is i am trying is number.. like if the value is 3 then result in select query should have 1,2,3 with…
Rahul
  • 95
  • 1
  • 3
  • 14
1 2 3
…
16 17