Requête avec une concaténation dynamique

**Rymaya** · 19/04/2012, 13h22

Bonjour,

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

Code :

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

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

**Scriuiw** · 19/04/2012, 14h02

Le SQL Dynamique est ton ami !

**Waldar** · 19/04/2012, 14h30

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

**mnitu** · 19/04/2012, 14h33

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

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
 
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>

**Rymaya** · 19/04/2012, 15h31

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?

**McM** · 19/04/2012, 18h01

Avec un exemple on comprendra mieux.. c'est encore flou pour moi.

**Rams7s** · 19/04/2012, 18h51

Vous ne voulez pas faire un pivot par hasard?

**Rymaya** · 20/04/2012, 10h01

D'accord , voilà un exemple :

Code :

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

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 :

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

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 .

**Rymaya** · 20/04/2012, 10h03

J'ai pensé a faire :

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
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

**lola06** · 20/04/2012, 10h07

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 ?

**McM** · 20/04/2012, 10h13

Pourquoi tu dis ça, il dit que ça fonctionne

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

Code :

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

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 ?

**lola06** · 20/04/2012, 10h14

Envoyé par Rymaya

D'accord , voilà un exemple :

Code :

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

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 :

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

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.

**Rymaya** · 20/04/2012, 10h31

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

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
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 .

**McM** · 20/04/2012, 10h32

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 :

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
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 :

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

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 :

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

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

**Rymaya** · 20/04/2012, 10h33

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

**Rymaya** · 20/04/2012, 11h22

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 :

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

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;

**Rams7s** · 20/04/2012, 11h38

Il doit manquer un "/" a la ligne 10, apres le END de la function.

**Rymaya** · 20/04/2012, 11h43

Merci beaucoup pour votre aide , ça fonctionne

Requête avec une concaténation dynamique

SQL Oracle

Discussions similaires

Partager

Partager