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
1 answer

Is there an easy way to apply REGEXP_LIKE to path coming from connect by prior query in Oracle?

I have a query including connect by prior like; SELECT SYS_CONNECT_BY_PATH(ENAME, '/') as path FROM EMP WHERE CONNECT_BY_ISLEAF=1 START WITH MGR IS NULL CONNECT BY PRIOR EMPNO=MGR; The result is following …
user6493966
  • 73
  • 2
  • 8
0
votes
2 answers

Hierarchical Connect By Pass Value to children

I have a hierarchical SQL Statement, which show me a hierarchical list of components of a product. For example: Part 1101400004 contains Part 1012444. And Part 1012444 contains B30048. For each component i have a Qty. Now my question is: is it…
weegli
  • 1
0
votes
1 answer

Separate columns using connect by

I have a table which has rows in a parent child relationship. The maximum parent-child relationship depth is 3. One parent would have only one child. Table definition - CREATE TABLE USERS { empid NUMBER, name VARCHAR2, department…
JHS
  • 7,761
  • 2
  • 29
  • 53
0
votes
1 answer

how can i show the parent of a data using connect by prior?

i have this this table where i have > parentitem | childitem ---table name ------------------------------- dinner | steak dinner | wine dinner | mashed potato dinner | coffee …
robin g
  • 61
  • 7
0
votes
1 answer

Select a row for each minute between Date Values in each row

I have a table that has records in 5 minute increments that looks like the following: UDSNSI TIMESTAMP -8134 7/20/2016 4:30:00 AM -8125 7/20/2016 4:35:00 AM -8098 7/20/2016 4:40:00 AM I need to select from in such a way that…
jrandomuser
  • 1,510
  • 19
  • 50
0
votes
0 answers

SQL query on hierarchical data

I am new to sql - trying to learn. I have a specific use case and need help with the same. I have 2 tables and i need to run a query to join those and get the data back. Table A Parent Item Child Item Relationship Type Table B Item Name Item ID Item…
Srini
  • 1
  • 1
0
votes
1 answer

Query to find the Columns without data from a table Dynamically

I wrote a Query to find the Columns without data from a table Dynamically., But its giving output only with rownum=1, which is also incorrect., WITH x AS (SELECT column_name FROM all_tab_cols WHERE OWNER='HR' AND table_name='EMPLOYEES' AND…
Vicky
  • 312
  • 2
  • 9
  • 19
0
votes
1 answer

Connect By Prior with previous values

I'm sorry, I tried to find answer, but I found not. I would to have too previous(!) ARNT as parentARTNR and POSNR as parentPOSNR in my query. I tried CONNECT_BY_ROOT, but this is only TOP ARTNR and POSNR. Than I tried SYS_CONNECT_BY_PATH(t1.ARTNR,…
user3811974
  • 11
  • 4
0
votes
3 answers

Oracle connect by to find child, ancestor pairs

I am trying to write a query to get all nodes with their ancestors. The database stores a tree (nodes and their children/parents). I know that connect by can give all ancestors, and when coupled with the start with clause you can get all ancestors…
McAngus
  • 1,826
  • 18
  • 34
0
votes
2 answers

Get path hierarhy using connect by prior vertical instead of horizontal in Oracle?

Hi I have an SQL statement using connect by prior. I want to get the path by specific where criteria. Following SQL works fine, but I don't want to get just a single row matching the criteria, I want also to get each parent record (vertically up the…
MUMBUÇOĞLU
  • 251
  • 1
  • 8
  • 24
0
votes
1 answer

How to find all child ids given a parent in Oracle 11g

I have a table of ids which have a parentid in a different table and this builds a folder structure in the application layer. I need to get a list of all of the ids in a specific "Root Folder" select count(id) from t1, t2 where t1.id=t2.id connect…
trueimage
  • 309
  • 2
  • 4
  • 14
0
votes
1 answer

Hierarchical Queries - Oracle

I have a table with many project id's and for each project ,I have hierarchical structure as below. My input is risk id..How can I get the top level parent for the input risk ID. IF I give 25 as Input I should get 23 as output.how can I achieve…
Sravan
  • 173
  • 1
  • 5
  • 13
0
votes
2 answers

How to get the list of parent and Child in a Hierarchy using Oracle SQL

I have two tables, structures of the same is as below: Table 1. Transactional data table trx id. 1 2 3 4 5..etc Table 2 table 2 has the parent child relationship as below . id subject_id (Child) object_id (Parent) 1 …
Vjai
  • 11
  • 1
  • 4
0
votes
1 answer

hierarchy query (connect by clause)

I'm trying to writte a query using connect by clause but I can't handle with that. Generally I have table: CREATE TABLE "TESTOWA" ( "ACCOUNT" VARCHAR2(20 BYTE), "PARENT" VARCHAR2(20 BYTE), "PAYMENT" VARCHAR2(20 BYTE) ); Insert into…
andreww
  • 223
  • 6
  • 16
0
votes
3 answers

Is there any alternate method for 'connect by ' in Oracle?

I am looking for an alternative to CONNECT BY: Select SUBSTR(str,1,LEVEL) OUTPUT FROM ( SELECT 'ORACLE' As str FROM DUAL ) CONNECT BY LEVEL<=LENGTH(str); Any suggestions?
MastanSky
  • 57
  • 1
  • 10
1 2 3
…
16 17