Discussion :

[dan2035]

Bonjour,
j'ai 2 tables tab1 et tab2 avec :
- id(1),nom(huile1),volume(v1) pour tab1 et
- id(1),prix(p1),volume(v0 si v1<=v0) pour tab2
- id(2),prix(p2 si v1>v0)pour tab2 aussi.
v0,v1,p1,p2 sont du type float.

ma question, j'ai fait la requête suivante:

Code :

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

1
2
3
4
5
6
7
 
select tab1.nom,tab1.volume,tab2.prix,tab2.volume, 
(
CASE 
       WHEN ((tab1.volume>tab2.volume) THEN ((p1*tab1.volume) and (p2*(tab1.volume-tab2.volume)))) 
ELSE 
        (p1*tab1.volume) END) as result from tab1,tab2;

cela m'a l'air correct et il m'affiche un résultat "result" qui ne représente pas les valeurs calculées. idée svp???
merci.

[escartefigue]

Bonjour,

Il manque un prédicat de jointure, d'où produit cartésien et résultat faux.
Par contre il y a pléthore de parenthèses inutiles et aucune mise en forme pour faciliter la lecture


et ceci est incorrect :

THEN ((p1*tab1.volume) and (p2*(tab1.volume-tab2.volume))

[dan2035]

que me suggérez vous svp ?

[escartefigue]

Tout dépend du but recherché...

[SQLpro]

que me suggérez vous svp ?

Débarrassez vous du stock de parenthèses que vous avez en trop !!!!!

A +

[Artemus24]

Salut à tous.

Vous avez deux tables :

1) tab1 qui contient disons les volumes d'huiles que vous désirez acheter.

2) tab2 le prix à appliquer sur le type d'huile.

Vous désirez calculer le prix. Jusque là, je comprends ce que vous cherchez à faire.
Mais ce que je ne comprends, c'est votre façon de calculer ce prix qui me semble incorrecte.

L'idée est d'appliquer une comparaison entre la volumétrie acheté et la façon de calculer le prix.
Pour ce faire, j'utilise un intervalle sur le volume pour déterminer quel prix je vais appliquer.

Le mieux est de donner un exemple :

Code :

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

1
2
3
4
5
6
7
8
+--------+------+
| Volume | Prix |
+--------+------+
|    5   |  10  |
|   10   |   9  |
|   15   |   8  |
|   20   |   7  |
+--------+------+

Si le volume est <= 5 alors on applique le prix 10
Ce qui donne : volume * 10 = 50.

Si le volume est <= 10 alors on applique le prix 10 sur le volume <= 5, le prix 9 sur le volume - 5.
Ce qui donne ! 5 * 10 + (volume - 5) * 9.

Si le volume est <= 15 alors on applique le prix 10 sur le volume <= 5, le prix 9 sur le volume - 5, et le prix 8 sur le volume - 15.
Ce qui donne : 5 * 10 + (10 - 5) * 9 + (volume - 15) * 8.

Et ainsi de suite.

Voici ce que cela donne :

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
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `tabtwo`
--------------
 
--------------
CREATE TABLE `tabtwo`
(  `id`      integer unsigned NOT NULL,
   `rang`    tinyint unsigned NOT NULL,
   `prix`    decimal(15,2)    NOT NULL,
   `volume`  decimal(15,0)        NULL,
   primary key (`id`,`rang`)
) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
--------------
 
--------------
INSERT INTO `tabtwo` (`id`,`rang`,`prix`,`volume`) VALUES
  (1, 1, 5, 10.0),(1, 2, 4, 20.0),(1, 3, 3, NULL),
  (2, 1, 7, 12.0),(2, 2, 6, 24.0),(2, 3, 5, NULL),
  (3, 1, 6,  7.0),(3, 2, 5, 14.0),(3, 3, 4, NULL)
--------------
 
--------------
select * from `tabtwo`
--------------
 
+----+------+------+--------+
| id | rang | prix | volume |
+----+------+------+--------+
|  1 |    1 | 5.00 |     10 |
|  1 |    2 | 4.00 |     20 |
|  1 |    3 | 3.00 |   NULL |
|  2 |    1 | 7.00 |     12 |
|  2 |    2 | 6.00 |     24 |
|  2 |    3 | 5.00 |   NULL |
|  3 |    1 | 6.00 |      7 |
|  3 |    2 | 5.00 |     14 |
|  3 |    3 | 4.00 |   NULL |
+----+------+------+--------+
--------------
DROP TABLE IF EXISTS `tabone`
--------------
 
--------------
CREATE TABLE `tabone`
(  `id`        integer unsigned NOT NULL auto_increment primary key,
   `nom`       varchar(255)     NOT NULL,
   `commande`  decimal(15,0)    NOT NULL,
   `clef`      integer unsigned not null,
   CONSTRAINT `FK_01` FOREIGN KEY (`clef`) REFERENCES `tabtwo` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
--------------
 
--------------
INSERT INTO `tabone` (`nom`,`commande`,`clef`) VALUES
  ('Huile Un', 7, 1),('Huile Deux', 22, 2),('Huile Trois', 20, 3)
--------------
 
--------------
select * from `tabone`
--------------
 
+----+-------------+----------+------+
| id | nom         | commande | clef |
+----+-------------+----------+------+
|  1 | Huile Un    |        7 |    1 |
|  2 | Huile Deux  |       22 |    2 |
|  3 | Huile Trois |       20 |    3 |
+----+-------------+----------+------+
--------------
select      t1.nom,
            t1.commande,
            t2.prix    as prix_1,
            t2.volume  as volume_1,
            t3.prix    as prix_2,
            t3.volume  as volume_2,
            t4.prix    as prix_3,
 
            CASE WHEN t1.commande <= t2.volume THEN t2.prix * t1.commande
                 WHEN t1.commande <= t3.volume THEN t2.prix * t2.volume + t3.prix * (t1.commande - t2.volume)
                                               ELSE t2.prix * t2.volume + t3.prix * (t3.volume   - t2.volume) + t4.prix * (t1.commande - t3.volume) END as result
 
      from  `tabone` as t1
 
inner join  `tabtwo` as t2
        on  t2.id = t1.clef and t2.rang = 1
 
inner join  `tabtwo` as t3
        on  t3.id = t1.clef and t3.rang = 2
 
inner join  `tabtwo` as t4
        on  t4.id = t1.clef and t4.rang = 3
--------------
 
+-------------+----------+--------+----------+--------+----------+--------+--------+
| nom         | commande | prix_1 | volume_1 | prix_2 | volume_2 | prix_3 | result |
+-------------+----------+--------+----------+--------+----------+--------+--------+
| Huile Un    |        7 |   5.00 |       10 |   4.00 |       20 |   3.00 |  35.00 |
| Huile Deux  |       22 |   7.00 |       12 |   6.00 |       24 |   5.00 | 144.00 |
| Huile Trois |       20 |   6.00 |        7 |   5.00 |       14 |   4.00 | 101.00 |
+-------------+----------+--------+----------+--------+----------+--------+--------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

C'est la même idée, sauf que j'applique trois intervalles au lieu de deux.

@+

[dan2035]

merci énormément, vous avez répondu exactement à ma question. en fait, c'est une promotion pour les commandes. plus vous commandez, plus vous avez des discounts. salut à tous.

[dan2035]

excusez moi, je reviens encore vers vous.
la même requête, dans une "insert into tab3", le tout dans un " string.format()" ne me donne pas le résultat escompté dans eclipse javafx.
comme t1.commande>t2.volume, j'ai comme résultat: - t2.prix * t2.volume
- t2.prix * t1.commande sur 2 lignes. ( t3.prix n'est jamais utilisé ).
est ce a cause du string.format()??

[dan2035]

j'ai oublié de vous dire que j'ai testé dans mysql la requête sans le string.format() et ca fonctionne. mais quand je la teste dans javafx sous eclipse bien sur cette fois avec string.format(), elle ne donne pas le bon résultat!! des idées svp.

[Artemus24]

Salut dan2035.

Je reprends mon exemple, et je vais détailler le calcul.

1) "Huile Un". La commande est de 7.
Nous nous trouvons dans le premier cas avec commande < volume_1.
Le calcul donne :
--> commande * prix_1
--> 5 * 7
--> 35

2) "Huile Deux". La commande est de 22.
Nous nous trouvons dans le deuxième cas avec : volume_1 <= commande < volume_2.
Le calcul donne :
--> volume_1 * prix_1 + (commande - volume_1) * prix_2
--> 12 * 7 + (22 - 12) * 6
--> 144

3) "Huile Trois". La commande est de 20.
Nous nous trouvons dans le troisième cas avec : commande > volume_2.
Le calcul donne :
--> volume_1 * prix_1 + (volume_2 - volume_1) * prix_2 + (commande - volume_2) * prix_3.
--> 7 * 6 + (14 - 7) * 5 + (20 - 14) * 4
--> 101

Je viens à nouveau de vérifier, les calculs sont justes.

comme t1.commande>t2.volume, j'ai comme résultat: - t2.prix * t2.volume
- t2.prix * t1.commande sur 2 lignes. ( t3.prix n'est jamais utilisé ).

Cela ne correspond à aucun cas que je vous ai donné.

En admettant que cela soit le deuxième cas, le test doit être : t2.volume <= t1.commande < t3.volume
et le calcul sera : t2.volume * t2.prix + (t1.commande - t2.volume) * t3.prix
Dans ce cas t3.prix est utilisé.

Si le string s'applique sur des colonnes numériques, il est normal que la conversion ne se fasse pas corrctement.

@+

[dan2035]

salue à tous et spécialement artemus24.
ce que vous m'avez suggéré artemus24 est très correcte. il n'y a rien à dire la dessus. en fait, en creusant la dessus, je découvre que mon string.format() pose problème. il faut dire déjà que ma requête est longues avec 11 variables. ajouter à cela le case when, cette condition n'est pas réalisée. c'est lorsque j'exécute le code javafx qui va interroger la bd et en extrait les infos utiles. je creuse encore un moment et si cela ne va pas, je vais vous montrer mon code avec la requête. merci à vous tous.

[dan2035]

salue à tous. voila qui est bon. j'ai trouvé ce qui n'allait pas dans mon programme.
- il y'avait d'abord un if de trop,
- et puis surtout, quelque chose de très bête: au lieu d'avoir un + "CASE ", j'avais + "CASE". sur toutes les lignes, il fallait un espace avant le " fermant.
voilà, c'est tout bête, mais c'est bloquant!! tout est bien qui finit bien. je vous remercie tous et principalement artemus24. le sujet peut être marqué maintenant comme résolu.

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
 
       String tabc = string.format("tab_%d",6);
       String var0 = "01/03/2006";
       String var1 = "30/03/2006";par exmple
       query = String.format("INSERT INTO tab3 (SELECT %s.id_tab, %s.nom, %s.commande, t2.prix, t2.volume," 
	+ "CASE"
	+ "WHEN %s.commande <= t2.volume THEN t2.prix*%s.commande"
	+ "ELSE t2.prix*t2.volume + t3.prix*(%s.commande - t2.volume) END as result"
	+ "FROM %s"
	+ "INNER JOIN tab2 as t2 ON t2.rang = 1"
	+ "INNER JOIN tab2 as t3 ON t3.rang = 2" 
	+ "where t2.id_client=%s.id_tab and date0 >= '%s' and date0 <= '%s' group by id_tab, prix, volume)", tabc, tabc, tabc, tabc, tabc, tabc, tabc, tabc, var0, var1);
 
      prepareStatement = conn.prepareStatement(query);	
     rs = prepareStatement.executeQuery();