Discussion :

[cathir]

Bonjour,

Malgré mes recherches, peut-être mal effectuées , je n'arrive pas à trouver de solution à mon problème que j'expose ici :

J'ai une table avec 2 colonnes, l'une avec un identifiant unique, ID, la seconde, ListePays, contient une liste de pays avec "un numéro d'ordre + nom pays + nombre" séparés par des virgules.

Ex :
ID ListePays
1 1FRANCE 55.25, 2ALLEMAGNE 30.50, 3BELGIQUE14.25
2 1URUGUAY 60.00, 2CHINE 40.00
3 1FRANCE 100
etc...

Je cherche à mettre à jour la colonne ListePays de manière à ne plus avoir le numéro d'ordre devant chacun des pays, mais il me faut laisser la virgule comme séparateur de ces pays.

Le résultat devant ressembler à ceci :
1 FRANCE 55.25, ALLEMAGNE 30.50, BELGIQUE14.25
2 URUGUAY 60.00, CHINE 40.00
3 FRANCE 100


Pouvez-vous m'aider ?

Merci beaucoup

[al1_24]

Quand une colonne contient une liste, c'est que le modèle de données a été mal conçu... et ça finit toujours par se payer.

[cathir]

Quand une colonne contient une liste, c'est que le modèle de données a été mal conçu... et ça finit toujours par se payer.

Merci pour votre réponse, mais cela ne m'aide pas vraiment.

Pour être vraiment précis, la table contenant cette liste a été créé via une requête récursive, permettant ainsi de disposer des informations dans une seule et même colonnes afin d'afficher ce résultat dans un rapport BO.
Seulement, la requête CTE renvoyait une liste trié par ordre alphabétique des pays et non par ordre du nombre suivant le pays. j'ai donc ajouté un numéro d'ordre avant le pays de manière à avoir une liste correctement triée.
Sauf que je ne veux pas afficher le numéro d'ordre, d'ou ma question.

[al1_24]

C'est donc dans la requête qui construit cette liste qu'il faut faire la mise en forme...

[cathir]

C'est donc dans la requête qui construit cette liste qu'il faut faire la mise en forme...

Oui, j'ai bien essayé cela, mais je dois surement mal m'y prendre car je n'y arrive pas.
Voici la requête initiale

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
WITH CTE (oeu_id, pays_list, liste_payscopro, length)
AS ( SELECT oeu_id, cast(' ' AS varchar(8000)), cast(' ' AS varchar(8000)), 0
	 FROM ( SELECT oeu_id, (cast(opa_ordre AS varchar(1)) + pay_libelle + ' ' + cast((cast(opa_pourcentage AS float))/100 AS varchar(6))) AS Payscopro
			FROM [OEUVRE_PAYS], [PAYS]
			WHERE [OEUVRE_PAYS].pay_id=[PAYS].pay_id) ListePaysCopro
	 GROUP BY oeu_id
	 UNION ALL
	 SELECT lcp.oeu_id, cast (pays_list +
			CASE WHEN length=0 THEN '' ELSE ', ' END + Payscopro AS varchar(8000) ),
			cast( Payscopro AS varchar(8000)), length +1
	FROM CTE c
	inner join ( SELECT oeu_id, (cast(opa_ordre AS varchar(1)) + pay_libelle + ' ' + cast((cast(opa_pourcentage AS float))/100 AS varchar(6))) AS Payscopro
				 FROM [OEUVRE_PAYS], [PAYS]
				 WHERE [OEUVRE_PAYS].pay_id=[PAYS].pay_id ) lcp
	ON c.oeu_id =  lcp.oeu_id
	WHERE lcp.Payscopro > c.liste_payscopro)
 
INSERT INTO OEUVRE_Lst_PAYS_COPRO
SELECT oeu_id, pays_list
FROM ( SELECT oeu_id, pays_list,
			  rank() OVER ( partition by oeu_id ORDER BY length DESC)
	   FROM CTE) D (oeu_id, pays_list, rank)
WHERE rank=1;

Le résultat me donne donc une liste de pays classées par ordre 'opa_ordre'.
Si j'enlève le champ "opa_ordre" de ma requête, celle-ci me donne une liste de pays classées par ordre alphabétique, ce qui ne convient pas puisque les pays doivent être classées par "opa_pourcentage" mais que cette dernière information doit se présenter après le pays.

J'espère que je m'explique correctement.

Si vous avez une idée pour résoudre ce problème, je suis preneur.

[Michel.Priori]

Première étape : homogénéiser l'écriture.

Si on utilise des CTE, on n'utilise plus les sous requêtes.
Si on utilise le mot clé JOIN on ne fait plus de restriction du produit cartésien

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
WITH 
 ListePaysCopro (oeu_id, Payscopro ) AS
 (SELECT oeu_id,
    , cast(opa_ordre AS varchar(1)) + pay_libelle + ' ' + cast((cast(opa_pourcentage AS float))/100 AS varchar(6))) AS Payscopro
  FROM [OEUVRE_PAYS]
	inner join [PAYS] on [OEUVRE_PAYS].pay_id=[PAYS].pay_id
 )
,CTE (oeu_id, pays_list, liste_payscopro, LENGTH) AS
  (SELECT oeu_id,
          cast(' ' AS varchar(8000)) AS pays_list,
          cast(' ' AS varchar(8000)) AS liste_payscopro,
          0 AS LENGTH
	   FROM
		  ListePaysCopro
	   GROUP BY oeu_id
   UNION ALL 
   SELECT lcp.oeu_id, --oeu_id
		CAST   (pays_list 
				+ CASE
					  WHEN LENGTH=0 THEN ''
					  ELSE ', '
				  END 
				+ Payscopro 
				AS varchar(8000)), 			-- pays_list
		cast(Payscopro AS varchar(8000)),	-- liste_payscopro
		LENGTH +1							-- LENGTH
	   FROM CTE 
		INNER JOIN ListePaysCopro lcp ON CTE.oeu_id = lcp.oeu_id
	   WHERE lcp.Payscopro > CTE.liste_payscopro
   )
,D (oeu_id, pays_list, rank) AS
(SELECT oeu_id,
          pays_list,
          rank() OVER (PARTITION BY oeu_id
                       ORDER BY LENGTH DESC)
   FROM CTE)
 
   --INSERT INTO OEUVRE_Lst_PAYS_COPRO
 
SELECT oeu_id,
       pays_list
FROM
   D 
WHERE rank=1;

Si je n'ai pas fait d'erreur de transcription, on obtient exactement les mêmes résultats qu'avant.
Vous validez ?


En imaginant que oui
A mon sens tout vient du fait que la CTE ListePaysCopro a amalgamé plusieurs colonnes pour former Payscopro.
Si les colonnes pouvaient rester unitaire jusqu'à la dernière requête on pourrait alors trier comme bon nous semble, la concaténation (si elle est toujours utile) serait faite en dernier.

Ce qui pose le problème de la CTE, ou du moins, la clause where de celle-ci.
La condition de sortie de la boucle est ... surprenante : comparer le rang de 2 chaines (lcp.Payscopro > c.liste_payscopro) alors que la colonne LENGTH (1- c'est pas bien d'utiliser des nom de fonction 2- ici il s'agit plutôt du rang, de la génération ou du numéro d'ordre) nous permet de faire la même chose !

In fine, de l'ensemble de la génération récursive on ne retient que la dernière ligne, c'est à dire celle qui a fini de concaténer tous les membres.

Et alors là on se demande pourquoi on n'utiliserait pas la fonction analytique : LISTAGG
https://docs.oracle.com/cd/E11882_01...htm#SQLRF30030

Pourquoi faire compliqué ?

[Michel.Priori]

Ce n'est donc pas une "table" mais le "résultat d'une requête".
Donc, tu nous ne demande pas de modifier la structure des données mais, plutôt, d'infléchir le résultat d'une exécution.

Ben voyons, al1_24, tu aurais dû comprendre

Et on peut la voir cette requête ?
Au passage, c'est bien ce que tu souhaites 'in fine'. Tu ne re-modifie pas le résultat ?