Hierarchy Query using Recursive function

Database
Enthusiast

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.

5 REPLIES
Enthusiast

Re: Hierarchy Query using Recursive function

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

Senior Apprentice

Re: Hierarchy Query using Recursive function

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

Enthusiast

Re: Hierarchy Query using Recursive function

Thank you Rohan.

yes Dieter i need in rows.

Senior Apprentice

Re: Hierarchy Query using Recursive function

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.

Enthusiast

Re: Hierarchy Query using Recursive function

Thank You Dieter