Oracle10 : concatener champs texte de plusieurs lignes

bonjour,

Soit les données suivantes:

Code:

---

1 2 3 4

blocNote ma_date affaire aa 01/02/2007 144000 bb 01/03/2007 144000 cc 19/11/2007 144000

---

Résultat attendu:

Code:

---

1 2	blocNote ma_date affaire aa bb cc 19/11/2007 144000

---

Je souhaite obtenir 1 seule ligne en prenant le max de la date et en concaténant tous mes BlocNotes (avec un espace entre chaque....).

Pour la date, je pense pouvoir utiliser :

Code:

---

max(MA_DATE) over(partition by affaire)

---

Mais comment faire pour concatener les valeurs de blocnote de chaque ligne ?


Si quelqu'un a une idée, ce serait cool !

Merci d'avance ! :lol:

A mon avis, il faut que passes par du PlSql et utilser un curseur

LBO72

ok...

domage que cela ne puisse pas se faire dans un select avec une fonction analytique...

Merci quand même. :roll:

Je reste ouverte à d'autres idées.

Salut,

Essaie ce code, mais il faut changer le nom de la table

Code:

---

1 2 3 4 5 6 7 8 9 10 11

SELECT SUBSTR (SYS_CONNECT_BY_PATH (blocnote, ' '), 2) name_list, ma_date FROM (SELECT blocnote, affaire, COUNT (*) OVER (PARTITION BY affaire) cnt, MAX (ma_date) OVER (PARTITION BY affaire) ma_date, ROW_NUMBER()OVER(PARTITION BY AFFAIRE ORDER BY blocnote) seq FROM note_tab WHERE blocnote IS NOT NULL) WHERE seq = cnt START WITH seq = 1 CONNECT BY PRIOR seq + 1 = seq AND PRIOR affaire = affaire;

---

Non ça ne marche pas

Code:

---

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

create table t_p ( blocNote varchar2(2), ma_date date, affaire Number(6) ) /   insert into t_p values('aa','01/02/2007',144000); insert into t_p values('bb','01/03/2007',144000); insert into t_p values('cc','19/11/2007',144000); insert into t_p values('cc','19/11/2007',144001); insert into t_p values('dd','19/11/2007',144001); insert into t_p values('ee','10/12/2007',144001); / SELECT SUBSTR (SYS_CONNECT_BY_PATH (blocnote, ' '), 2) name_list, ma_date FROM (SELECT blocnote, affaire, COUNT (*) OVER (PARTITION BY affaire) cnt, MAX (ma_date) OVER (PARTITION BY affaire) ma_date, ROWNUM seq FROM t_p WHERE blocnote IS NOT NULL) WHERE seq = cnt START WITH seq = 1 CONNECT BY PRIOR seq + 1 = seq AND PRIOR affaire = affaire /   NAME_LIST -----------------------------------------   MA_DATE -------- aa bb cc 19/11/07

---

Par contre cette solution marche si tu connais d'avance le max(blocnote) group by affaire. Sinon, des autres solutions sont envisageable (y compris celles similaire à la proposition de salim11, valable à partir d'Oracle 10 seulement)

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

Select affaire, max(ma_date), max(decode(seq,1,blocnote,null)), max(decode(seq,2,blocnote,null)), max(decode(seq,3,blocnote,null)) from ( select affaire, ma_date, blocnote, row_number() over (partition by affaire order by ma_date) seq from t_p) where seq <= 3 group by affaire / AFFAIRE MAX(MA_D MA MA MA ---------- -------- -- -- -- 144000 19/11/07 aa bb cc 144001 10/12/07 cc dd ee

---

Salut,

Merci pour ta remarque, mais j'ai corrigé ma requête même avant que tu post ton code ;).

Voici la démonstration que ça marche bien :mouarf:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SQL> SELECT affaire, SUBSTR (SYS_CONNECT_BY_PATH (blocnote, ' '), 2) name_list, ma_date 2 FROM 3 (SELECT blocnote, affaire, COUNT (*) OVER (PARTITION BY affaire) cnt, 4 MAX (ma_date) OVER (PARTITION BY affaire) ma_date, 5 ROW_NUMBER()OVER(PARTITION BY AFFAIRE ORDER BY blocnote) seq 6 FROM t_p 7 WHERE blocnote IS NOT NULL) 8 WHERE seq = cnt 9 START WITH seq = 1 10 CONNECT BY PRIOR seq + 1 = seq AND PRIOR affaire = affaire;   AFFAIRE NAME_LIST MA_DATE -------------- ---------------------------------------- ------------ 144000 aa bb cc 19/11/2007 144001 cc dd ee 10/12/2007   SQL>

---

avec des perfs probablement plus prometteuse en plus :king:

Citation:

---

Envoyé par salim11

Salut,

Merci pour ta remarque, mais j'ai corrigé ma requête même avant que tu post ton code ;).
...

---

Bonjour M Salim 11,

Si vous relisez ce que j’ai écrit vous pouvez noter que je n’ai ne pas douté de la faisabilité de votre solution :? J’ai simplement remarqué que le code posté ne donne pas le bon résultat et que votre solution est valable à partir d’Oracle 10 seulement.
Par contre pourriez-vous m’expliquer que est-ce que vous voulez dire par « j'ai corrigé ma requête même avant que tu post ton code » ?:oops:

Merci à tous !!

Je vois que les challenges techniques ont toujours des preneurs !
Heureusement qu'il y a des pointures comme vous !
Merci mille fois, car ce n'était absolument pas évident pour une débutante+ comme moi .
;)

Code:

---

1 2 3 4

create table t as ( select 'aa' blocnote,to_date('01/02/2007','DD/MM/YYYY') ma_date,44000 affaire from dual union all select 'bb',to_date('01/03/2007','DD/MM/YYYY'),44000 from dual union all select 'cc',to_date('19/11/2007','DD/MM/YYYY'),44000 from dual )

---

Code:

---

1 2 3 4 5 6

select wmsys.wm_concat(blocnote) blocnote, max(ma_date) ma_date, affaire from t group by affaire;

---

Code:

---

1 2 3

BLOCNOTE MA_DATE AFFAIRE -------- ------------------- ---------- aa,bb,cc 2007-11-19_00:00:00 44000

---

:lol: la requête qui calme tout le monde :king:

Bonjour,
c'est quoi :

Code:

---

wmsys.wm_concat()

---

c'est dispo depuis quelle version?

Trés trés fort Laurent .....
Toute mon admiration :king:

LBO72.

Citation:

---

Envoyé par isn44

Bonjour,
c'est quoi :

Code:

---

wmsys.wm_concat()

---

---

c'est une fonction d'aggrégat. Je l'ai découvert par hasard en cherchant :

Code:

---

select owner,object_name from dba_procedures where aggregate='YES';

---

Je ne peux pas dire dans quelle version c'est apparu, ce n'est pas documenté et les user-defined-aggregates (UDAG) sont apparues en 9iR1. Si quelqu'un a une 9iR1 pour confirmer, merci 8-)

Merci Laurent, cella je ne le connaissais pas.
Par contre il faut avoir installé Oracle Workspace Manager d'abord :)

Citation:

---

Envoyé par mnitu

Merci Laurent, cella je ne le connaissais pas.
Par contre il faut avoir installé Oracle Workspace Manager d'abord :)

---

c'est très juste! cependant, si tu utilises DBCA alors WM est toujours installé.

Salut,

Merci Laurent, Tu nous proposes toujours de solutions extraordinaires:king:.

J’apprends toujours de toi de nouvelles techniques.

Salim.

Citation:

---

Envoyé par laurentschneider

c'est très juste! cependant, si tu utilises DBCA alors WM est toujours installé.

---

Mon DBA n'utilise pas DBCA :)
Mais je voudrais vous demander si vous envisages d'utiliser votre solution dans un environnement de production ?

Citation:

---

Envoyé par mnitu

Mon DBA n'utilise pas DBCA :)
Mais je voudrais vous demander si vous envisages d'utiliser votre solution dans un environnement de production ?

---

bon tu peux installer wm manuellement avec @?/rdbms/admin/owminst.plb

j'imagine que la fonction est utilisable en prod, pour être sûr tu peux ouvrir un ticket sur metalink... mais ça a l'air de fonctionner. Tu peux aussi développer ta propre fonction comme asktom/stragg

Je ne pense pas que je vais installer la wm manuellement et je n'ai pas besoin d'ouvrir un ticket sur métalink. Désolé, mais c'est une fonction pour impressionner les âmes sensibles sur les forums :king: (big c'est all_objects)

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SQL> select count(*) 2 from big;   COUNT(*) ---------- 398632   SQL> select owner, wmsys.wm_concat(object_name) 2 from big 3 group by owner 4 ; ERROR: ORA-22813: la valeur de l'opÚrande dÚpasse les limites systÞme

---

Par titre de comparaison essayez (un exemple classique pour ce type de problème)

Code:

---

1 2 3

select owner, cursor(select object_name from big b where b.owner = a.owner) from (select distinct owner from big) a

---

Bien sur je suis prêt à changer d'avis si vous m'expliques comment contourner l'erreur ou quelles sont les limites de la fonction. Mais pour l'instant je viens d'oublier cette solution.:oops:

un desc de wmsys devrait permettre de voir les limites pour faire un traitement par lot ;)

Citation:

---

Envoyé par orafrance

un desc de wmsys devrait permettre de voir les limites pour faire un traitement par lot ;)

---

Vous voulez dire :mouarf:

Code:

---

1 2 3 4 5 6

SQL> desc wmsys.wm_concat FUNCTION wmsys.wm_concat RETURNS VARCHAR2 Nom d'argument Type E/S par dÚfaut ? ------------------------------ ----------------------- ------ -------- P1 VARCHAR2 IN

---

Bingo :king: mais je ne l'utiliserais pas.

faut quand même dire que concaténer 32000 caratères c'est pas super lisible, ce type de besoin doit surtout apparaitre pour quelques lignes à concaténer j'imagine... pas des centaines ;)

J'étais mauvais joueur :hola:

tu peux toujours employer XMLAGG pour retourner un CLOB

Code:

---

1 2 3 4

select xmlagg(xmlelement(str,object_name||',')).extract('/STR/text()').getclobval() from all_objects;

---

Pour ceux que cela intéresse, voici le code d'une fonction d'aggrégation permettant de concaténer n lignes (ce qui doit revenir à ce que fait wmsys.wm_concat() ) :
http://asktom.oracle.com/pls/asktom/...:2196162600402

Merci plaineR, Laurent. Je suis passé complètement à coté du stragg (je l’ai lu à travers sans faire l’effort de comprendre) :aie:

Voilà aussi ce que Tom Kyte dit à propos de wm_concat

Citation:

---

my suggestion is going to be consistent....

Never use undocumented stuff, it is subject to change, removal, broken-ness without recourse.

either

a) use stragg
b) write your own
c) use the connect by trick.

---

Citation:

---

Envoyé par mnitu

...use the connect by trick

---

"CONNECT BY TRICK" ? Mais c'est pas documenté cette syntaxe !! :mouarf:

Citation:

---

Envoyé par tom kyte

Never use undocumented stuff, it is subject to change, removal, broken-ness without recourse.

---

Je ne peux qu'approuver cette recommendation :roll:

Citation:

---

Envoyé par Pomalaix

"CONNECT BY TRICK" ? Mais c'est pas documenté cette syntaxe !!

---

oui, et stragg n'est pas tellement documenté non plus :aie:

bon, connect by prior + row_number me semble en ordre, c'est une astuce mais ça ne crée pas de LOOP, donc on peut l'employer.

A choisir, je trouve MODEL plus élégant que CONNECT BY. J'ai posté à l'instant un essai sur le site de Tom Kyte.
http://asktom.oracle.com/pls/asktom/...:2196162600402

Citation:

---

Envoyé par laurentschneider

CONNECT BY TRICK...oui, et stragg n'est pas tellement documenté non plus :aie:

---

Mff, moi ma remarque était une simple vanne : je voulais juste dire que je n'avais jamais vu le mot clé TRICK dans le diagramme syntaxique du CONNECT BY...

Laurent,
j'ai testé

Code:

---

1 2 3 4 5

SELECT xmlagg(xmlelement(str,object_name||',')).extract('/STR/text()').getclobval() FROM big_table

---

Et au bout d'une heure il à rendu son âme avec "Ora 04030 mémoire de traitement manquante lors ...".
Par contre

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

declare TYPE tb is table of varchar2(30); l_data tb; begin select object_name bulk collect into l_data from big_table; -- dbms_output.put_line(to_char(l_data.count)); end;   1052736

---

s'exécute en 3 seconde.

Je pense que stragg est documenté ici

Citation:

---

Envoyé par mnitu

Laurent,
j'ai testé

Code:

---

1 2 3 4 5

SELECT xmlagg(xmlelement(str,object_name||',')).extract('/STR/text()').getclobval() FROM big_table

---

Et au bout d'une heure il à rendu son âme avec "Ora 04030 mémoire de traitement manquante lors ...".

---

pas de chance :aie:

Citation:

---

Envoyé par mnitu

Par contre

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

declare TYPE tb is table of varchar2(30); l_data tb; begin select object_name bulk collect into l_data from big_table; -- dbms_output.put_line(to_char(l_data.count)); end;   1052736

---

s'exécute en 3 seconde.

---

quel rapport?

Citation:

---

Je pense que stragg est documenté

---

STRAGG et WM_CONCAT sont identiques. Les deux se basent sur UDAG. Si tu employe STRAGG, ce ne sera pas beaucoup plus "documenté" que WM_CONCAT. Par contre WM_CONCAT est wrappé, donc si ça ne marche pas comme prévu, c'est pas de chance, tant dit qu'avec STRAGG, tu peux toujours déboggué toi même pour trouver la bulle...

Bon, moi je vois un avantage dans WM_CONCAT, c'est qu'il n'y a pas besoin d'avoir de privilèges style CREATE TYPE et CREATE PROCEDURE pour l'employer 8-)

Citation:

---

Envoyé par Pomalaix

Mff, moi ma remarque était une simple vanne : je voulais juste dire que je n'avais jamais vu le mot clé TRICK dans le diagramme syntaxique du CONNECT BY...

---

:lol:

Citation:

---

Envoyé par laurentschneider

quel rapport?

---

Je pensai écrire une procédure pour récupérer dans un clob, j'ai laissé tomber :)

Citation:

---

Envoyé par laurentschneider

STRAGG et WM_CONCAT sont identiques.

---

D'accorde à 100% :king: J'ai fait un desc du wmsys.wm_concat_impl. C'est stragg sauf

Code:

---

1 2	CURR_STR VARCHAR2(32767)

---

Donc dans stragg on peut voir l'implémentation.

Citation:

---

Envoyé par laurentschneider

Bon, moi je vois un avantage dans WM_CONCAT, c'est qu'il n'y a pas besoin d'avoir de privilèges style CREATE TYPE et CREATE PROCEDURE pour l'employer 8-)

---

Argument non retenu à cause de

Code:

---

1 2	create or replace type wmsys.Wm_Concat_Impl wrapped

---

Je vois deux désavantages pour une utilisation en production

• nécessité d'installer/vérifier l'install du WM sur N site. Est-ce que WM est disponible dans toutes les éditions d'Oracle? Est-ce que ça se mérite de installer N objets pour une fonction ? Il me semble que le Wm est dédié plutôt aux développeurs.
• le fait quelle est non-documenté

Pour moi c'est non.

Citation:

---

Envoyé par mnitu

Pour moi c'est non.

---

je vois ça :mouarf: