listagg returning duplicate values

Question

I have this query:

with chave (data_utc, matricula) AS (
     select de.data_utc, de.matricula from dep_assop.dedalo de 
     group by de.data_utc, de.matricula

     union

     select oa.data_utc, oa.matricula from dep_assop.ocorrencias_aeronauticas oa 
     group by oa.data_utc, oa.matricula

     union

     select ec.data_utc, ec.matricula from dep_assop.eccairs ec 
     group by ec.data_utc, ec.matricula   
 )
select
    ch.data_utc,
    ch.matricula,
    oa.classificacao classificacao_anac,
    ec.classificacao classificacao_eccairs,
    de.classificacao classificacao_dedalo,
    listagg(oa.tipo, ', ') within group (order by oa.tipo) ADREP_ANAC,
    listagg(ec.tipo, ', ') within group (order by ec.tipo) ADREP_ECCAIRS,
    listagg(de.tipo, ', ') within group (order by de.tipo) ADREP_DEDALO
from 
    chave ch
    full join eccairs ec on ec.data_utc=ch.data_utc  and ec.matricula=ch.matricula 
    full join dedalo de on de.data_utc=ch.data_utc  and de.matricula=ch.matricula 
    full join ocorrencias_aeronauticas oa on oa.data_utc=ch.data_utc  and oa.matricula=ch.matricula
group by
    ch.data_utc, ch.matricula, oa.classificacao, ec.classificacao, de.classificacao

What should be returned:
28/10/10 | PPGOZ | Serious incident | null | Serious incident | LOC-G, RE | null | LOC-G, RE

What she returns from:

Why this? Since I am grouping everything from the union of the tables and then when I have my key table defined.

Answer 1

Without being able to test only the part of "Agg"

 select
    data_utc,
    matricula,
    classificacao_anac,
    classificacao_eccairs,
    classificacao_dedalo,
    listagg(ADREP_ANAC, ', ') within group (order by oa.tipo) LADREP_ANAC,
    listagg(ADREP_ECCAIRS, ', ') within group (order by ec.tipo) LADREP_ECCAIRS,
    listagg(ADREP_DEDALO, ', ') within group (order by de.tipo) LADREP_DEDALO
from 
    (select
        DISTINCT 
        ch.data_utc data_utc,
        ch.matricula matricula,
        oa.classificacao classificacao_anac,
        ec.classificacao classificacao_eccairs,
        de.classificacao classificacao_dedalo,
        oa.tipo ADREP_ANAC,
        ec.tipo ADREP_ECCAIRS,
        de.tipo ADREP_DEDALO
    from 
        chave ch
        full join eccairs ec on ec.data_utc=ch.data_utc  and ec.matricula=ch.matricula 
        full join dedalo de on de.data_utc=ch.data_utc  and de.matricula=ch.matricula 
        full join ocorrencias_aeronauticas oa on oa.data_utc=ch.data_utc  and oa.matricula=ch.matricula)
group by
    data_utc,
    matricula,
    classificacao_anac,
    classificacao_eccairs,
    classificacao_dedalo