Requête avec une concaténation dynamique

Version imprimable

19/04/2012, 13h22
Rymaya

Requête avec une concaténation dynamique
Bonjour,

Je veux construire un champs composé en utilisant la concaténation voila un exemple
Code:

1 2 SELECT CODE , (SELECT COL1, COL2 WHERE i=1)||(SELECT COL1, COL2 WHERE i=2)||(SELECT COL1, COL2 WHERE i=3) as condition FROM MA_TABLE
la concaténation dépend de la valeur max de i , je m'explique si i=5 alors j'ai 5 concaténation .

Dans l'exemple max(i)=3 alors j'ai fais trois concaténations.

Je veux faire ça dynamiquement : avoir la valeur maximale de i et de suite
faire la concaténation.

Merci d'avance
19/04/2012, 14h02
Scriuiw

Le SQL Dynamique est ton ami !
19/04/2012, 14h30
Waldar

Avez-vous quelques données pour illustrer votre besoin ?

Votre question est confuse. Vous cherchez quelque chose comme l’exemple ou autre chose?

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
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> Select empno, case deptno
  2                  When 10 Then ename
  3                  When 20 Then ename||job
  4                  Else ename||job||to_char(sal)
  5                end
  6    from emp
  7  /
 
EMPNO CASEDEPTNOWHEN10THENENAMEWHEN2
----- -----------------------------------------------------------
 7369 SMITHCLERK
 7499 ALLENSALESMAN1600
 7521 WARDSALESMAN1250
 7566 JONESMANAGER
 7654 MARTINSALESMAN1250
 7698 BLAKEMANAGER2850
 7782 CLARK
 7788 SCOTTANALYST
 7839 KING
 7844 TURNERSALESMAN1500
 7876 ADAMSCLERK
 7900 JAMESCLERK950
 7902 FORDANALYST
 7934 MILLER
 
14 rows selected
 
SQL>

19/04/2012, 15h31
Rymaya

Merci pour vos réponses , peut être que j'ai pas bien posé ma question:

Ma requête dépend d'un champs i ce champs s'incrémente chaque fois ou il y a une nouvelle ligne, (si i=3 alors j'ai trois lignes), mon besoin est de concaténer ces informations en se basant sur le i .

d’après le lien présenté par Scriuiw

j'ai eu une idée

Je calcule le max(i) et par la suite j'utilise while (while max(i)>0) {fais les concaténations}

Que ce que vous en pensez?
19/04/2012, 18h01
McM

Avec un exemple on comprendra mieux.. c'est encore flou pour moi.
19/04/2012, 18h51
Rams7s

Vous ne voulez pas faire un pivot par hasard?

D'accord , voilà un exemple :
Code:

1 2 3 4 CODE VALEUR CLE INDEX OPE MIN MAX LIB 1 20 PTS 1 Apartir de 20 39 achat 1 30 PTS 2 Apartir de 40 49 achat
L'explication : L'avantage qui possède un code=1 à trois conditions: si l'achat entre 20 et 39 le client pourra avoir 20pts bonus , si l'achat entre 40 et 49 le client pourra avoir 30 pts
Mon but est de construire une dimension Avantage, je veux concaténer tous ces champs et les mettre dans un seul champs , autrement dit pour chaque avantage je voudrais avoir un seul ligne
Mon résultat dans cette exemple
Code:

1 2 3 CODE CONDITION 1 20 PTS A PARTIR DE 20 39 ACHAT && 20 PTS A PARTIR DE 20 39 ACHAT
L'utilisateur de système peut mettre plusieurs condition pour un seul avantage, mon besoin et d'avoir le max de l'index et en se basant sur ce max je fais la concaténation .

J'ai pensé a faire :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL> DECLARE
  2  LN$J pls_integer ; 
  3  LN$I pls_integer ; 
  4  LC$Requete VARCHAR2(512) ;
  5  LC$Requete1 VARCHAR2(512) ;
  6  BEGIN
  7  LC$Requete := 'SELECT MAX(AVTCARACT_INDEX) FROM VL_DATA.ADVCARRIERACTION';
  8   EXECUTE IMMEDIATE LC$Requete INTO LN$I;
  9  While LN$I > 0
 10  LC$Requete1 :='SELECT concat (F.AVTCARACT_AMOUNT,F.AVTCARACT_MIN ) FROM VL_DATA.ADVCARRIERACTION F WHERE  F.AVT_ID=1000000023 AND F.AVTCARACT_INDEX='||LN$I; 
 11  LN$I:=LN$I-1;
 12  LC$Requete1 := LC$Requete1 + 'SELECT concat (F.AVTCARACT_AMOUNT,F.AVTCARACT_MIN ) FROM VL_DATA.ADVCARRIERACTION F WHERE F.AVT_ID=1000000023 AND F.AVTCARACT_INDEX='||LN$I;
 13  END LOOP;
 14  END ;
 15  /

Mais ça fonctionne :(

20/04/2012, 10h07
lola06

Citation:

Envoyé par Rymaya

J'ai pensé a faire :

Mais ça fonctionne :(

Peux-tu mettre en forme ton code histoire qu'on y voit quelque chose ?
Et nous indiquer pourquoi cela ne fonctionne pas ? erreur ? mauvais résultat ?
20/04/2012, 10h13
McM
Pourquoi tu dis ça, il dit que ça fonctionne ;)

Je n'ai pas compris le résultat désiré :
Code:

1 2 CODE CONDITION 1 20 PTS A PARTIR DE 20 39 ACHAT && 20 PTS A PARTIR DE 20 39 ACHAT
Ce serait pas plutôt
20 PTS A PARTIR DE 20 39 ACHAT && 30 PTS A PARTIR DE 40 49 ?

Citation:
Envoyé par Rymaya
D'accord , voilà un exemple :
Code:

1 2 3 4 CODE VALEUR CLE INDEX OPE MIN MAX LIB 1 20 PTS 1 Apartir de 20 39 achat 1 30 PTS 2 Apartir de 40 49 achat
L'explication : L'avantage qui possède un code=1 à trois conditions: si l'achat entre 20 et 39 le client pourra avoir 20pts bonus , si l'achat entre 40 et 49 le client pourra avoir 30 pts
Mon but est de construire une dimension Avantage, je veux concaténer tous ces champs et les mettre dans un seul champs , autrement dit pour chaque avantage je voudrais avoir un seul ligne
Mon résultat dans cette exemple
Code:

1 2 3 CODE CONDITION 1 20 PTS A PARTIR DE 20 39 ACHAT && 20 PTS A PARTIR DE 20 39 ACHAT
L'utilisateur de système peut mettre plusieurs condition pour un seul avantage, mon besoin et d'avoir le max de l'index et en se basant sur ce max je fais la concaténation .
Dans cet exemple tu concatène deux fois le même avantage, est-ce bien ce que tu veux ? J'avais cru comprendre que tu voulais concaténer les différents avantages.

Merci pour vos réponses voilà ma requete , elle fonctionne correctement :

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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
SELECT E.AVT_ID ,
 
 
(SELECT F.AVTCARACT_AMOUNT||' '||FFFF.CSTTOT_KEY||' '||
        FF. OPE_LIB||' ' || F.AVTCARACT_MIN||' '||F.AVTCARACT_MAX||' '||FFF.ADVCOND_GUILIB FROM VL_DATA.ADVCARRIERACTION F INNER JOIN VL_DATA.OPERATORLIB FF ON F.OPE_CODE=FF.OPE_CODE
                                                                           INNER JOIN VL_DATA.ADVCONDITIONLIB FFF on FFF.ADVCOND_CODE=F.ADVCOND_CODE
                                                                           INNER JOIN VL_DATA.ADVANTAGE FFFF on FFFF.AVT_ID=F.AVT_ID
                                                                           WHERE F.AVTCARACT_INDEX=1 AND E.AVT_ID=F.AVT_ID) ||'  '|| 
 (SELECT F.AVTCARACT_AMOUNT||' '||FFFF.CSTTOT_KEY||' '||
        FF. OPE_LIB||' ' || F.AVTCARACT_MIN||' '||F.AVTCARACT_MAX||' '||FFF.ADVCOND_GUILIB FROM VL_DATA.ADVCARRIERACTION F INNER JOIN VL_DATA.OPERATORLIB FF ON F.OPE_CODE=FF.OPE_CODE
                                                                           INNER JOIN VL_DATA.ADVCONDITIONLIB FFF on FFF.ADVCOND_CODE=F.ADVCOND_CODE
                                                                           INNER JOIN VL_DATA.ADVANTAGE FFFF on FFFF.AVT_ID=F.AVT_ID
                                                                           WHERE F.AVTCARACT_INDEX=2 AND E.AVT_ID=F.AVT_ID)  ||'   ' ||    
 
 (SELECT F.AVTCARACT_AMOUNT||' '||FFFF.CSTTOT_KEY||' '||
        FF. OPE_LIB||' ' || F.AVTCARACT_MIN||' '||F.AVTCARACT_MAX||' '||FFF.ADVCOND_GUILIB FROM VL_DATA.ADVCARRIERACTION F INNER JOIN VL_DATA.OPERATORLIB FF ON F.OPE_CODE=FF.OPE_CODE
                                                                           INNER JOIN VL_DATA.ADVCONDITIONLIB FFF on FFF.ADVCOND_CODE=F.ADVCOND_CODE
                                                                           INNER JOIN VL_DATA.ADVANTAGE FFFF on FFFF.AVT_ID=F.AVT_ID
                                                                           WHERE F.AVTCARACT_INDEX=3 AND E.AVT_ID=F.AVT_ID) ||'  '||  
 
 
  (SELECT F.AVTCARACT_AMOUNT||' '||FFFF.CSTTOT_KEY||' '||
        FF. OPE_LIB||' ' || F.AVTCARACT_MIN||' '||F.AVTCARACT_MAX||' '||FFF.ADVCOND_GUILIB FROM VL_DATA.ADVCARRIERACTION F INNER JOIN VL_DATA.OPERATORLIB FF ON F.OPE_CODE=FF.OPE_CODE
                                                                           INNER JOIN VL_DATA.ADVCONDITIONLIB FFF on FFF.ADVCOND_CODE=F.ADVCOND_CODE
                                                                           INNER JOIN VL_DATA.ADVANTAGE FFFF on FFFF.AVT_ID=F.AVT_ID
                                                                           WHERE F.AVTCARACT_INDEX=4 AND E.AVT_ID=F.AVT_ID) ||' ' ||
 
  (SELECT F.AVTCARACT_AMOUNT||' '||FFFF.CSTTOT_KEY||' '||
        FF. OPE_LIB||' ' || F.AVTCARACT_MIN||' '||F.AVTCARACT_MAX||' '||FFF.ADVCOND_GUILIB FROM VL_DATA.ADVCARRIERACTION F INNER JOIN VL_DATA.OPERATORLIB FF ON F.OPE_CODE=FF.OPE_CODE
                                                                           INNER JOIN VL_DATA.ADVCONDITIONLIB FFF on FFF.ADVCOND_CODE=F.ADVCOND_CODE
                                                                           INNER JOIN VL_DATA.ADVANTAGE FFFF on FFFF.AVT_ID=F.AVT_ID
                                                                           WHERE F.AVTCARACT_INDEX=5 AND E.AVT_ID=F.AVT_ID) ||' '|| ''||
 
    (SELECT F.AVTCARACT_AMOUNT||' '||FFFF.CSTTOT_KEY||' '||
        FF. OPE_LIB||' ' || F.AVTCARACT_MIN||' '||F.AVTCARACT_MAX||' '||FFF.ADVCOND_GUILIB FROM VL_DATA.ADVCARRIERACTION F INNER JOIN VL_DATA.OPERATORLIB FF ON F.OPE_CODE=FF.OPE_CODE
                                                                           INNER JOIN VL_DATA.ADVCONDITIONLIB FFF on FFF.ADVCOND_CODE=F.ADVCOND_CODE
                                                                           INNER JOIN VL_DATA.ADVANTAGE FFFF on FFFF.AVT_ID=F.AVT_ID
                                                                           WHERE F.AVTCARACT_INDEX=6 AND E.AVT_ID=F.AVT_ID)   ||'  '|| 
 
      (SELECT F.AVTCARACT_AMOUNT||' '||FFFF.CSTTOT_KEY||' '||
        FF. OPE_LIB||' ' || F.AVTCARACT_MIN||' '||F.AVTCARACT_MAX||' '||FFF.ADVCOND_GUILIB FROM VL_DATA.ADVCARRIERACTION F INNER JOIN VL_DATA.OPERATORLIB FF ON F.OPE_CODE=FF.OPE_CODE
                                                                           INNER JOIN VL_DATA.ADVCONDITIONLIB FFF on FFF.ADVCOND_CODE=F.ADVCOND_CODE
                                                                           INNER JOIN VL_DATA.ADVANTAGE FFFF on FFFF.AVT_ID=F.AVT_ID
                                                                           WHERE F.AVTCARACT_INDEX=7 AND E.AVT_ID=F.AVT_ID)                                                                      
 
                                                                           AS CONDITION_PORTEUR                                                                              
 
 
         FROM        VL_DATA.ADVANTAGE E

Dans ma requête j'ai mis 7 comme max , mais l’utilisateur peut réaliser des avantages avec 4 ou 5 ou même 10 conditions .

Si on veut concaténer les différents avantages, c'est bien du pivot.
Plusieurs méthodes

Celle avec PIVOT (mais non dispo dans certaines versions de base)

Celle limitée à X concaténations

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT code, lib, RTRIM(desc0 ||' && '|| desc1||' && '|| desc2||' && '|| desc3||' && '|| desc4||' && '|| desc5||' && '|| desc6||' && '
			|| desc7||' && '|| desc8 ||' && '|| desc9,' &') description
 FROM(
  SELECT code, lib, 
        row_number() OVER (PARTITION BY code, lib ORDER BY ind) AS num,
        description desc0, 
        lead(description, 1) OVER (PARTITION BY code, lib ORDER BY ind) AS desc1,
        lead(description, 2) OVER (PARTITION BY code, lib ORDER BY ind) AS desc2,
        lead(description, 3) OVER (PARTITION BY code, lib ORDER BY ind) AS desc3,
        lead(description, 4) OVER (PARTITION BY code, lib ORDER BY ind) AS desc4,
        lead(description, 5) OVER (PARTITION BY code, lib ORDER BY ind) AS desc5,
        lead(description, 6) OVER (PARTITION BY code, lib ORDER BY ind) AS desc6,
        lead(description, 7) OVER (PARTITION BY code, lib ORDER BY ind) AS desc7,
        lead(description, 7) OVER (PARTITION BY code, lib ORDER BY ind) AS desc8,
        lead(description, 7) OVER (PARTITION BY code, lib ORDER BY ind) AS desc9
  FROM (
  SELECT code, ind, lib, valeur || cle || ope || mini ||' '|| maxi AS description
  FROM (SELECT 1 code, 20 valeur, ' PTS' cle, 1 ind, ' a partir de ' ope, 20 mini, 39 maxi, 'achat' lib FROM dual
        UNION ALL 
        SELECT 1 code, 30 valeur, ' PTS' cle, 2 ind, ' a partir de ' ope, 40 mini, 49 maxi, 'achat' lib FROM dual)
  )
)
WHERE num = 1

Code:

1
2
CODE	LIB	DESCRIPTION
1	achat	20 PTS a partir de 20 39 && 30 PTS a partir de 40 49

Celle en plsql

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE FUNCTION F_CONCAT(p_code IN NUMBER) RETURN varchar2
IS
 v_retour VARCHAR2(4000);
BEGIN
	FOR r IN (SELECT valeur || cle || ope || mini ||' '|| maxi AS lib FROM tatable WHERE code = p_code ORDER BY ind)
  LOOP
  	v_retour := v_retour ||' && '|| r.lib;
  END LOOP;
	RETURN RTRIM(v_retour, ' &');
END;
 
 
 SELECT code, F_CONCAT(code)
 FROM tatable
 GROUP BY code

20/04/2012, 10h33
Rymaya

Ma concaténation se fait sur les colonnes et si index>1 alors je fais une autre concaténation sur les lignes

Merci infiniment McM pour votre réponse .

J'ai implémenté la fonction j'ai le message 'COMPILED SUCCESSFULY', mais j'ai pas pu faire le SELECT
l'erreur
Code:

1 2 3 4 5 Compilation errors for FUNCTION SYSTEM.F_CONCAT Error: PLS-00103: Symbole "SELECT" rencontré Line: 20 Text: SELECT avt_ID, F_CONCAT(AVT_ID) FROM VL_DATA.ADVCARRIERACTION;

20/04/2012, 11h38
Rams7s

Il doit manquer un "/" a la ligne 10, apres le END de la function.
20/04/2012, 11h43
Rymaya

Merci beaucoup pour votre aide , ça fonctionne :)