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
0 answers

CONNECT BY clause required in this query block

I am in the process of upgrading to Hibernate 5.3.4. This has caused some integration tests to fail. A couple of tables have columns names id. This is causing the CONNECT BY clause required in this query block error. The methods in question are…
ShaggyInjun
  • 2,880
  • 2
  • 31
  • 52
0
votes
1 answer

oracle CONNECT_BY_ROOT searching within a group

given is the following table KID | CROSS_LINK | KEY | GROUP_KEY --------|------------|--------|----------- COR001 | | 711768 | 336406 EXP001 | | 740194 | 336406 POD002 | DPR002 | 712408 | 336406…
serdar
  • 13
  • 4
0
votes
1 answer

Hierarchical Query connect by using start with clause

The data I am working with is sequenced like the below example: Example What I want to know is this: Want What I get from my query is this (the actual query with actual data is posted at the end of this post) : Get This is the code I have now, which…
thors123
  • 15
  • 1
0
votes
0 answers

How to find max level in each path in a Oracle hierarchical SQL?

I would like to know the way to find the max level number in Oracle hierarchical SQL within the given path. For example : If connect by clause starts with root 1 having below relation . parent_id node_id votes NULL 1 - 1 2 …
Guru
  • 31
  • 1
  • 5
0
votes
1 answer

Are 'connect by' or 'WITH RECURSIVE' even usable in a highly connected big data scenario?

Imagine the following BIG-DATA situation: There are 1 million persons stored in a SQL database. Each of them follows exactly 50 other persons. So there is a table like this (with 50 million entries): person1 | person2 0 | 1 0 | …
hardfork
  • 2,470
  • 1
  • 23
  • 43
0
votes
1 answer

Create rows between two dates

I have the below table. I need to create a row for each month from hire_dt to term_dt id hire_dt term_dt 1 08/07/2017 02/20/2018 Expected Results: id hire_dt term_dt Month level_alias 1 08/07/2017 …
John
  • 289
  • 3
  • 14
0
votes
1 answer

Oracle, Connect By rownum

I tried to find some informations about connect by "engine". I found this post: Confusion with Oracle CONNECT BY User krokodilko answered and says: The analyze of the last query: select level from dual connect by rownum<10; I leave to you as a…
q4za4
  • 630
  • 4
  • 12
0
votes
2 answers

how to filter history data based on most recent record?

Table: HISTORY CUSTOMER MONTH PLAN 1 1 A 1 2 A 1 2 B 1 3 B In this example customer 1 had plan A and changed to B on month 2. I need to remove the change from month 2 and…
filippo
  • 5,583
  • 13
  • 50
  • 72
0
votes
2 answers

Oracle Connect By vs Recursive User-Defined Function performance

I'm doing a basic performance check using both Connect By and a user-defined function to get a parent value. It seems that using a user-defined function performs better than the Connect By query. I would like to know if using the user-defined…
ads
  • 1,703
  • 2
  • 18
  • 35
0
votes
3 answers

Oracle sql query to get parent node based on type?

I need help in building a hierarchical query to get parent node based on type. For example: Table (org) pid|cid|type|name | 1|MGT |Ofc Pres 1| 2|DEP |HR 1| 3|DEP |IT 3| 4|DIV |Web 3| 5|DIV |Database 4| 6|SEC |HTML 4| 7|SEC…
ads
  • 1,703
  • 2
  • 18
  • 35
0
votes
0 answers

Moving Recursive queries from Oracle Sql to Redshift

I am trying to move this recursive query from Oracle Sql to Redshift. In order to so, I should be able to get rid of the commands "CONNECT BY", "LEVEL" and " PRIOR" within the code written below. I was wondering to move the recursive pattern within…
BubbleBeat
  • 13
  • 2
0
votes
2 answers

Output records that match the condition, as well as their parent records up to the root record. Oracle SQL

There is such an Oracle table: +----+-----+--------+ | ID | PID | NAME | +----+-----+--------+ | 1 | | testX | | 2 | | test2 | | 3 | 2 | test3 | | 4 | 3 | testX | | 5 | 3 | test5 | | 6 | 3 | test6 | | 7 | 4 | test7 …
archjkee
  • 41
  • 4
0
votes
1 answer

Oracle connect by clause

I have below data available in a table called say EXPENSE : Account DEPT EXPENSE_AMT ------------------------ 1001 DEPT_A 50000 1002 DEPT_B 100000 1003 DEPT_C 10000000 1004 DEPT_D 500000 1005 DEPT_E 1000 These departments are controlled by PARENT…
Guru
  • 31
  • 1
  • 5
0
votes
2 answers

Oracle SQL: CONNECT BY LEVEL returning many rows

I am having a first go at the CONNECT BY command, and I understand its potential to create loops with it. I was trying to create a query that generates a row for each timestamp between a starttime and an endtime, with a variable interval. When I run…
Ronald
  • 172
  • 2
  • 11
0
votes
1 answer

Query to retrieve manager before employee

We have list of resources with their managers (not the complete hierarchy). We now need to retrieve the resource list such that resource's manager row should always appear before the resource row. I already tried hierarchical query, however given…
Vishal
  • 198
  • 1
  • 3
  • 11
1 2 3
…
16 17