Discussion :

[coeurdange]

Pourriez-vous m'aider concernant l'élaboration d'une requête SQL sur une base de données ?

Le contexte est le suivant: mon système gère des contrats liés à des activités (1 contrat peut être lié à 1 ou plusieurs activités). Chaque activité est lié à un "domaine"

Voici la structure des tables de ma base de données:

Table CONTRAT

Id_CONTRAT	Titre_CONTRAT
1	Contrat 1
2	Contrat 2
3	Contrat 3
4	Contrat 4

Table DOMAINE

Id_DOMAINE	Titre_DOMAINE
2	Domaine 2
3	Domaine 3
5	Domaine 5
8	Domaine 8

Table ACTIVITE

Id_ACTIVITE	Titre_ACTIVITE	Id_DOMAINE
118	Activite 118	2
266	Activite 266	2
269	Activite 269	3
272	Activite 272	3
275	Activite 275	5
283	Activite 283	8
301	Activite 301	8

Table de liaison LIEN_CONTRAT_ACTIVITE

Id_CONTRAT	Id_ACTIVITE
1	118
2	266
2	269
3	272
4	275
4	283
4	301

J'ai créé cette vue: VUE_CONTRAT_ACTIVITE_DOMAINE en effectuant la requête:

Code :

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

1
2
3
4
5
SELECT c.Id_CONTRAT,
a.Id_ACTIVITE,
a.Id_DOMAINE
FROM LIEN_CONTRAT_ACTIVITE c, activite a
WHERE c.Id_CONTRAT = a.Id_CONTRAT

Id_CONTRAT	Id_ACTIVITE	Id_DOMAINE
1	118	2
2	266	2
2	269	3
3	272	3
4	275	5
4	283	8
4	301	8

J'ai effectué cette requête afin d'obtenir tous les domaines obtenus par contrat:

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 Id_CONTRAT,
    SUBSTR(SYS_CONNECT_BY_PATH(Id_DOMAINE,';'),2) Id_DOMAINE
  FROM
    (SELECT Id_DOMAINE,
      Id_CONTRAT,
      COUNT(*) OVER ( partition BY Id_CONTRAT ) im,
      ROW_NUMBER () OVER ( partition BY Id_CONTRAT order by Id_DOMAINE) seq
    FROM VUE_CONTRAT_ACTIVITE_DOMAINE
    WHERE Id_CONTRAT IS NOT NULL
    )
  WHERE seq               =im
    START WITH seq        =1
    CONNECT BY prior seq+1=seq
  AND prior Id_CONTRAT         =Id_CONTRAT

Titre_CONTRAT	Id_DOMAINE
Contrat 1	2
Contrat 2	2;3
Contrat 3	3
Contrat 4	2;8;8

Cependant je souhaiterais pour la dernière ligne que le système affiche 2;8 et non pas 2;8;8 c'est-à-dire qu'il n'affiche q'une fois le domaine même si celui-ci est le même pour plusieurs activités.

Pourriez-vous me dire comment faire?

Merci d'avance pour votre aide.

Cordialement

[Baldrick]

Bonjour,

J'ai l'impression que vous vous compliquez la vie en voulant utiliser la fonction sys_connect_by_path et une requête hiérarchique.

Est-ce que la fonction LISTAGG ne pourrait pas vous être plus utile ?

Code :

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

1
2
3
4
SELECT id_contrat, LISTAGG(id_domaine, ';') WITHIN GROUP (ORDER BY id_domaine) AS id_domaine
FROM VUE_CONTRAT_ACTIVITE_DOMAINE
WHERE Id_CONTRAT IS NOT NULL
GROUP BY id_contrat

[coeurdange]

Bonjour, Merci pour la réponse.

Effectivement le résultat est identique. Merci beaucoup pour cette simplification.

Par contre sauriez-vous comment résoudre mon problème initial (ne pas afficher les doublons s'ils existent) ?

[Sunchaser]

Bonjour,
A vue de nez, je dirais qqchose comme:

Code :

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

1
2
3
4
5
6
7
8
 
SELECT id_contrat, LISTAGG(id_domaine, ';') WITHIN GROUP (ORDER BY id_domaine) AS id_domaine
FROM
   (SELECT id_contrat, id_domaine 
     FROM VUE_CONTRAT_ACTIVITE_DOMAINE
      WHERE Id_CONTRAT IS NOT NULL
    GROUP BY id_contrat, id_domaine)
GROUP BY id_contrat

@+

[coeurdange]

Merci beaucoup, cela fonctionne. Par contre j'essaie de créer un vue sur base de cette query mais cela ne fonctionne pas, cela me marque:

Code :

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

1
2
3
Error(s) parsing SQL:
Unexpected token near *!* in the following:
SELECT id_contrat, LISTAGG(id_domaine, ';') WITHIN *!* GROUP (ORDER BY id_domaine) AS id_domaine

Pourriez-vous me dire pourquoi et m'aider à résoudre le problème

Merci d'avance

[Sunchaser]

Salut,
Quel est le statement exact utilisé pour créer la vue?