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
1 answer
How to improve processing time for queries that use CONNECT BY and LEVEL clause
I was wondering if there's a way to re-write the following oracle SQL script to get a better processing time. This current version is very slow and wouldn't work the larger dataset that I'm working with.
I'm trying to assign a number to each…
Saad
- 5
- 2
0
votes
1 answer
Oracle Hierarchical queries: Translate START WITH ... CONNECT BY PRIOR into 'Recursive Subquery Factoring'
How would the following START WITH / CONNECT BY hierarchical query look like when translated into a RECURSIVE SUBQUERY FACTORING hierarchical query with WITH clause:
SELECT t1.id
FROM table1 t1, table2 t2
WHERE t1.version_id =…
Spanky
- 111
- 9
0
votes
1 answer
Oracle SQL Connect By Level - literal does not match format string
I want to generate dates between 2 dates coming from parameters in oracle fusion using the connect by level statement
SELECT papf.person_number emp_id,
(SELECT to_date(:p_from_date,'dd-mm-yyyy') + level - 1 dt
from dual
connect by level <= (
…
Aasem Shoshari
- 37
- 1
- 7
0
votes
0 answers
postgres connect by query in typeorm
how can we do connect by in Postgres type orm
async viewChain(userId: string) {
let id="0c06f58e-0b5f-43a9-a5b4-746e24a5915c"
let data=await this.userModel.query("WITH RECURSIVE cte AS (SELECT name, 1 AS level FROM users UNION ALL SELECT…
RJ amal
- 5
- 2
0
votes
1 answer
Get Ultimate manager oracle
I have data in the below format.
Emp to_location from_location Vehicle
---------------------------------------------
1 A B Road
1 B C Ship
1 C D …
U12
- 83
- 3
- 11
0
votes
1 answer
Is there a method to ORDER BY managers DESCENDING in an Oracle CONNECT BY query?
In this scenerio...
CREATE TABLE emp
(
empno NUMBER(4) NOT NULL,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
sal NUMBER(7,2),
deptno NUMBER(2)
);
INSERT INTO EMP VALUES(7782, 'CLARK', 'MANAGER', 7839,…
EvilTeach
- 28,120
- 21
- 85
- 141
0
votes
1 answer
Expanding Oracle rows with comma-delimited values into multiple rows
I have a table in Oracle like the following:
KEY,VALS
k1,"a,b"
I need it to look like:
KEY,VAL
k1,a
k1,b
I did this with CONNECT BY and LEVEL, following an example:
with t as (
select 'k1' as key, 'a,b' as vals
from dual
)
select key,…
Tianxiang Xiong
- 3,887
- 9
- 44
- 63
0
votes
0 answers
Parameterising a START WITH / CONNECT BY query
I have a genealogy dataset e.g.
Parent: Batch A
Child: Batch B
Grandchild: Batch C & Batch D
Great grand children: Batch E, Batch F and Batch G
etc.
All nodes have associated variables (e.g. material no, quantity, description, etc.)
From this, which…
Neilduggan
- 1
- 1
0
votes
1 answer
SQL Connect By Level sometimes works, sometimes doesn't can't understand why
I am trying to run the query in Oracle, and if I change the round to 0, I get a result, but anytime there are decimals I am not getting a result back when using the connect by level part. But if I run I my query from after n.n= I get the…
user1660680
- 97
- 2
- 11
0
votes
1 answer
OracleSQL: generate a horizontal view from father-son table
I have a table with this structure:
ELEMENT
FATHER_ELEMENT
INITIAL_DATE
END_DATE
I can have this situation:
| ELEMENT| FATHER_ELEMENT|
|--------|---------------|
| A | B |
| A | C |
| B | D |
| D …
antoninus96
- 77
- 12
0
votes
1 answer
Use connect by by in REGEXP_SUBSTR without breaking result to multiple rows
SELECT CHR(91)||'a-zA-Z0-9._%-'||CHR(93)||'+'|| listagg(REGEXP_SUBSTR('aaa@yahoo.com, bbb@hotmail.com', '@'||CHR(91)||'^,'||CHR(93)||'+', 1, LEVEL), ', ') within group (order by level) as domain
FROM DUAL
CONNECT BY REGEXP_SUBSTR('aaa@yahoo.com,…
user2102665
- 429
- 2
- 11
- 26
0
votes
0 answers
Create materialized view with hierarchical query and ANSI join
I am unable to create a materialized view and getting a ORA-904 error. This looks like the Bug 17551261 , but this should already be fixed. I tested it on Oracle 12.2, 19 and 21.
In the view i use a connect by statement and ANSI join.
When i rewrite…
christris
- 31
- 3
0
votes
1 answer
how to write connect by instr prior sys guid() on hive
im in the process on transferiing data from oracle to hive with thiveinput talend.
My code looks like this.
SELECT
DISTINCT A.ID,
LEVEL SEQUENCE,
REGEXP_SUBSTR(A.ANEST,'[^|]+', 1, LEVEL),
DATE
FROM
( SELECT A.*
FROM tableaa A,
tablebb B
…
thecardcaptor
- 131
- 7
0
votes
1 answer
Oracle Recursive Query Connect By Loop in data
I have a table that looks essentially like this (the first row pk1=1 is the parent row)
pk1
event_id
parent_event_id
1
123
123
2
456
123
3
789
456
Given any particular row in the above table, I need a query that returns all the…
adveach
- 55
- 1
- 8
0
votes
1 answer
hierarchical query without side rows
I have "id-parent_id related" data, like this:
1
/ \
/ \
2 4
/
/
3
I have the code, that returns data for all rows related to particular (related to condition in the start with clause) tree - in both sides ("up" and…
archjkeee
- 13
- 4