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
1
vote
2 answers
Oracle-SQL Single select, cascade by field
I'm not entirely sure how to phrase what I mean. Let me try: Is there a way to select all elements that cascade by a reference field?
For instance, I have the rows:
parentRef | Reference | Data
------------------------------
aContainer | mainObj…
David Crane
- 93
- 11
1
vote
3 answers
Force Oracle to use filtering in START WITH from outside of query
Table definition:
create table Tree
(node varchar2(20),
parentNode varchar2(20),
val number);
create index idx_tree_01 on Tree
(node );
create index idx_tree_02 on Tree
(parentnode);
Sample data:
Insert into TREE…
dcieslak
- 2,697
- 1
- 12
- 19
1
vote
3 answers
Given any child in the hierarchy, fetch complete tree by INFORMIX hierarchical SQL
I need a little help with Informix hierarchical sql query. I have table with the following structure :
create table empl_relation (
employee_id char(10),
manager_id char(10));
employee_id | manager_id
5148 null
…
user3244615
- 330
- 1
- 15
1
vote
0 answers
mysql i want to use connect by level oracle concept
SELECT COUNT(*) num
FROM (
SELECT REGEXP_SUBSTR(PWD, '[^,]+', 1, LEVEL) AS USERPWD
FROM (
SELECT PASSWORD || ',' || LPASS1 || ',' || LPASS2 || ',' ||
LPASS3 || ',' ||…
christopher alex
- 11
- 1
- 3
1
vote
1 answer
Is there a CONNECT BY PRIOR equivalent in XQuery?
I'm using BaseX, which supports XQuery 3.0.
Let's say I have an dataset like this, ordered on the value of :
1
2
2
4
…
wvdz
- 16,251
- 4
- 53
- 90
1
vote
1 answer
Oracle Hierarchical query "start with"-value in where clause
In the given Scenario we have several lots of ingrediances we mix to indermediate product lots. These intermediate product lots are used for further intermediate products or for finished product lots. There are an different amount of indermediate…
Cabation
- 11
- 4
1
vote
1 answer
JPA Entity with hierachy relationship
I have the following Entity
@Entity
public class Project implements Serializable {
@Id
private Integer project_id;
private String project_name;
other attributes
@OneToOne
@JoinColumn(name = "lead_emp_no",…
jeff
- 3,618
- 9
- 48
- 101
1
vote
1 answer
Oracle 'CONNECT BY' Syntax
This is an offshoot of the following question:
Single out duplicates between two result sets
As by a comment in that questions, I'm trying to implement my query using Oracle's special 'CONNECT BY' syntax. I'm having trouble finding any (clear)…
The_Denominater
- 985
- 4
- 9
- 20
1
vote
1 answer
Getting counts at each level of a hierarchical query
With Reference to one of the question answer before,
Getting counts/totals at each level of a hierarchical query using CONNECT BY
DIRS
====
DIR_ID
PARENT_DIR_ID
DIR_NAME
FILES
=====
FILE_ID
FILE_NAME
DIR_ID
FILE_SIZE
If DIRS…
Ammad
- 4,031
- 12
- 39
- 62
1
vote
1 answer
SQL: Recursion With a Join Confusion
I'll start by laying out what I'm trying to do and then I'll list the code I've made so far.
I'm coding in Oracle PL/SQL on the Application Express Platform
I have two tables: USERS and LEADS.
Leads Columns: LEADID, COMPANYNAME, CONTACTNAME,…
GuavaKhan
- 187
- 1
- 9
1
vote
1 answer
How to make pl/sql code to Ansi-Sql?
My question may be not challenging for sql expert. i want to rewrite my sql as a ansi-sql. How can i change below sql to ansi-sql in Oracle?
select *
from TEST r
start with r.childid=@CHILDID
connect by prior r.PARENTID=r.childid and…
Penguen
- 16,836
- 42
- 130
- 205
1
vote
1 answer
Indexes and time complexity for Oracle CONNECT BY query?
I have multiple hierarchical menus stored in a Oracle 11g table (called items), with the following structure:
menu: ID for the menu the item belongs to.
id: ID for the menu item. Unique within the menu, but not within the table.
name: Name of the…
Anders
- 8,307
- 9
- 56
- 88
1
vote
1 answer
Conversion Oracle SQL to PostgreSQL
I have a problem to convert a script from Oracle to Postgres:
WITH q AS (
SELECT 'ABCDE' str
FROM dual)
SELECT 'TEST' AS WL_ID
,substr(str, LEVEL, 1) AS SUPPLY_MODE_ID
FROM q connect BY LEVEL < length(q.str) + 1;
I tried this in…
Catalin Vladu
- 389
- 1
- 6
- 17
1
vote
1 answer
Oracle Sibling Structure
I have a structure that I store equal records in a database table. You can think that these records are siblings. For example I have two records in this table; 1=2 and 1=3. And I need a query that will return all siblings of a given record. Let me…
Mustafa Şimşek
- 13
- 2
1
vote
1 answer
Connect By in MYSQL
I have this select statement to display 20 records of time in Oracle
SELECT SYSDATE + (1 - LEVEL)/ 24 / 3600 * 600 ten_sec
FROM DUAL
CONNECT…
Shanna
- 753
- 4
- 14
- 34