remove duplicate rows in Hierarchical Queries 1

[lovekang]

in the query below how to surpress last six rows(they are duplicate)?

drop table tbl_emp;

create table tbl_emp(
emp_no varchar2(20),
emp_name varchar2(20),
ref_emp_no varchar2(20)
);

insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('677','cho','');
insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('685','mun','');
insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('812','bai','');
insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('24','lee','');
insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('25','ki','493');
insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('26','kim','');
insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('27','cho','506');
insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('28','kang','430');
insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('29','kim','');
insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('30','park','');
insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('31','park','420');
insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('420','kim','812');
insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('430','kang','');
insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('493','mun','');
insert into tbl_emp(emp_no,emp_name,ref_emp_no) values('506','um','');

select level,emp_no,LPAD(' ', LEVEL * 3) ||emp_name,ref_emp_no from tbl_emp
connect by prior ref_emp_no = emp_no;

this gives
1 1 24 lee
2 1 25 ki 493
3 2 493 mun
4 1 26 kim
5 1 27 cho 506
6 2 506 um
7 1 28 kang 430
8 2 430 kang
9 1 29 kim
10 1 30 park
11 1 31 park 420
12 2 420 kim 812
13 3 812 bai
14 1 420 kim 812
15 2 812 bai
want to delete below rows
16 1 430 kang
17 1 493 mun
18 1 506 um
19 1 677 cho
20 1 685 mun
21 1 812 bai

Thanks.

 

[lovekang]

the below won't work either.
select level,emp_no,LPAD(' ', LEVEL * 3) ||emp_name,ref_emp_no from tbl_emp
start with emp_no not in (select ref_emp_no from tbl_emp)
connect by prior ref_emp_no = emp_no

 

[SantaMufasa]

in the query below how to surpress last six rows(they are duplicate)?

What do you consider "duplicate"? For example, in your output, above, you show:
[tt]...
12 2 420 kim 812
13 3 812 bai
14 1 420 kim 812
15 2 812 bai
...
[/tt]Aren't "kim" and "bai" duplicates?

Also, you list as duplicates:[tt]...
19 1 677 cho
20 1 685 mun
...
[/tt]Yet those rows are nowhere in the previous output.

I believe your output should, instead, look like this:

 LEVEL EMP_NO EMPLOYEES    REF_EMP_NO
------ ------ ------------ ----------
     1 24        lee
     1 25        ki        493
     2 493          mun
     1 26        kim
     1 27        cho       506
     2 506          um
     1 28        kang      430
     2 430          kang
     1 29        kim
     1 30        park
     1 31        park      420
     2 420          kim    812
     3 812             bai
     1 677       cho
     1 685       mun

15 rows selected.

Let us know your response.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Basically, my understanding of "remove duplicates" means, "If an EMP_NO has already appeared in the result set, don't let it appear again": ¿Sí/No?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[lovekang]

what I mean by "remove duplicates" is
if emp_no's are referenced somewhere else, they should not appear again.

so

24 lee
25 ki 493
493 mun
26 kim
27 cho 506
506 um
28 kang 430
430 kang
29 kim
30 park
31 park 420
420 kim 812
812 bai
420 kim 812 X
812 bai X
430 kang X
493 mun X
506 um X
677 cho
685 mun
812 bai X

rows marked X should not appear because the emp_no is referenced by some rows.

 

[SantaMufasa]

In other words, the rows you want are the ones I posted earlier, right? <grin>

My output, above, (which matches what you want) results from the slight modification to your original code:

col level format 99999
col emp_no format a6
col employees format a12
col ref_emp_no format a10
select level
      ,emp_no
      ,LPAD(' ', LEVEL * 3) ||emp_name Employees
      ,ref_emp_no
  from tbl_emp
 [B][I]where hier_check.str(emp_no) <> 'done already'[/I][/B]
connect by prior ref_emp_no = emp_no
/

 LEVEL EMP_NO EMPLOYEES    REF_EMP_NO
------ ------ ------------ ----------
     1 24        lee
     1 25        ki        493
     2 493          mun
     1 26        kim
     1 27        cho       506
     2 506          um
     1 28        kang      430
     2 430          kang
     1 29        kim
     1 30        park
     1 31        park      420
     2 420          kim    812
     3 812             bai
     1 677       cho
     1 685       mun

The "brains" of the above code ("where hier_check.str(emp_no) <> 'done already'") rely upon public-variable-value persistence that occurs in Oracle packages.

An functional narrative of the packaged function, "hier_check.str(emp_no)" is:

Check to see if the current emp_no appears in the (persistent) list of previously displayed emp_nos ("hold_ids", see package code, below.).[ul][li]If the current emp_no is not yet in the list, then[/li][ul][li]add the emp_no to the list, and[/li][li]RETURN the message, 'not done yet'[/li][/ul][li]If the current emp_no is already in the list, then[/li][ul][li]RETURN the message, 'done already'.[/li][/ul][/ul]

Here, then is the code that creates the package, "hier_check", along with the packaged function "str", which checks to see if the emp_no str has aleady appeared in the hierarchical output:

create or replace package hier_check is
    hold_ids      varchar2(4000) := '/';
    function str (id_in number) return varchar2;
    pragma restrict_references(str,WNDS);
end;
/       
create or replace package body hier_check is
    function str (id_in number) return varchar2 is
        begin
            if instr(hold_ids,'/'||id_in||'/') > 0 then
                return 'done already';
            else
                hold_ids := hold_ids||id_in||'/';
                return 'not done yet';
        end if;
    end str;
end;
/

Due to the persistence of the values in "hold_ids", if you choose to re-run in the same session your hierarchical SELECT, then you must "unpersist"/re-initialise "hold_ids" with this command prior to re-running the query:

exec dbms_session.reset_package

Let us know your thoughts about this method.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

LoveKang,

How did this issue resolve for you?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[lovekang]

yes it did.
thanks.
btw, any ideas only with sql not pl/sql?

 

[SantaMufasa]

LoveKang,

The problem is that while you are SELECTing (reading), one must also be tracking (writing) the fact that an emp_no has been written to the result set.

I am not aware of a SQL-only alternative that allows you to WRITE while you READ. That is why I used the "tricky" little method that I did.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[lovekang]

Thanks.