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

Oracle SQL - Generate rows based on quantity column

We use pooled positions that have a max headcount assigned and I need to build a report that creates a line for each head, including the details of the incumbent if there is one or a NULL line where there is a vacancy. Like…
Slowalesce
  • 13
  • 2
0
votes
1 answer

Hierarchical query with some joins

I am struggling to write a performing query which would consist of the data from one sub-select, and hierarchically retrieved data from another table based on the rows from that first sub-select. So, I have some data retrieved from multiple tables…
Xarrion
  • 47
  • 8
0
votes
2 answers

Oracle 12c Recursive query for Product , Category relationship

I have data like below. If i search Product "P1" then I need all the category which has the product "P1" I need all the products that are related to the category from step 1 Explanation: Product P1 avail in Categories in C1,C2,C4. So, I'll…
Victor
  • 23
  • 4
0
votes
1 answer

connect-by-prior: using PRIOR to drill 2-levels up

I have the following connect-by-prior SQL which essentially starts at the leaf node and works its way up the tree to the parent "tree-trunc" (level-1): with my_tree as ( select 'level 4.1' node, 'level 3.1' parent_node from dual union select…
cartbeforehorse
  • 3,045
  • 1
  • 34
  • 49
0
votes
2 answers

Display immediate parent after CONNECT_BY_ROOT in Oracle PL SQL

I am using oracle 12 DB and client. Need help on this one. I want to populate a column which displays the root, child, and immediate parent after the root? lets say "CONNECT BY 2nd ROOT"? Here is my code so far. SELECT PARENT, …
user1215811
  • 55
  • 1
  • 5
0
votes
0 answers

syntax error at or near "START" in Postgres despite the fact that EXTENSION tablefunc is created

I have a query like this(which works in Oracle properly): ... LEFT JOIN myTable ON .... START WITH orgNodeId IN ( SELECT someColumn FROM anothertable WHERE ... ) CONNECT…
gstackoverflow
  • 36,709
  • 117
  • 359
  • 710
0
votes
1 answer

Redirect postgres connectby into a table

I am using postgres 9.6 with the tablefunc expansion to generate a hierarchial table using the connectby function. My syntax is SELECT * FROM connectby('depTree2', 'id', 'parentid', 'sequence', '{4472}',0) AS t(keyid text, parent_keyid text,…
Keith Miller
  • 3
  • 1
0
votes
2 answers

SQL Recursive Queries , problems with understanding "prior" expression on connect by clause

I created a table like the following , to understand recursive queries: For this I made an insert, to cause a loop to the query and now my table looks like the following (Athens to Vienna connection has been added): Now to make an uncycled…
WalkingCoffeeMan
  • 109
  • 1
  • 7
0
votes
1 answer

Oracle SQL/PLSQL: Hierarchical recursive query

I have asked very similar questions before but still haven't achieved the correct result... third times the charm but I now have a stable parent-child relationship to work with. Every row in the multi-level data set has a cost. For a specific row I…
pwlm
  • 174
  • 2
  • 2
  • 19
0
votes
1 answer

Oracle hierarchial queries

I am trying to pull out all the hierarchy values for a particular segment in Oracle Apps(referring tables applsys.fnd_flex_Value_norm_hierarchy & applsys.fnd_flex_values). I want a tree like display starting from the top parent and going down until…
aleesha rajwanshi
  • 1
0
votes
0 answers

How do I connect a hierachical query by a substring of the prior record

So essentially I have hierarchical data saved to a table with the standard parent/ child relationship. I want to remove part of the child's name (for example the first letter) and use that to connect to the next level. Example: Each Parent has a…
ZakZ
  • 1
  • 1
0
votes
2 answers

Salary Accumulation by mgr (DB2 for Oracle CONNECT_BY)

We have data from scott.emp table: select empno, ename,mgr, sal from emp order by empno ; EMPNO|ENAME | MGR| SAL -----|----------|-----|---------- 7369|SMITH | 7902| 800 7499|ALLEN | 7698| 1600 7521|WARD | 7698|…
Durian Parongil
  • 19
  • 4
0
votes
3 answers

How to connect to both sides

I'm trying to get all the ascendants and the descendants of a List of IDs. It's like a directory-tree where the list of IDs are the directory with access rights including sub-directorys. To navigate to the folders I also need the ascendents. I…
Nico Richter
  • 227
  • 1
  • 9
0
votes
0 answers

"CONNECT BY LEVEL"

We have an application which basically takes a companies fiscal year end, and walks backwards to gather ways using CONNECT BY LEVEL and looks up other data. However, we found some companies that change their fiscal cycle causing the CONNECT BY LEVEL…
Landon Statis
  • 683
  • 2
  • 10
  • 25
0
votes
0 answers

How to add root second parent id to oracle table

I have a child-parent table like this (0 means that it is the top item): itemID | parentItemId --------------------- 1 | 0 2 | 0 3 | 0 4 | 1 5 | 1 6 | 2 …
Nir
  • 601
  • 7
  • 21
1 2 3
…
16 17