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
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.