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

Oracle hierachial query to select only the root parents

I have a tree data and I am trying to select only the root parents. The data may be subset of a larger set so it is possible that parent may not be empty. I would like to top most level for each tree in the data set. with test_data as ( select '1'…
Sannu
  • 1,202
  • 4
  • 21
  • 32
2
votes
2 answers

Using Oracle to fetch last ten days of sales, even if day has none

I am trying to select sales data from the last 10 days, and include the date with a 0 where there were no sales. To do so, I am experimenting with using dual and connect by level, but I do not know why the dates do not match, and query is…
nycynik
  • 7,371
  • 8
  • 62
  • 87
2
votes
1 answer

Not getting full series multiplication (product) in CONNECT-BY query

I have a CONNECT BY query that I am not getting desired results from. The final sub-query and query are supposed to produce the product of multiplying a string of percentages together. It starts with a table of retained percentages (PCT) from year…
Marc
  • 778
  • 7
  • 18
2
votes
1 answer

PostgreSQL - migrate a query with 'start with' and 'connect by' in oracle

I have the following query in oracle. I want to convert it to PostgreSQL form. Could someone help me out in this, SELECT user_id, user_name, reports_to, position FROM pr_operators START WITH reports_to = 'dpercival' CONNECT BY PRIOR user_id =…
vinod kumar
  • 175
  • 2
  • 3
  • 6
2
votes
1 answer

Oracle to Derby - ConnectBy and Start With Equivalent in Derby

The following Query1 is fine with Oracle. I want the same functionality to be carried out in Derby. I got the Query2, as one of the suggestion to be followed here. But still that is throwing the Error1. Please somebody help me out to get rid of…
Venmathi
  • 29
  • 4
2
votes
0 answers

java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: start near line 1, column 84

I am getting an error while executing the query. The error is: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: start near line 1, column 84 [from com.services.om.entity.OrderLines where…
Varun Vikram Singh
  • 41
  • 5
2
votes
3 answers

Group by calculating the non-breaking periods from two (from and to) values

Which Oracle SQL-technique is possible to use for concatenate this rows: id | from | to -------------- a | 20 | 25 a | 26 | 30 a | 32 | 40 b | 1 | 5 b | 7 | 10 b | 11 | 20 for such result: a | 20 | 30 a | 32 | 40 b | …
jurijcz
  • 366
  • 1
  • 12
2
votes
2 answers

Connect By query with recurring hierarchical checks

I have written the following query and unfortunately it does not work. All I am trying to achieve is that the dates that I select with each row is the effective date of the customer relationship. The ci_per_per table contains the following…
MozenRath
  • 9,652
  • 13
  • 61
  • 104
2
votes
3 answers

Connect by clause to get the top of hierarchy

I am facing an issue while using the CONNECT BY clause in Oracle for finding hierarchical data. Let me give an example: A is my parent part which has child part B and B also has a child part C. When I am using the CONNECT BY clause I am able to get…
user2133005
  • 21
  • 1
  • 1
  • 3
2
votes
1 answer

Strange query behavior oracle 11g

I have a query as: SELECT ps_node_id,name FROM cz_ps_nodes WHERE cz_ps_nodes.ps_node_type=261 START WITH NAME = 'Bundle Rule Repository',cz_ps_nodes.devl_project_id = P_devl_project_id AND cz_ps_nodes.deleted_flag = 0 CONNECT BY PRIOR ps_node_id =…
Nitin Thind
  • 21
  • 3
2
votes
1 answer

How to achive particular hierarchical query in Oracle

I have a table that looks like (Oracle 11.2.0.2.0): ParentID ID AllowsEntry NULL A188 N A188 1881 Y NULL A189 N A189 1891 Y 1891 189A Y Business rules allow for non-leaf level elements…
tayknight
  • 27
  • 4
2
votes
1 answer

How to create a hierarchical query as a materilized view with refresh on commit in oracle

Could anyone please tell me if there is a possibility to create a hierarchical query in oracle 10g as a materialized view with REFRESH ON COMMIT? I tried using CONNECT_BY but this doesn't work with REFRESH ON COMMIT. Are there any other…
Christian Hager
  • 468
  • 6
  • 22
2
votes
2 answers

connect by prior oracle

I have a table that contains an hierarchic element Table : A B P1 - P2 P1 C1 P2 C2 P2 B is a foreign key coming from the same table The query is : SELECT level niveau, A from table parent, table child START WITH A IN (…
Potinos
  • 371
  • 3
  • 5
  • 15
2
votes
2 answers

Reference to parent columns in Oracle CONNECT BY hierarchical query

I have data that looks like this: KEY1 KEY2 KEY3 LKEY1 LKEY2 LKEY3 FLAG ====== ========= ====== ====== ========= ====== ===== 09/10 10000 A1234 09/10 AU000123 A1234 1 09/10 10000 A1234 09/10 AU000456 A1234 1 09/10 …
Xophmeister
  • 8,884
  • 4
  • 44
  • 87
1
vote
2 answers

How to convert connect by level in teradata

I was trying to convert the connect by level function of oracle to teradata. I have seen many examples over the net but this particular one is different, (SELECT CASE LEVEL WHEN 1 THEN 'MB' WHEN 2 THEN 'SB' ELSE…
user708477
  • 147
  • 2
  • 4
  • 14
1 2 3
…
16 17