Discussion :

[coeurdange]

Bonjour,

J'ai une base de données qui contient une table d'historisation des contacts où il y a plusieurs lignes d'historisation de contacts pour un même identifiant de contrat.
Je souhaiterais dans une vue récupérer les données de plusieurs lignes de contact communes avec le même identifiant de contrat:



CONTACT_HIST_IM_ID est l'identifiant du contrat

J'ai effectué une query qui permet de récupérer tous les noms associés à un contrat mais les noms sont en double et j'aimerais avoir les noms qu'une seule fois:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
  SELECT im_id,
    SUBSTR(SYS_CONNECT_BY_PATH(contact_name, ', '),2) contact_name
  FROM
    (SELECT DISTINCT(CONTACT_NAME),
      CONTACT_HIST_IM_ID as im_id,
      COUNT(*) OVER ( partition BY CONTACT_HIST_IM_ID ) im,
      ROW_NUMBER () OVER ( partition BY CONTACT_HIST_IM_ID order by contact_name) seq
    FROM FIDES_CONTACT_HIST_V
    WHERE CONTACT_HIST_IM_ID IS NOT NULL
    )
  WHERE seq               =im
    START WITH seq        =1
    CONNECT BY prior seq+1=seq
  AND prior im_id         =im_id;

IM_ID

CONTACT_NAME

39

Constantin-Alin, Constantin-Alin, Constantin-Alin, Constantin-Alin, Constantin-Alin, Constantin-Alin, Constantin-Alin, Constantin-Alin, Constantin-Alin, Constantin-Alin, Georges, Georges, Georges, Georges, Georges, Georges, Georges, Georges, Georges, Georges, Maurizio, Maurizio, Maurizio, Maurizio, Maurizio, Maurizio, Maurizio, Maurizio, Maurizio, Maurizio, Michel, Michel, Michel, Michel, Michel, Michel, Michel, Michel, Michel, Michel, Mohammad Reza, Mohammad Reza, Mohammad Reza, Mohammad Reza, Mohammad Reza, Mohammad Reza, Mohammad Reza, Mohammad Reza, Mohammad Reza, Mohammad Reza, Pal, Pal, Pal, Pal, Pal, Pal, Pal, Pal, Pal, Pal

Pourriez-vous m'aider à éviter les doublons car je ne sais pas comment faire même avec un DISTINCT ?

[McM]

Avec LISTAGG (suivant ta version de BDD)

https://oracle-base.com/articles/12c...ncements-12cr2

[vanagreg]

Bonjour,

Pourquoi utilises-tu un connect by? Y-a-t-il une notion de hiérarchie dans cette table? Si non, tu as juste un faire un select distinct sur les colonnes.

[coeurdange]

J'avais utilisé cela dans le passé pensant que c'était la meilleure solution à l'époque pour mes besoins.
Par contre maintenant c'est vrai que ce n'est plus la meilleure solution.
Par contre il faut quand même que je fusionne tous les contacts dans un même champs

[coeurdange]

Avec LISTAGG (suivant ta version de BDD)

https://oracle-base.com/articles/12c...ncements-12cr2

J'ai essayé cela:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT CONTACT_HIST_IM_ID as im_id, LISTAGG(CONTACT_NAME, ',') WITHIN GROUP (ORDER BY CONTACT_NAME) AS CONTACT_NAME
FROM   FIDES_CONTACT_HIST_V
GROUP BY CONTACT_HIST_IM_ID
ORDER BY CONTACT_HIST_IM_ID;

Mais le problème est toujours identique: j'ai toujours les doublons.

[McM]

Tu as suivi le lien Listagg distinct ?
paramètre DISTINCT en 19c, sinon, ils donnent des exemples pour enlever les doublons (avec un sous-requête faisant un distinct, puis listagg dessus)

si pas en 19c, alors ceci va fonctionner

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
select im_id, LISTAGG(CONTACT_NAME, ',') WITHIN GROUP (ORDER BY CONTACT_NAME) AS CONTACT_NAME
FROM (
SELECT DISTINCT CONTACT_HIST_IM_ID AS im_id, CONTACT_NAME FROM FIDES_CONTACT_HIST_V)
GROUP BY im_id
ORDER BY im_id

[coeurdange]

Merci beaucoup

[JeitEmgie]

Tu as suivi le lien Listagg distinct ?
paramètre DISTINCT en 19c, sinon, ils donnent des exemples pour enlever les doublons (avec un sous-requête faisant un distinct, puis listagg dessus)

Il y plusieurs choses dont il faut être conscient avec la technique du sous-query utilisant DISTINCT :
- cela peut devenir coûteux sur de gros volumes et pousser à la création d'un index composé (ici sur CONTACT_HIST_IM_ID et CONTACT_NAME) s'il n'existe déjà,
alors que souvent il existe déjà des index simples sur les champs concernés,
- cela marche tant qu'on a besoin que d'un seul LISTAGG dans la requête originale.

Une autre technique à connaître est celle qui consiste à profiter du fait que LISTAGG ne retourne pas les NULL,
donc au lieu de faire le LISTAGG sur la colonne originale est de faire sur un calcul CASE qui retourne NULL si la valeur est égale à la suivante sur la partition par
la clé triée par la colonne que l'on veut collecter:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
case

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
CONTACT_NAME WHEN LEAD(CONTACT_NAME,1) OVER(PARTITION BY CONTACT_HIST_IM_ID ORDER BY CONTACT_NAME) THEN NULL ELSE CONTACT_NAME END

Cela nécessite aussi une sous-requête car on ne peut avoir de fonction de fenêtrage à l'intérieur même du LISTAGG,
mais l'avantage est que cela fonctionnera quelque soit le nombre de LISTAGG dont vous avez besoin dans le résultat final et souvent cela ré-utilise des index qui existent déjà,
et donc il est aussi probable dans ce cas que cela retourne les premiers résultats plus rapidement (sur de gros volumes).