With Recursive - how to filter duplicate records?

General

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 ?
1 REPLY
Enthusiast

Re: With Recursive - how to filter duplicate records?

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.