All Forums General
Radhika.Jakkula 3 posts Joined 01/10
14 Mar 2013
With Recursive - how to filter duplicate records?

Hi there,
I am using With recursive to get the parent - child relationship between the records. In my data there is a max of 4 levels, but it is not necessary that every parent has a child. I want to start building the hierarchy from parent, can you let me know how to identify the last level. Below is the query i am using.
REPLACE RECURSIVE VIEW emp_hier1 (level, mgr_id, emp_id, staff_id, path) AS
(SELECT 1 as level, a.mgr_id, a.emp_id, a.staff_id, cast (trim(a.staff_id) as varchar(200)) as path
FROM
 employee a
WHERE a.mgr_id is null
UNION ALL
SELECT c.level+1, b.mgr_id, b.emp_id, b.staff_id, cast (trim(c.path) || '~+~' || trim(b.staff_id) as varchar(200)) as path
FROM
 employee b ,
 emp_hier1 c
where b.mgr_id = c.emp_id
AND c.level <= 4) ;
employee table looks like this.
 

emp_id

mgr_id

staff_id

1

?

101

2

1

102

3

2

103

4

2

104

7

3

107

10

1

110

11

?

111

21

11

211

31

21

311

 
 
After the below select query (this is to break a single row to multiple columns) , I am getting records as below. I dont need the records in bold. Please suggest an option.
 
 Select level, mgr_id, emp_id, 
, Case When Col1Len = -1 Then path Else Substring(path From 1 For Col1Len) End As Snr_Ops_Mngr_Staff_ID
, Case When Col1Len = -1 Then Null Else Substring(path From Col1Len + 4 For (Case When Col2Len = -2 THEN 99 Else Col2Len End)) End As Ops_Mngr_Staff_ID
, Case When Col2Len = -2 Then Null Else Substring(path From Col1Len + Col2Len + 7 For (Case When Col3Len = -2 Then 99 Else Col3Len End)) End As Line_Mngr_Staff_ID
, Case When Col3Len = -2 Then Null Else Substring(path From Col1Len + Col2Len + Col3Len + 10) End As Agent_Staff_ID
From
-- Get the start position for all substrings
(Select level, mgr_id, emp_id, staff_id, path
, Position('~+~' in cast (trim(path) || '~+~' || trim(emp_id) as varchar(200)))-1 As Col1Len
, Position('~+~' in Substring(cast (trim(path) || '~+~' || trim(emp_id) as varchar(200)) From Col1Len + 3))-2 As Col2Len
, Position('~+~' in Substring(cast (trim(path) || '~+~' || trim(emp_id) as varchar(200)) From Col1Len + Col2Len + 6 ))-2 As Col3Len
, Position('~+~' in Substring(cast (trim(path) || '~+~' || trim(emp_id) as varchar(200)) From Col1Len + Col2Len + Col3Len + 9 ))-2 As Col4Len from emp_hier1) As D1
 

101

?

?

?

101

102

?

?

101

102

103

?

101

102

104

?

101

102

103

107

101

110

 

?

111

?

?

?

111

211

 

?

111

211

311

?

 

KS42982 137 posts Joined 12/12
15 Mar 2013

I think to find out the last level, you first need to create rank based on mgr_id, emp_id and staff_id and put it in employee table/view. Let's call is 'RNK'. Once that is done, you can change your recursive view like below.
REPLACE RECURSIVE VIEW emp_hier1 (E_RNK, mgr_id, emp_id, staff_id, path) AS
(
SELECT RNK E_RNK, a.mgr_id, a.emp_id, a.staff_id,
CAST (TRIM(a.staff_id) AS VARCHAR(200)) AS path
FROM  employee a
WHERE a.mgr_id IS NULL
AND RNK = 1
UNION ALL
SELECT b.RNK E_RNK, b.mgr_id, b.emp_id, b.staff_id, CAST (TRIM(c.path) || '~+~' || TRIM(b.staff_id) AS VARCHAR(200)) AS path
FROM
 employee b ,
 emp_hier1 c
WHERE b.mgr_id = c.emp_id
AND c.E_RNK + 1 = b.RNK) ;

(I don't have access to data, so there might be some syntax error(s), but you can fix that.)
Now, if you SELECT E_RNK and Path, you will get the last level.
Regarding your second question, really don't get on what criteria you want to get rid of BOLD records. Please explain the condition and would try to help.

You must sign in to leave a comment.