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?

[coeurdange]

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

J'essaie de faire un CREATE (je clique droit sur VIEWS et fait New View, puis je colle le texte dans SQL QUERY), mais ça ne marche pas. Quand je teste la query, c'est bon, mais si je clique sur Check Syntax, j'ai cette erreur.

[Sunchaser]

puis je colle le texte dans SQL QUERY)

Hum ... je ne connais pas l'outil que tu utilises, mais donc c'est ce texte / SQL que tu devrais poster ici, histoire que l'on voit ce que cela donne.

[coeurdange]

J'utilise l'outil SQL Developer pour gérer la bdd

[Sunchaser]

Salut,
Oki, mais ceci ne donne pas le code utilisé pour créer cette vue ...
A tout hasard, as-tu essayé le même code avec sqlplus directement?

@+