Topic
2 replies Latest Post - ‏2012-11-26T13:21:28Z by Bob@LearCorp
Bob@LearCorp
Bob@LearCorp
26 Posts
ACCEPTED ANSWER

Pinned topic Result Set Order when using a recursive CTE

‏2012-11-16T21:43:20Z |
I build this recursive CTE that does a Where Used on a given entity within a table of parents and children.
The first section looks up the table to find the highest-level entity.
The second section locates highest-level entity for use in the third section.
The third section calls-out the complete tree structure.
When I ran my test, I noticed the order of the values returned were different each time I ran the CTE.
Given that I always found the highest-level entity in section two, I expected section three to return the values in the same order.

My question to the group is why are these different?

The code and result sets are below

Thanks for your help!

Bob
**************************************************************************
CREATE TABLE TESTRECUR(
PRNT CHAR(3),
TYP CHAR(3),
CHLD CHAR(3));

INSERT INTO TESTRECUR VALUES ('C1', 'CRE', 'C1');
INSERT INTO TESTRECUR VALUES ('C1', 'CRE', 'P1');
INSERT INTO TESTRECUR VALUES ('P1', 'PRN', 'CH5');
INSERT INTO TESTRECUR VALUES ('C1', 'CRE', 'P2');
INSERT INTO TESTRECUR VALUES ('C1', 'CRE', 'P3');
INSERT INTO TESTRECUR VALUES ('P3', 'PRN', 'CH1');
INSERT INTO TESTRECUR VALUES ('P3', 'PRN', 'CH2');
INSERT INTO TESTRECUR VALUES ('CH2', 'CHD', 'CH3');
INSERT INTO TESTRECUR VALUES ('CH2', 'CHD', 'CH4');

INSERT INTO TESTRECUR VALUES ('C2', 'CRE', 'C2');
INSERT INTO TESTRECUR VALUES ('C2', 'CRE', 'P5');
INSERT INTO TESTRECUR VALUES ('P5', 'PRN', 'CH6');
INSERT INTO TESTRECUR VALUES ('P5', 'PRN', 'CH7');
/* START WITH THE ENTITY SELECTED AND LOOK UP TO FIND THE TOP-MOST LEVEL */
WITH LIST1 (PRNT, CHLD) AS
(SELECT PRNT, CHLD FROM TESTRECUR
WHERE CHLD = 'CH3' -- PLACE ENTITY TO START WITH HERE

UNION ALL

SELECT PREV.PRNT, PREV.CHLD
FROM TESTRECUR AS PREV, LIST1
WHERE LIST1.PRNT = PREV.CHLD AND
LIST1.PRNT LIST1.CHLD),

/* READ THE TOP-MOST LEVEL */
LIST2 AS (
SELECT PRNT
FROM LIST1
WHERE PRNT = CHLD),

/* READ THE TREE DOWN FOR ALL RECORD RELATED TO THE ENTITY SELECTED */
LIST3 (LEVEL, PRNT, CHLD) AS
(SELECT 1, T1.PRNT, T1.CHLD
FROM TESTRECUR T1, LIST2
WHERE T1.PRNT = LIST2.PRNT AND T1.PRNT T1.CHLD

UNION ALL

SELECT LIST3.LEVEL + 1, NEXT.PRNT, NEXT.CHLD
FROM TESTRECUR AS NEXT, LIST3
WHERE LIST3.CHLD = NEXT.PRNT )

SEARCH DEPTH FIRST BY PRNT, CHLD SET NEWORDER
CYCLE PRNT, CHLD
SET DUPLICATEERROR TO '*' DEFAULT ' '
SELECT LEVEL, PRNT, CHLD
FROM LIST3
ORDER BY NEWORDER;

**************************************************************************

Result Set 1 WHEN ENTITY SELECTED IS 'CH5'
LEVEL PRNT CHLD
1 C1 P1
2 P1 CH5
1 C1 P2
1 C1 P3
2 P3 CH1
2 P3 CH2
3 CH2 CH3
3 CH2 CH4

Result Set 2 WHEN ENTITY SELECTED IS 'P2'
LEVEL PRNT CHLD
1 C1 P3
2 P3 CH1
2 P3 CH2
3 CH2 CH3
3 CH2 CH4
1 C1 P2
1 C1 P1
2 P1 CH5

Result Set 3 WHEN ENTITY SELECTED IS 'CH3'
LEVEL PRNT CHLD
1 C1 P2
1 C1 P3
2 P3 CH2
3 CH2 CH3
3 CH2 CH4
2 P3 CH1
1 C1 P1
2 P1 CH5
Updated on 2012-11-26T13:21:28Z at 2012-11-26T13:21:28Z by Bob@LearCorp
  • PWConner
    PWConner
    42 Posts
    ACCEPTED ANSWER

    Re: Result Set Order when using a recursive CTE

    ‏2012-11-22T00:48:09Z  in response to Bob@LearCorp
    Bob,

    I ran your three test, but I get the same results each time.

    1 C1 P1
    2 P1 CH5
    1 C1 P2
    1 C1 P3
    2 P3 CH1
    2 P3 CH2
    3 CH2 CH3
    3 CH2 CH4
    • Bob@LearCorp
      Bob@LearCorp
      26 Posts
      ACCEPTED ANSWER

      Re: Result Set Order when using a recursive CTE

      ‏2012-11-26T13:21:28Z  in response to PWConner
      I believe this is related to the OS Level I initially created & ran this on. I developed it on v5r4 and got back the results shown. I ported it to v7r1 and it ran fine.