Problème d'une vue en SQL

**aieeeuuuuu** · 17/01/2011, 16h23

Je ne suis pas sur de bien comprendre

est-ce que ceci te donne ce que tu veux ?

Code sql :

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
 
SELECT carte.carte_ID, listRegle.regle_ID, l.limitation_ID, l.limitation_valeur
FROM carte
CROSS JOIN (
  SELECT 1 UNION ALL
  SELECT 2 UNION ALL
  SELECT 3 UNION ALL
  SELECT 4
) AS listRegle(num_regle) 
LEFT OUTER JOIN (
  SELECT ROW_NUMBER OVER(PARTITION BY carte_ID) AS Num_regle, 
     Carte_ID,
   limitation_id,
    limitation_valeur
  FROM Limitation
) AS l
  ON l.carte_ID = carte.carte_ID
  AND l.num_regle = listRegle.num_regle

**Marty000** · 17/01/2011, 16h42

Non, pas tout a fait.
J'aimerais récupérer pour chaque carte, quatre limitations, qu'elle soit nul ou non en fait.
Une carte a au maximum 4 limitations (1 limitation est le lien entre une carte et une règle,) et au minimum aucune, mais j'aimerais qu'on me retourne 4 fois nuls dans ce cas :
carte_ID | regle_ID | limitation_ID | limitation_valeur
37 NULL NULL NULL
37 NULL NULL NULL
37 NULL NULL NULL
37 NULL NULL NULL

Si la carte 37 est liée à 3 limitations, on peut récupérer ces infos :
carte_ID | regle_ID | limitation_ID | limitation_valeur
37 1 1 0.5
37 2 2 1
37 89 3 0.5
37 NULL NULL NULL

Le numéro de règle n'existe pas en base, il n'y a pas de champs qui s'intitule numéro de regle ou autre.

C'est pour cela que dans la requete que j'ai travaillé au début du poste j'ai ce genre de lignes, je voulais récupérer 4 limitations :

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
 LEFT OUTER JOIN (SELECT L1.carte_ID,L1.limitation_ID,L1.limitation_valeur,L1.limitation_creeLe,L1.limitation_modifieLe,
                                                     R1.regle_ID,R1.regle_libelle,R1.regle_creeLe,R1.regle_modifieLe
                                         FROM Limitation AS L1 INNER JOIN Regle AS R1 ON L1.Regle_ID=R1.Regle_ID) T1 ON Carte.Carte_ID=T1.Carte_ID
 
                  LEFT OUTER JOIN (SELECT L2.carte_ID,L2.limitation_ID,L2.limitation_valeur,L2.limitation_creeLe,L2.limitation_modifieLe,
                                                     R2.regle_ID,R2.regle_libelle,R2.regle_creeLe,R2.regle_modifieLe
                                         FROM Limitation AS L2 INNER JOIN Regle AS R2 ON L2.Regle_ID=R2.Regle_ID) T2 ON Carte.Carte_ID=T2.Carte_ID
 
                  LEFT OUTER JOIN (SELECT L3.carte_ID,L3.limitation_ID,L3.limitation_valeur,L3.limitation_creeLe,L3.limitation_modifieLe,
                                                     R3.regle_ID,R3.regle_libelle,R3.regle_creeLe,R3.regle_modifieLe
                                         FROM Limitation AS L3 INNER JOIN Regle AS R3 ON L3.Regle_ID=R3.Regle_ID) T3 ON Carte.Carte_ID=T3.Carte_ID
 
                  LEFT OUTER JOIN (SELECT L4.carte_ID,L4.limitation_ID,L4.limitation_valeur,L4.limitation_creeLe,L4.limitation_modifieLe,
                                                     R4.regle_ID,R4.regle_libelle,R4.regle_creeLe,R4.regle_modifieLe
                                         FROM Limitation AS L4 INNER JOIN Regle AS R4 ON L4.Regle_ID=R4.Regle_ID) T4 ON Carte.Carte_ID=T4.Carte_ID

Ce numéro de règle c'est une idée pour expliquer.

**CinePhil** · 17/01/2011, 16h47

J'ai l'impression que ton truc est plus de la présentation, de la cosmétique, et devrait être pris en charge par le programme qui utilise la BDD.
Comme ton numéro de règle est purement fictif, on ne peut pas faire de jointure sur celui-ci pour remplir la ligne de la règle n° X avec des NULL ou des valeurs.

**Marty000** · 17/01/2011, 16h58

En fait, c'est du 100% SQL Server, vu que cette vue est utilisée par une procédure stockée, et nulle part ailleurs.
C'est pour faire une vue, et donc éviter les traitements longs et plus couteux en temps.

J'ai essayé de repartir de ma requête, j'ai renommé les limitation_valeur, en limitation_enCours :

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
Select distinct top(1) T1.Carte_ID,T1.limitation_enCours,T1.limitation_creeLe,
      T1.regle_libelle,T1.regle_creeLe,
      --table2
      (case
            when T2.regle_ID=T1.regle_ID then NULL
            else T2.limitation_enCours
      end) as T2_limitation_enCours,
      (case 
            when T2.regle_ID=T1.regle_ID then NULL
            else T2.limitation_creeLe
      end) as T2_limitation_creeLe,
      (case 
            when T2.regle_ID=T1.regle_ID then NULL
            else T2.limitation_modifieLe
      end) as T2_limitation_modifieLe,
      (case 
            when T2.regle_ID=T1.regle_ID then NULL
            else T2.regle_libelle
      end) as T2_regle_libelle,
      (case 
            when T2.regle_ID=T1.regle_ID then NULL
            else T2.regle_creeLe
      end) as T2_regle_creeLe,
      (case 
            when T2.regle_ID=T1.regle_ID then NULL
            else T2.regle_modifieLe
      end) as T2_regle_modifieLe,
      --table 3
      (case
            when T2.regle_ID=T1.regle_ID or T3.regle_ID=T2.regle_ID then NULL
            else T3.limitation_enCours
      end) as T3_limitation_enCours,
      (case 
            when T2.regle_ID=T1.regle_ID or T3.regle_ID=T2.regle_ID then NULL
            else T3.limitation_creeLe
      end) as T3_limitation_creeLe,
      (case 
            when T2.regle_ID=T1.regle_ID or T3.regle_ID=T2.regle_ID then NULL
            else T3.limitation_modifieLe
      end) as T3_limitation_modifieLe,
      (case 
            when T2.regle_ID=T1.regle_ID or T3.regle_ID=T2.regle_ID then NULL
            else T3.regle_libelle
      end) as T3_regle_libelle,
      (case 
            when T2.regle_ID=T1.regle_ID or T3.regle_ID=T2.regle_ID then NULL
            else T3.regle_creeLe
      end) as T3_regle_creeLe,
      (case 
            when T2.regle_ID=T1.regle_ID or T3.regle_ID=T2.regle_ID then NULL
            else T3.regle_modifieLe
      end) as T3_regle_modifieLe,
--table4
      (case
            when T2.regle_ID=T1.regle_ID or T3.regle_ID=T2.regle_ID or T4.regle_ID=T3.regle_ID then NULL
            else T4.limitation_enCours
      end) as T4_limitation_enCours,
      (case 
            when T2.regle_ID=T1.regle_ID or T3.regle_ID=T2.regle_ID or T4.regle_ID=T3.regle_ID then NULL
            else T4.limitation_creeLe
      end) as T4_limitation_creeLe,
      (case 
            when T2.regle_ID=T1.regle_ID or T3.regle_ID=T2.regle_ID or T4.regle_ID=T3.regle_ID then NULL
            else T4.limitation_modifieLe
      end) as T4_limitation_modifieLe,
      (case 
            when T2.regle_ID=T1.regle_ID or T3.regle_ID=T2.regle_ID or T4.regle_ID=T3.regle_ID then NULL
            else T4.regle_libelle
      end) as T4_regle_libelle,
      (case 
            when T2.regle_ID=T1.regle_ID or T3.regle_ID=T2.regle_ID or T4.regle_ID=T3.regle_ID then NULL
            else T4.regle_creeLe
      end) as T4_regle_creeLe,
      (case 
            when T2.regle_ID=T1.regle_ID or T3.regle_ID=T2.regle_ID or T4.regle_ID=T3.regle_ID then NULL
            else T4.regle_modifieLe
      end) as T4_regle_modifieLe
 
from (select L1.carte_ID,L1.limitation_ID,L1.limitation_enCours,L1.limitation_creeLe,L1.limitation_modifieLe,
                                                     R1.regle_ID,R1.regle_libelle,R1.regle_creeLe,R1.regle_modifieLe
                                         from Limitation as L1 inner join Regle as R1 on L1.Regle_ID=R1.Regle_ID) T1 
 
                  left outer join (select L2.carte_ID,L2.limitation_ID,L2.limitation_enCours,L2.limitation_creeLe,L2.limitation_modifieLe,
                                                     R2.regle_ID,R2.regle_libelle,R2.regle_creeLe,R2.regle_modifieLe
                                         from Limitation as L2 inner join Regle as R2 on L2.Regle_ID=R2.Regle_ID) T2 on T2.Carte_ID=T1.Carte_ID
 
                  left outer join (select L3.carte_ID,L3.limitation_ID,L3.limitation_enCours,L3.limitation_creeLe,L3.limitation_modifieLe,
                                                     R3.regle_ID,R3.regle_libelle,R3.regle_creeLe,R3.regle_modifieLe
                                         from Limitation as L3 inner join Regle as R3 on L3.Regle_ID=R3.Regle_ID) T3 on T3.Carte_ID=T1.Carte_ID
 
                  left outer join (select L4.carte_ID,L4.limitation_ID,L4.limitation_enCours,L4.limitation_creeLe,L4.limitation_modifieLe,
                                                     R4.regle_ID,R4.regle_libelle,R4.regle_creeLe,R4.regle_modifieLe
                                         from Limitation as L4 inner join Regle as R4 on L4.Regle_ID=R4.Regle_ID) T4 on T4.Carte_ID=T1.Carte_ID 
 
where T1.Regle_ID<=T2.Regle_ID and T2.Regle_ID<=T3.Regle_ID and T1.Regle_ID<=T3.Regle_ID and 
      T1.Regle_ID<=T4.Regle_ID and T2.Regle_ID<=T4.Regle_ID and T3.Regle_ID<=T4.Regle_ID
order by T4_regle_libelle desc,T3_regle_libelle desc,T2_regle_libelle desc

Mais dans ce cas je ne récupère que le premier élément avec le top(1), et sans celui-ci je ne peux pas savoir qu'elle ligne récupérée.

**punkoff** · 17/01/2011, 17h06

Envoyé par Marty000

En fait, c'est du 100% SQL Server, vu que cette vue est utilisée par une procédure stockée, et nulle part ailleurs.
C'est pour faire une vue, et donc éviter les traitements longs et plus couteux en temps.

J'ai du louper un wagon, mais en quoi une vue va-t-elle éviter des traitements longs et couteux ?
A moins que vous ne parliez de vue materlialisez ?

**Marty000** · 17/01/2011, 17h12

Bonjour punkoff,
oui ce que je voulais dire c'est que mes traitements seront, je pense plus long et couteux si je fais appel à une procédure stockée.
Le but final de cette vue est le suivant :
elle est lue par une procédure stockée justement, qui insère chaque ligne de ma vue, dans autre base de donnée.

**aieeeuuuuu** · 17/01/2011, 17h22

Envoyé par Marty000

Le numéro de règle n'existe pas en base, il n'y a pas de champs qui s'intitule numéro de regle ou autre.

Dans ma requête, la colonne num_regle est définie dans la pseudo table

as-tu testé ma requête, que t'a-t-elle renvoyé ?

Comme disait Waldar si tu nous fournissais un jeu d'essai et les résultats voulus, ça serait beaucoup plus simple !

**Marty000** · 17/01/2011, 17h36

En fait il y avait un léger soucis de syntaxe dans ta requête aieeeuuuuu et elle ne marchait pas.
J'avoue que sur le coup, il m'a semblé que le soucis venait de la colonne num_regle, mais en fait non.

Et après quelques tests, tu as bien trouvé ce que je voulais comme résultat !
Voici la requête finale :

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
SELECT carte.carte_ID, listRegle.num_regle, l.limitation_ID, l.limitation_enCours, l.regle_ID
FROM carte
CROSS JOIN (
  SELECT 1 UNION ALL
  SELECT 2 UNION ALL
  SELECT 3 UNION ALL
  SELECT 4
) AS listRegle(num_regle) 
LEFT OUTER JOIN (
  SELECT ROW_NUMBER() OVER(PARTITION BY carte_ID ORDER BY carte_ID ASC) AS Num_regle, 
     Carte_ID,
   limitation_id,
    limitation_enCours,
regle.regle_ID,
regle.regle_libelle
  FROM Limitation,Regle where regle.regle_ID = limitation.regle_ID
) AS l
  ON l.carte_ID = carte.carte_ID
  AND l.num_regle = listRegle.num_regle
order by carte_ID

J'ai juste rajouté les parties en gras pour obtenir exactement ce que je voulais, merci beaucoup à toi, et à vous.
Normalement mon problème est résolu, et je devrais avoir assez de matière pour m'en sortir seul, merci encore.

**Waldar** · 17/01/2011, 17h38

Il a fourni le jeu d'essai.
Marty000, je vous montre comme on fait, mais lisez les règles du forum car c'est expliqué.

Si vous aviez fourni ceci dès le départ, le sujet aurait été clos en moins d'une heure.

Objets

Code :

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

1
2
3
4
5
6
7
8
9
10
11
declare @Carte table (Carte_ID tinyint)
 
declare @Regle table (Regle_ID tinyint)
 
declare @Limitation table
(
    Limitation_ID     integer,
    Limitation_valeur decimal(2,1),
    Carte_ID          tinyint,
    Regle_ID          tinyint
)

Données

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
insert into @Carte (Carte_ID) values (37)
insert into @Carte (Carte_ID) values (38)
insert into @Carte (Carte_ID) values (39)
 
insert into @Regle (Regle_ID) values (1)
insert into @Regle (Regle_ID) values (2)
insert into @Regle (Regle_ID) values (3)
insert into @Regle (Regle_ID) values (4)
insert into @Regle (Regle_ID) values (5)
 
insert into @Limitation (Limitation_ID, Limitation_valeur, Carte_ID, Regle_ID)
select     1, -1  , 37, 1 union all
select     2,  0  , 37, 2 union all
select     3,  0  , 37, 3 union all
select     4,  0.5, 38, 1 union all
select     5,  5  , 38, 2 union all
select     6,  1  , 38, 3 union all
select 33998,  1  , 37, 5

Requête

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
;With NbDefinitions (NbRegle) as
(
select 1 union all
select 2 union all
select 3 union all
select 4 
)
  ,  SR (Carte_ID, rn, Regle_ID, Limitation_ID, Limitation_valeur) as
(
select L.Carte_ID,
       row_number() over(partition by L.Carte_ID order by L.Limitation_ID asc),
       L.Regle_ID, L.Limitation_ID, L.Limitation_valeur
  from @Limitation as L
)
  select C.Carte_ID, N.NbRegle, SR.Regle_ID, SR.Limitation_ID, SR.Limitation_valeur
    from @Carte as C
         cross join NbDefinitions as N
         left outer join SR
           ON SR.rn       = N.NbRegle
          AND SR.Carte_ID = C.Carte_ID
order by C.Carte_ID asc, N.NbRegle asc

Résultats

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Carte_ID NbRegle     Regle_ID Limitation_ID Limitation_valeur
-------- ----------- -------- ------------- ---------------------------------------
37       1           1        1             -1.0
37       2           2        2             0.0
37       3           3        3             0.0
37       4           5        33998         1.0
38       1           1        4             0.5
38       2           2        5             5.0
38       3           3        6             1.0
38       4           NULL     NULL          NULL
39       1           NULL     NULL          NULL
39       2           NULL     NULL          NULL
39       3           NULL     NULL          NULL
39       4           NULL     NULL          NULL

**Marty000** · 17/01/2011, 17h43

OK,
excusez-moi pour cette erreur.

Problème d'une vue en SQL

Langage SQL

Discussions similaires

Partager

Partager