Re: Hierarchical queries Oracle to MySQL

[Rajarshi Biswas]

Hi guys !

Can any one help me in the question i posed on hierarchical queries , i am stuck in this ,

i tried to make temporary table and and tried with cursors .... it failed to give me the desired result . I am not a regular user of MySQL , so i have my conceptual limitations . Please suggest where i can get more info regarding this .

I tried this way


CREATE OR REPLACE TEMPORARY TABLE T_RECURSE AS
Select parent.id,parent.kind,parent.depth,parent.layer FROM tbl_parent parent WHERE parent.id='abc' and parent.layer='Business' ;

BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a,b,c,d VARCHAR(128);
DECLARE cur1 CURSOR FOR SELECT parent.id,parent.kind,parent.depth,parent.layer FROM T_RECURSE parent
UNION ALL
SELECT child.id,child.kind, child.depth,child.layer FROM T_RECURSE parent_v, tbl_parent child
WHERE child.parentid = parent_v.id and child.layer='Business';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO a, b,c,d;
IF NOT done THEN
INSERT INTO T_RECURSE VALUES (a,b,c,d);
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END ;