0

Here I need the tree structure query, with one int and other is varchar field. I don't want to modify varchar to int.

Table structure:

ID  INT
SUB_TASK_ID INT
TASK_ID INT
PID INT
WBS_ID  VARCHAR
WBS_TYPE VARCHAR

Here I'm trying to cast the wbs_id

SELECT * 
from art_sub_task_allocation 
START WITH sub_task_id IN (929) 
CONNECT BY PRIOR  CAST(wbs_id AS Numeric(10,0)) = sub_task_id;

But I get error :CONNECT BY loop in user data

APC
  • 144,005
  • 19
  • 170
  • 281
ankita lembhe
  • 161
  • 1
  • 11
  • 1
    What is your question? Are you getting an error, or wrong results? Please edit you question to show sample data, expected results, and what you get now. – Alex Poole Feb 05 '18 at 11:25
  • What is the error/issue you are facing? Also show sample data. – Kaushik Nayak Feb 05 '18 at 11:26
  • Error :CONNECT BY loop in user data – ankita lembhe Feb 05 '18 at 11:27
  • My wbs_id is varchar so, tree structure query is not working, so I'm trying to cast wbs_id, it's not working with, SELECT * from art_sub_task_allocation START WITH sub_task_id IN (929) CONNECT BY PRIOR wbs_id = sub_task_id; due to varchar – ankita lembhe Feb 05 '18 at 11:30
  • WHY do you not want to modify varchar to int? You think CONNECT BY loop has something to do with it? If the issue is the CONNECT BY loop, then ask a question about it, not about something else. CONNECT BY loop has absolutely nothing to do with casting varchar to int. Then: Use varchar2, not varchar, in Oracle. Use TO_NUMBER instead of CAST; and use NUMBER(10,0) instead of NUMERIC(10,0). And most importantly, if WBS_ID should be int, declare it so in the table creation! None of these, though, have anything to do with CONNECT BY loop. –  Feb 05 '18 at 13:47
  • 1
    The error message is telling you that your so-called "tree structure" is not a tree. There is a cycle in your table: there are rows with the following values for sub_task_id and wbs_id: (1, 2), (2, 3), (3, 1) (maybe not with the numbers 1, 2, 3 but with different numbers, and maybe a cycle longer than three steps). You could use NOCYCLE as suggested in an Answer; but if this is unexpected, it should be analyzed and if in fact it's in error, the error should be fixed first. Otherwise it's garbage in, garbage out. –  Feb 05 '18 at 13:53

2 Answers2

2

I think you should use NOCYCLE.

SELECT *
      FROM art_sub_task_allocation
START WITH sub_task_id IN (929)
CONNECT BY NOCYCLE PRIOR CAST (wbs_id AS NUMERIC (10, 0)) = sub_task_id;

You may also use TO_NUMBER instead of CAST.

PRIOR TO_NUMBER (wbs_id) = sub_task_id
Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
  • Also note that you can use `CONNECT_BY_ISCYCLE` as a column to see where the loop is in your data. http://www.dba-oracle.com/t_advanced_sql_connect_by_loop.htm – kfinity Feb 05 '18 at 18:07
0

When I see a table with columns called SUB_TASK_ID and TASK_ID I immediately think "compound key". That is I would presume that SUB_TASK_ID is only unique with TASK_ID. If that is the case you need to include TASK_ID in the CONNECT BY clause.

SELECT * 
from art_sub_task_allocation 
START WITH sub_task_id IN (929) 
CONNECT BY PRIOR  CAST(wbs_id AS Numeric(10,0)) = sub_task_id
      AND PRIOR TASK_ID = TASK_ID;
APC
  • 144,005
  • 19
  • 170
  • 281