Vues avec plusieurs SELECT / avec plusieurs colonnes

[lediz]

Bonjour,
j'aimerais savoir s'il était possible de créer une vue contenant plusieurs select (en tant que colonne) et SURTOUT que ces SELECT me ramènent plusieurs colonnes...
Un exemple concret pour que cela soit plus clair :
j'aimerais pour 2011 : 1 enregistrement par nom et prenom
ainsi que (som) et (bdg) pour les 2 années précédentes

Tous ces éléments se trouvent dans une MEME table/vue qui contient
- Toujours un seul élément(nom/prénom) pour 2011,
- Mais peut-être rien en 2010 et peut-être rien en 2009

Code :

1
2
3
4
5
6
7
 
CREATE OR REPLACE VIEW toto (nom,prenom,bdg_1,som_1,bdg_2,som_2) AS
SELECT t.prenom,t.nom 
,(SELECT bdg,som FROM table1 t1 WHERE t.annee=t.annee-1 AND rownum <2)
,(SELECT bdg,som FROM table1 t2 WHERE t.annee=t.annee-2 AND rownum <2)
FROM table1 t WHERE t.annee=2011
/

J'ai une erreur à la création de la vue
car j'indique plusieurs colonnes dans mes 2 derniers SELECT
y-a-t'il un moyen de créer une vue ce ce genre ?
Pour que cela fonctionne je dois dédoubler les 2 SELECT(Avec une seule colonne) ..PAS du tout perfomant!
Surtout que ma vue finale va contenir une vingtaine de colonnes.

Pouvez-vous m'aider ?
Merci.

[McM]

mets les sous select dans la clause FROM

[lediz]

je vais essayer,
mais je n'ai accès à la base de données que lundi matin
pourrais-je avoir un exemple concret/simple,
car je n'ai jamais utilisé ce type de "mécanisme"
dans la création de mes vues.
Merci.

[McM]

Désolé, j'ai dit une connerie. Après avoir testé, ça ne marche pas (on ne peut pas référencer une autre table dans un sous select au niveau From.

2 solutions : Soit une condition sur les 3 années, avec un decode et un group by, soit une concaténation bdg ||'.'|| som dans les sousrequêtes, et un split dans un select au dessus

1ere solution (attention, A finir car on ne teste pas s'il existe une ligne en 2011)

Code :

1
2
3
4
5
6
SELECT prenom, nom, 
	max(decode(annee, 2009, bdg, NULL)) bdg_1, max(decode(annee, 2009, som, NULL)) som_1, 
	max(decode(annee, 2010, bdg, NULL)) bdg_2, max(decode(annee, 2010, som, NULL)) som_2 
FROM table1 
WHERE annee BETWEEN 2009 AND 2011
GROUP BY prenom, nom

2ème solution

Code :

1
2
3
4
5
6
7
SELECT prenom, nom, substr(d1, 0, instr(d1, '.')-1) bdg1, substr(d1, instr(d1, '.')+1) som1, 
				substr(d2, 0, instr(d2, '.')-1) bdg2, substr(d2, instr(d2, '.')+1) som2
FROM (
	SELECT t.prenom,t.nom,(SELECT bdg ||'.'|| som FROM table1 t1 WHERE t.annee=t.annee-1 AND rownum <2) d1
		,(SELECT bdg ||'.'|| som FROM table1 t2 WHERE t.annee=t.annee-2 AND rownum <2) d2
	FROM table1 t WHERE t.annee=2011
	)

[mnitu]

La requête scalaire ne peut ramener qu'une seule valeur d’une seule colonne. Il est très facile de contourner cette restriction en utilisant des types objet mais ce n’est pas très performant. Mais comme ce type de requête est équivalent à un outer join il suffit donc de l’utiliser:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
WITH DATA AS (
  SELECT prenom, nom, annee, Max(bdg) AS dgb, Max(bdg) AS som
    FROM table1 t1 
   GROUP BY prenom, nom, annee
)
SELECT t.prenom,t.nom, 
       t_1.bdg, t_1.som,
       t_2.bdg, t_2.som
  FROM table1 t 
       LEFT OUTER JOIN
       DATA t_1
    ON t.annee = t_1.annee-1
       AND t.nom = t_1.nom
       AND t.prenom = t_1.prenom
       LEFT OUTER JOIN
       DATA t_2
    ON t.annee = t_1.annee-2
       AND t.nom = t_2.nom
       AND t.prenom = t_2.prenom           
 WHERE t.annee=2011
/

Mais faite attention à la signification des vos rownum < 2.

[Drizzt [Drone38]]

Avec une fonction analytique sinon :

Code :

1
2
3
4
5
6
7
8
9
10
11
SELECT prenom, nom, bdg1, som1, bdg1, bdg2 
FROM (
    SELECT prenom, nom,
           lag(bdg,1) over(partition BY prenom, nom ORDER BY annee) bdg1,
           lag(som,1) over(partition BY prenom, nom ORDER BY annee) som1,
           lag(bdg,2) over(partition BY prenom, nom ORDER BY annee) bdg2,
           lag(som,2) over(partition BY prenom, nom ORDER BY annee) som2,
           annee
    FROM table1 
    WHERE annee BETWEEN 2011-2 AND 2011
) WHERE annee = 2011

Edit> Attention cette requête est incorrecte si il y a des trous dans la présence des données depuis 2011 (ex: données en 2011 et 2009 mais pas 2010). Par contre cela fonctionne si les données les plus récentes sont présentes (ex: données en 2011 et 2010 mais pas 2009).

[McM]

Ta requête ne fonctionne que si la clé c'est "nom, prenom, année", si jamais tu as mois en plus dans la clé, ça ne marchera pas.

[Drizzt [Drone38]]

Oui mais ce n'est pas précisé dans l'énoncé et il est facile de l'adapter si la clef est différente.

[McM]

Je ne suis pas sur.
De plus selon l'énoncé, une des années peut ne rien ramener comme données, dnas ce cas, le lag(1) ne va pas ramener la bonne année.

[Drizzt [Drone38]]

Je ne vois vraiment pas en quoi l'ajout du mois peut gener.
Je viens de faire un test (rapide, dans un cas nominal uniquement) et je peux sans souci remonter les valeurs des années 2010 et 2009 pour chaque mois de 2011 traité.

Par contre tu as raison si il manque des données en 2010.
J'avais testé avec des données manquantes en 2009, mais pas ce cas.

A voir donc si ce cas est fonctionnellement possible dans le cas de l'auteur mais je vais ajouter la restriction sur mon post.
Merci à toi pour la remarque.