Nom de colonnes non statiques

[edogawa]

bonjour,
je cherche pour des besoins spécifiques, à retourner une table avec des noms de colonnes qui se varient selon la date du système:
exemple du résultat attendu :

SOMME042011
--------------
20
30

j'ai essayé avec la requête suivante mais ça se compile pas

Code :

1
2
 
SELECT s.total AS (SELECT 'SOMME/'||to_char(sysdate,'MONYYYYMM') FROM dual) FROM sommes s WHERE ...

comment faire pour avoir le bon résultat ? est ce que je dois passer par une procédure stocké ?

Merci

[Waldar]

Oui, avec du SQL dynamique.
Mais à mon avis vous devriez procéder à ce changement "ailleurs".

Que faites-vous de ces données ?

[Pomalaix]

Si c'est dans SQL*Plus, ça peut se faire grâce à la variable de substitution prédéfinie _DATE.

Code :

1
2
3
4
5
6
7
SQL> ALTER session SET nls_date_format='DDMMYYYY';
 
SQL> SELECT count(*) AS col&_DATE FROM user_objects;
 
COL14042011
-----------
      23121

[edogawa]

Bonjour, et merci pour les réponses.

Que faites-vous de ces données ?

je développe une procédure stocké, et parmi les éléments retournés, il y a une colonne qui doit avoir comme nom COL/MONYYYYMM (exemple AVR201104).

Si c'est dans SQL*Plus, ça peut se faire grâce à la variable de substitution prédéfinie _DATE.

Je travaille avec l'outil Toad,lorsque j'essaie setter la date session avec le format "MONYYYYMM", une erreur se produit :

ORA-01816: le mois ne peut être indiqué qu'une seule fois

[ojo77]

Envoyé par edogawa

Je travaille avec l'outil Toad,lorsque j'essaie setter la date session avec le format "MONYYYYMM", une erreur se produit :

Le formattage de la date pour NLS exige que chaque champs ne soit représenté qu'une fois (l'année, le mois, les secondes ...)

Lorsque vous donnez le format MONYYYYMM, MON et MM représentent tous les deux le mois, pour utiliser la méthode décrite par Pomalaix il faut choisir une et une seule représentation du mois.

Vous pouvez utiliser une variable que vous définissez vous même :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
14:16:34 SQL> COLUMN madate new_value ladate noprint
14:16:41 SQL> SELECT to_char(sysdate,'MONYYYYMM') madate FROM dual;
 
1 row selected.
 
14:16:45 SQL> SELECT sysdate "col&ladate" FROM dual;
old   1: SELECT sysdate "col&ladate" FROM dual
new   1: SELECT sysdate "colAVR. 201104" FROM dual
 
colAVR. 201104
-------------------
15/04/2011,14:16:51
 
1 row selected.

[edogawa]

Bonjour,
Merci encore pour les réponses,
Avec la procédure que je développe, il y a plusieurs colonnes que je dois retourner avec un nom dépendant de la date: je m'explique :

SOMME042011 | SOMME032011 | SOMME022011
---------------------------------------------
10 10 10
20 30 40

qu'est ce que vous pouvez me proposez pour ce cas ?

Merci beaucoup de votre aide et excusez moi pour ces questions, je suis pas un expert PL SQL..

[ojo77]

Disons que la description du problème est un peu courte ... Alors on va imaginer

J'imagine donc que vous avez une table à 3 colonnes, l'une contant des dates, une autre des valeurs numérique et la troisième des valeurs de regroupement. Bref la table de gestion des dépenses par postes (par exemple)

Bref j'imagine une jolie table comme celle-ci :

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
LA_DATE     POSTE_DEP        DEPENSES
----------- ---------------- --------
01/01/2011  Bouffe                112
02/01/2011  Voiture                70
10/01/2011  Bouffe                250
15/01/2011  Voiture                67
17/01/2011  Loisirs                30
25/01/2011  Bouffe                310
27/01/2011  Voiture                68
31/01/2011  Loisirs                42
01/02/2011  Bouffe                132
02/02/2011  Voiture                30
10/02/2011  Bouffe                230
15/02/2011  Voiture                37
17/02/2011  Loisirs                30
25/02/2011  Bouffe                340
27/02/2011  Voiture                48
01/03/2011  Bouffe                143
02/03/2011  Voiture                43
10/03/2011  Bouffe                243
15/03/2011  Voiture                43
17/03/2011  Loisirs                33
25/03/2011  Bouffe                313
27/03/2011  Voiture                63
31/03/2011  Loisirs                43

Donc nous voilà en avril 2011 et j'imaine que vous voulez faire un bilan financier du premier trimestre ...

Solution n°1 je me paluche la requête :

Version avant la 11g :

Code :

1
2
3
4
5
6
SELECT POSTE
     , sum ( decode ( to_char(LA_DATE,'YYYYMM') ,'201101',DEPENSE,0 ) ) SOMME201101
     , sum ( decode ( to_char(LA_DATE,'YYYYMM') ,'201102',DEPENSE,0 ) ) SOMME201102
     , sum ( decode ( to_char(LA_DATE,'YYYYMM') ,'201103',DEPENSE,0 ) ) SOMME201103
FROM dep_ext
GROUP BY POSTE ;

Version 11g

Code :

1
2
3
4
5
SELECT * 
FROM ( SELECT 'SOMME'||to_char(LA_DATE,'YYYYMM') LE_MOIS 
            , poste
            , depense  
	   FROM dep_ext) PIVOT ( SUM(DEPENSE) FOR LE_MOIS IN ('SOMME201101','SOMME201102','SOMME201103') );

Simple, efficace, mais il faut se la retaper tous les mois ...

Solution n°2 j'écris un code PL/SQL qui me génère cette requête :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL> CREATE OR REPLACE FUNCTION gen_req RETURN clob IS
  2   ret   clob;
  3   ttext varchar2(32767);
  4  begin
  5   ret := 'select POSTE'||chr(10);
  6   ttext:='';
  7   FOR c IN ( SELECT DISTINCT to_char(LA_DATE,'YYYYMM') fil FROM dep_ext )
  8   loop
  9    ttext:=ttext||'     , sum ( decode ( to_char(LA_DATE,''YYYYMM'') ,'''||trim(c.fil)||''',DEPENSE,0 ) ) SOMME'||trim(c.fil)||chr(10);
 10   end loop;
 11   ttext:=ttext||'from dep_ext'||chr(10)||'group by POSTE ;'||chr(10);
 12   dbms_lob.writeappend(ret,length(ttext),ttext);
 13   RETURN ret ;
 14  end gen_req ;
 15  /

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> SELECT gen_req FROM dual;
 
GEN_REQ
-------------------------------------------------------------------------------------------------
SELECT POSTE
     , sum ( decode ( to_char(LA_DATE,'YYYYMM') ,'201101',DEPENSE,0 ) ) SOMME201101
     , sum ( decode ( to_char(LA_DATE,'YYYYMM') ,'201102',DEPENSE,0 ) ) SOMME201102
     , sum ( decode ( to_char(LA_DATE,'YYYYMM') ,'201103',DEPENSE,0 ) ) SOMME201103
FROM dep_ext
GROUP BY POSTE ;
 
 
1 ligne sÚlectionnÚe.

Le résultat de la requête une fois exécutée est le suivant :

Code :

1
2
3
4
5
POSTE                                              SOMME201101 SOMME201102 SOMME201103
-------------------------------------------------- ----------- ----------- -----------
Bouffe                                                     672         702         699
Voiture                                                    205         115         149
Loisirs                                                     72          30          76

Mais bon comme j'ai imaginé pas mal de trucs, je ne suis pas certains du tout que ce soit exactment ce que vous attendez

[skuatamad]

Effectivement il faut faire du sql dynamique pour pivoter les lignes, mais autant renvoyer un curseur :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE OR REPLACE procedure gen_req2(ret out sys_refcursor) IS
     ttext varchar2(32767);    
    begin
     ttext := 'select POSTE_DEP ';
     FOR c IN ( SELECT DISTINCT to_char(LA_DATE,'YYYYMM') fil FROM dep_ext )
     loop
      ttext:=ttext||'     , sum ( decode ( to_char(LA_DATE,''YYYYMM'') ,'''||trim(c.fil)||''',DEPENSES,0 ) ) SOMME'||trim(c.fil);
    end loop;
    ttext:=ttext||' from dep_ext group by POSTE_DEP';
    --dbms_output.put_line(ttext);
    open ret FOR ttext;
   end;
   /

Et pour l'exécuter dans sqlplus :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SQL> var rc refcursor
SQL> execute gen_req2 (:rc)
 
PL/SQL procedure successfully completed.
 
SQL> print rc
 
POSTE_D SOMME201101 SOMME201102 SOMME201103
------- ----------- ----------- -----------
Bouffe          672         702         699
Voiture         205         115         149
Loisirs          72          30          76

PRINT c'est la méthode sqlplus pour fetch le curseur, à transposer au langage client utilisé.

Sinon il y a aussi la méthode PIVOT de tom kyte.