All Forums Database
srikanthpusa 6 posts Joined 10/14
28 Jan 2015
Hierarchy Query using Recursive function

Hi gurus, am new to Teradata 
my query is about  hierarchy, table given below i got the following information from teradata links
EMP_ID  MGR_ID  EMP_NAME
28000      ?            Mark
28001    28000     Steve
28002    28000     Anna
28003    28001     Lorena
28004    28001     Simon
28005    28003     Patrix
28006    28005    Zoya
28007    28002    Peter
28008    28002   Paul
 
Mark is the Root for all nodes in the table. 
Mark has two childs Steve and Anna
Steve has two childs Lorena and simon
Lorena has one child Patrix
Patrix has one child Zoyz
Anna has two childs Paula and Peter 
My query is to find all the child nodes where child should not be the parent of any node in the table, 
like if my (i)input is "Mark" result should be "zoya,simon,paul,peter".
              (ii)Input is "Anna" result should be "Paul,Peter".
Thanks in adavance.

Rohan_Sawant 55 posts Joined 07/14
30 Jan 2015

Hi srikanthpusa,
 
The following queries must get you the required output:
 

/* Creating test data */
CREATE MULTISET VOLATILE TABLE VT_INPUT_DATA, NO FALLBACK , NO LOG,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
	EMP_ID INTEGER,
	MGR_ID INTEGER,
	EMP_NAME VARCHAR(10)
)
PRIMARY INDEX (EMP_ID,MGR_ID)
ON COMMIT PRESERVE ROWS;

INSERT INTO VT_INPUT_DATA VALUES (28000,NULL,'Mark');
INSERT INTO VT_INPUT_DATA VALUES (28001,28000,'Steve');
INSERT INTO VT_INPUT_DATA VALUES (28002,28000,'Anna');
INSERT INTO VT_INPUT_DATA VALUES (28003,28001,'Lorena');
INSERT INTO VT_INPUT_DATA VALUES (28004,28001,'Simon');
INSERT INTO VT_INPUT_DATA VALUES (28005,28003,'Patrix');
INSERT INTO VT_INPUT_DATA VALUES (28006,28005,'Zoya');
INSERT INTO VT_INPUT_DATA VALUES (28007,28002,'Peter');
INSERT INTO VT_INPUT_DATA VALUES (28008,28002,'Paul');
/* Completed creating test  */

/* Preparing parent and last child relation */
CREATE MULTISET VOLATILE TABLE VT_PARENT_CHILD_SEPARATE, NO FALLBACK , NO LOG,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
AS
(
	WITH RECURSIVE REC_EMP_CHILD
	( 
	  EMP_ID
	, MGR_ID
	, EMP_NAME
	, EMP_CHILD
	)  
	AS
	(
	  SELECT
	    A.EMP_ID
	  , A.MGR_ID
	  , A.EMP_NAME
	  , A.EMP_NAME AS EMP_CHILD
	  FROM
	    VT_INPUT_DATA A
	  WHERE
	    NOT EXISTS
	    (
	      SELECT *
	      FROM VT_INPUT_DATA
	      WHERE
	        A.EMP_ID = MGR_ID
	    )
	  UNION ALL
	   SELECT
	    A.EMP_ID
	  , A.MGR_ID
	  , A.EMP_NAME
	  , B.EMP_CHILD AS EMP_CHILD
	  FROM
	    VT_INPUT_DATA A
	  JOIN
	    REC_EMP_CHILD B
	  ON A.EMP_ID = B.MGR_ID
	)
	SELECT
	  EMP_NAME
	, EMP_CHILD
	, ROW_NUMBER() OVER (PARTITION BY EMP_NAME ORDER BY EMP_CHILD DESC) RN
	FROM
	  REC_EMP_CHILD
)
WITH DATA
PRIMARY INDEX (EMP_NAME)
ON COMMIT PRESERVE ROWS;	  

/* Combing childs and here is your output */
WITH RECURSIVE REC_PARENT_CHILD_COMBINE
( 
  EMP_NAME
, EMP_CHILD
, RN
)  
AS
(
  SELECT
	EMP_NAME
  , EMP_CHILD
  , RN
  FROM
    VT_PARENT_CHILD_SEPARATE
  WHERE
    RN = 1
  UNION ALL
  SELECT
	A.EMP_NAME
  , A.EMP_CHILD || ',' || B.EMP_CHILD AS EMP_CHILD
  , B.RN
  FROM
    REC_PARENT_CHILD_COMBINE A
  JOIN
    VT_PARENT_CHILD_SEPARATE B
  ON A.EMP_NAME = B.EMP_NAME
  AND A.RN + 1 = B.RN
)
SELECT 
DISTINCT
  EMP_NAME
, EMP_CHILD
FROM
  REC_PARENT_CHILD_COMBINE
QUALIFY MAX(RN) OVER (PARTITION BY EMP_NAME ORDER BY EMP_CHILD DESC) = RN;

 
Thanks,
Rohan Sawant

dnoeth 4628 posts Joined 11/04
31 Jan 2015

Do you really need the result as a comma-delimited list or as multiple rows?

Dieter

srikanthpusa 6 posts Joined 10/14
03 Feb 2015

Thank you Rohan.
yes Dieter i need in rows.

dnoeth 4628 posts Joined 11/04
04 Feb 2015

If this is always for a single root you might use this top-down query:

    WITH RECURSIVE REC_EMP_CHILD
    ( 
      EMP_ID
    , EMP_NAME
    , lvl 
    )  
    AS
    (
      SELECT
        A.EMP_ID
      , A.EMP_NAME
      , 1
      FROM
        VT_INPUT_DATA A
      WHERE A.EMP_NAME = 'Mark'
      UNION ALL
       SELECT
        A.EMP_ID
      , A.EMP_NAME
      , B.lvl + 1
      FROM
        VT_INPUT_DATA A
      JOIN
        REC_EMP_CHILD B
      ON B.EMP_ID = A.MGR_ID
    )
    SELECT *
    FROM REC_EMP_CHILD AS t1
    WHERE NOT EXISTS
    (
      SELECT *
      FROM VT_INPUT_DATA t2
      WHERE
        t1.EMP_ID = t2.MGR_ID
    )

You should put it in a macro and then pass 'Mark' as parameter.
 
If this should return all leave nodes you should start with the NOT EXISTS and the go bottom-up.

Dieter

srikanthpusa 6 posts Joined 10/14
25 Feb 2015

Thank You Dieter

You must sign in to leave a comment.