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
31 Jan 2015
Do you really need the result as a comma-delimited list or as multiple rows?
Dieter
03 Feb 2015
Thank you Rohan.
yes Dieter i need in rows.
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
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.