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
4
votes
3 answers

Connect by query

I'm storing hierarchical data in a table. When a resource is accessed by its hierarchical path (grantParent/parent/resource), I need to locate the resource using a CONNECT BY query. Note: SQL commands are exported from EnterpriseDB, but it should…
Karthik Murugan
  • 1,429
  • 3
  • 17
  • 28
4
votes
3 answers

Mixing together Connect by, inner join and sum with Oracle

I need help with a oracle query. Here is my setup: I have 2 tables called respectively "tasks" and "timesheets". The "tasks" table is a recursive one, that way each task can have multiple subtasks. Each timesheet is associated with a task (not…
François
  • 987
  • 1
  • 8
  • 13
4
votes
2 answers

Hierarchical Queries "START WITH" with where clause behavior

I came across a query during work and could not figure out how exactly it works. What the query does is look for all the parents to a person that are its parent today. Now the trick here is that each parent child relationship has a duration for…
MozenRath
  • 9,652
  • 13
  • 61
  • 104
3
votes
1 answer

Fast way to generate concatenated strings in Oracle

Don't we hate when evil coding comes back to haunt? Some time ago I needed to generate a string concatenating some fields for some more processing later. I thought it would be a good idea to do if straight in the query, and used SO's help to get it.…
filippo
  • 5,583
  • 13
  • 50
  • 72
3
votes
1 answer

Why does deterministic function return unexpected numbers in CONNECT BY LEVEL query?

Test #1: I have a user-defined function and a CONNECT BY LEVEL query: with function custom_function(p_id in number) return number is begin return p_id; end; select custom_function(level) from dual connect by level <= 1000 ID -- …
User1974
  • 276
  • 1
  • 17
  • 63
3
votes
1 answer

Oracle : Hierarchical Query Connect By

I wrote a Oracle Hierarchical query which will give us Top managers of Particular Employee. For Example If we have sample Emp and Manager mapping like : WITH emp_manager_mapping AS ( select 'A' empId, 'B' managerId,sysdate-100 appliedOn,'Success'…
Viral
  • 189
  • 1
  • 14
3
votes
1 answer

Oracle, Regexp, string separated via semicolons

I have this Query: SELECT ROWNUM AS num_row, REGEXP_SUBSTR(REPLACE('param1;param2;param3;;param5;;param7;;;param10;param11;param12;param13;param14;param15;', ';;', '; ;'), …
q4za4
  • 630
  • 4
  • 12
3
votes
1 answer

Oracle CONNECT BY behaviour

I thought I understand how Oracle's CONNECT BY works. But then I found this. select dual.*, CONNECT_BY_ISCYCLE from dual connect by nocycle 1=1 and LEVEL <= 2 ; results in DUMMY CONNECT_BY_ISCYCLE X 0 X 0 but select dual.*,…
Tymur Gubayev
  • 468
  • 4
  • 14
3
votes
2 answers

Recursive query in Oracle

I am kind of new to the more advanced topics of PLSQL, so hopefully someone can help me out. The problem: I have a table with messages sent between an admin and users. The table has a message_parent with FK to the same table message_id field: in…
taurijuhkam
  • 187
  • 1
  • 8
3
votes
2 answers

Is there a way to detect a cycle in Hierarchical Queries in SQL Server?

In Oracle, we can use the function CONNECT_BY_ISCYCLE to detect a cycle in Hierarchical Queries. I try to do the same in SQL Server. Is there a way to do this? Thanks a lot!
amau96
  • 857
  • 1
  • 8
  • 19
3
votes
1 answer

Tree structure in sql in Oracle.How to show tree,child nodes and parent nodes in SQL Oracle

I would like to show a tree structure in SQL with child nodes and parent nodes. I have a table like: Employee ------------- ID (int) FirstName (varchar) LastName (varchar) ParentID (int) Job (varchar) which represents an employee. ParentID…
Senior .Net Dev3loper
  • 111
  • 1
  • 1
  • 9
3
votes
2 answers

PRIOR in SELECT list

I can't understand what it adds to the result of the query. From the book that I'm learning: If you prefix a column name with PRIOR in the select list (SELECT PRIOR EMPLOYEE_ID, ...), you specify the “prior” row’s value. SELECT PRIOR…
ashur
  • 4,177
  • 14
  • 53
  • 85
3
votes
1 answer

Can I create a custom expression without using the where clause?

While I have already solved this issue in a previous question using a native query. I am now wondering if it is possible to create a custom expression that is usable in a Criteria without using the where clause? The reason I don't want the where…
Mike
  • 4,257
  • 3
  • 33
  • 47
3
votes
3 answers

Repeat rows dynamically in Oracle based on a condition

Heres what i have Query with temp as ( select 11 as x from dual union select 222 as x from dual ) select x from temp t,(SELECT 1 FROM DUAL CONNECT BY ROWNUM <= 3) Output X -- 11 222 11 222 11 222 Output i desire…
Sudhakar
  • 4,823
  • 2
  • 35
  • 42
3
votes
2 answers

Oracle Connect By top/bottom hierarchy

How can I get the top and bottom of a hierarchy using connect by, I have a table that stores transition between ids(ID-> REPLACE_ID), I'm interested in getting the latest id starting from any id. --drop table test_connect_by; create table…
Rodriguez
  • 452
  • 1
  • 5
  • 16
1
2
3
…
16 17