ORA-01436: CONNECT by loop in user data

2009-09-09 11:41

SELECT r1.region_id,parent_id
From Cnl_region R1
WHERE r1.region_id =1 START with r1.region_id = 1
CONNECT by PRIOR r1.region_id = r1.parent_id

When you run the SQL statement above, it will report a ORA-01436 error, which is the result of a loop. I later found that the data region_id 1 parent_id 24684, and region_id 24684 parent_id 1, which generated a loop and then I changed the data to run normally.

Do not change the data the following SQL statement to let it not produce loops

SELECT r1.region_id,parent_id
From Cnl_region R1
WHERE r1.region_id =1 START with r1.region_id = 1
CONNECT by nocycle PRIOR r1.region_id = r1.parent_id

SQL Code
--start with ... connect by hierarchy query usage
--Building a table statement
CREATE TABLE Automobiles (
PART_ID Number (5)
Constraint pk_auto_part_id primary KEY,
PARENT_ID Number (5)
Constraint fk_auto_ppid_ references automobiles (part_id),
Part_cname varchar2 () NOT NULL,
Part_ename varchar2 () NOT NULL,
Mp_cost number (9,2),
Desribe VARCHAR2 (20)
);
--Inserting data
INSERT into automobiles VALUES (1, 1, ' Auto ', ' mobile ', 84321.99, ' Assembly ');
INSERT into automobiles VALUES (2, 1, ' bodywork ', ' bodywork ', 19892.99, ' manufacture ');
INSERT into automobiles VALUES (3, 1, ' Send Machine ', ' engine ', 42128, ' Purchase ');
INSERT into automobiles VALUES (4, 1, ' attachment ', ' attached ', 15212, ' Assembly ');
INSERT into automobiles VALUES (5, 2, ' bumper ', ' bumper ', 4812.95, ' Purchase ');
INSERT into automobiles values (6, 2, ' chassis ', ' chassis ', 12795.11, ' manufacture ');
INSERT into automobiles VALUES (7, 2, ' trunk ', ' Boot ', 812.11, ' manufacture ');
INSERT into automobiles VALUES (8, 6, ' wheel ', ' Wheel ', 2062.2, ' manufacture ');
INSERT into automobiles VALUES (9, 6, ' Fender ', ' mudguard ', 990, ' manufacture ');
INSERT into automobiles VALUES (10, 8, ' tires ', ' Tyre ', #, ' Purchase ');
INSERT into automobiles values (11, 3, ' Send Disk ', ' Bonnet ', 3212, ' manufacture ');
INSERT into automobiles VALUES (12, 3, ' pistons ', ' piston ', 1112.2, ' manufacture ');
INSERT into automobiles VALUES (13, 3, ' carburetor ', ' carburetter ', 712.29, ' manufacture ');
INSERT into automobiles VALUES (14, 4, ' transmission ', ' gearbox ', 5712.25, ' manufacture ');
INSERT into automobiles VALUES (15, 4, ' dashboard ', ' Dashboard ', 538.92, ' manufacture ');
INSERT into automobiles VALUES (16, 14, ' brake ', ' carburetter ', 712.29, ' manufacture ');
INSERT into automobiles values (17, 14, ' shift lever ', ' gearshift ', 2001, ' manufacture ');
INSERT into automobiles values (18, 17, ' propeller shaft ', ' Shaft ', 1101, ' manufacture ');
INSERT into automobiles values (19, 15, ' odometer ', ' milometer ', 350.28, ' Purchase ');
/
--Layered SQL scripting statement exercises
Select Level,part_id,parent_id,part_cname,part_ename,mp_cost,desribe
from automobiles
Start with part_id=1
Connect by Prior part_id=parent_id
Order by level;
--Indent display
Select level,
Lpad (', 2*level-1 ') | | part_cname| | ' '|| Part_ename as PartName
from automobiles
Start with part_id=1
Connect by Prior part_id=parent_id
Order by level;
The following error was reported when executing the SELECT Level statement: Ora-01436:connect by loop in user Data solution: Change the parent_id in the first data to NULL, otherwise the loop loops to find Parent_ The ID will not be found.

This article from Csdn Blog, reproduced please indicate the source: http://blog.csdn.net/weikaifenglove/archive/2009/05/21/4206979.aspx