A hierarchical query clause in Oracle.
CONNECT BY is a hierarchical query clause used in oracle. It specifies the relationship between the parent and child rows in the hierarchy.
Questions tagged connect-by should usually also be tagged oracle.
Questions tagged [connect-by]
252 questions
0
votes
2 answers
Identifying start and end of assignment oracle sql 10g
This is very similar to a question I have already asked (and had answered) here: Identifying start and end of period covered - oracle 10g sql
However, I've not been able to work out how to achieve what I need in this case. I have data that looks…
bawpie
- 467
- 4
- 12
- 27
0
votes
1 answer
Oracle Connect By query resultset randomly not in hierarchy
I have prepared few queries using startwith and connectby for fetching all items of a table with parent - childs relationship.
Till now, these queries were working perfectly fine. But now, i noticed the hierarchy returned was not the same. hierarchy…
Riddhi Shah
- 73
- 1
- 2
- 12
-1
votes
1 answer
I need to convert this query from Oracle to SQL Server
SELECT
ffl2.fee_record_code,
(SELECT max(fee_record_code)
FROM fees_list ffl3
START WITH ffl3.fee_record_code = Nvl(ffl2.fes_associated_record, ffl2.fee_record_code)
CONNECT BY PRIOR ffl3.fee_record_code =…
simran kukreja
- 3
- 1
-1
votes
1 answer
Identify a Stop Roll Up within a Hierarchy Based on Conditions
I was able to successfully write a query that returns a hierarchy, which would be 7 levels deep. I want to add a column that has a "stop" value for each row.
Please see the example here:…
wrestler
- 3
- 1
-1
votes
1 answer
How to reverse the order using connect by prior
I have data like below:
ID DATA S_LEVEL
1304819 M02-004 1
1304819 M01-003 2
1304819 M01-005 3
1304819 MT-M01-005 4
1304819 M01-004 5
1304819 M01-002 …
badkarma
- 31
- 1
- 7
-1
votes
1 answer
I want to know how to execute CONNECT BY REGEXP in Google Big query
I have following statement in oracle sql I want to run this in Google Big Query.
CONNECT BY REGEXP_SUBSTR(VALUE, '[^,]+', 1, LEVEL) IS NOT NULL)
How can I run above code in Big query?
Samihan Jawalkar
- 113
- 1
- 9
-1
votes
1 answer
Oracle SQL >> Hierarchical Query for parent, child and grandchild in same table
I have the data in my table like below structure,
Manager Id Employee id chartfield
SM1 MGR-1 12
SM2 MGR-1 12
MGR-1 LEAD-1 12
MGR-1 LEAD-2 12
MGR-1 LEAD-3 12
LEAD-1 …
googly
- 9
- 3
-1
votes
1 answer
Oracle - Calculating route value in graph
I'm working with Oracle 10.
I have data that describes the nodes and paths of a graph.
I'd like to build a query that describes all the routes between endpoints and their value or cost of traversal.
Example data :
NodeId1 NodeId2 Endpoint1 …
guilemay
- 39
- 3
-1
votes
1 answer
Oracle - Tree like query
there is a table: Groups
It has three columns: ID, NAME, PARENT
This would flow in the following way.
Suppose there is a Group ELECTRONICS
Under ELECTRONICS, there is MOBILE
Under MOBILES there is SAMSUNG
Under SAMSUNG there is GALAXY EDGE
Under…
Imran Hemani
- 599
- 3
- 12
- 27
-1
votes
1 answer
PL-SQL TREE MAX VALUE ROLLUP
Sorry if this question is already asked. please redirect me.
Sorry if it is on hold.
Just to clarify my question.
Here is my details.
I have 1 view named T1 and it has 3 columns named ID(NUM), PARENT_ID(NUM) and COLOR(NUM).
*ID column is the…
Seigfred Mondez
- 17
- 5
-2
votes
2 answers
Oracle sql recursive
I have the following table:
+----+-----------+------+
| ID | Parent_ID | Name |
+----+-----------+------+
| 1 | null | A |
| 2 | null | B |
| 3 | null | C |
| 4 | 1 | D |
| 5 | 4 | E |
| 6 | 2 …
-2
votes
1 answer
Recursive SQL and information on different level
Is it possible to display, in the same query, information concerning different level of recursivity ?
select LEVEL, ae2.CAB, ae2.NIVEAU, ae2.ENTITE, ae2.ENTITE_PARENT, ae2.libelle
from my_table ae2
where ae2.NIVEAU = 2
start with ae2.cab =…
mlwacosmos
- 4,391
- 16
- 66
- 114