Un SELECT dans un SUBSTRING() ?

Version imprimable

Bonjour,

Voilà le topo.
J'ai deux tables :

Promo (
ID,
Magasin,
Nom,
CatProf (catégories professionnelles de clients visées par la promo),
DateDebut,
DateFin)

CatProf (
ID,
Nom)

Bien sûr, il devrait y avoir une table de jointure entre les deux puisqu'il s'agit d'une relation n,n (une promo peut viser plusieurs catégories de client et une catégorie de client peut être touchée par plusieurs promos).
Mais ce n'est pas le cas. Le fondateur de notre DB ne l'entendait visiblement pas de cette oreille. Il a préféré identifier les catégories professionnelles visées par une promo grâce à un code (croyez-moi ça vaut le détour...).
Un string de 100 caractères avec des "X" et des espace " ". La position d'un X dans la chaîne de caractère correspond à l'ID d'une catégorie professionnelle visée.

Un exemple pour que ce soit bien claire :

Imaginons les catégories professionnelles :
(1, Entreprise générale)
(2, Menuisier)
(3, Peintre)
(4, Plafonneur)

Si Promo.CatProf = "X XX", cela veut dire que la promo en question vise les entreprises générales, les peintres et les plafonneurs mais pas les menuisiers.

Maintenant, il me faut trouver une solution pour isoler les promos visant une catégorie professionnelle spécifique. Ce paramètre étant variable d'une requête à l'autre.

Je précise également que j'utilise MSQuery pour extraire mon résultat et l'afficher dans Excel. MSQuery est fort limité comme je vais vous le montrer. Et j'aurais besoin de vous pour m'aider à contourner certaines restrictions.

L'idée, c'est d'utiliser SUBSTRING(Promo.CatProf, ma_variable, 1) = 'X' avec ma_variable = l'ID de la catégorie professionnelle dont je souhaite récupérer les promos.
Le tout dans une clause WHERE afin de tester la valeur du caractère qui correspond à ma CatProf.

Voici ma 1ère tentative :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 SELECT Promo.ID, Promo.Magasin, Promo.Nom, Promo.DateDébut, Promo.DateFin FROM Promo WHERE Promo.Magasin = 70 AND SUBSTRING(Promo.CatProf, ?, 1) = 'X'
Avec ? étant la variable que je souhaite injecter depuis Excel.
MSQuery permet à Excel d'injecter des paramètres dans une requête.
Malheureusement, il semblerait qu'il ne le permette qu'à droite du = dans une clause WHERE.
Exemple : "WHERE Promo.Magasin = ?" aurait fonctionné. Le ? serait alors une valeur variable arbitraire comme le contenu d'une celle Excel par exemple.

Je me suis donc mis en quête de faire passer le ? de l'autre côté du = d'une façon ou d'une autre.

Voici ma seconde tentative.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 SELECT Promo.ID, Promo.Magasin, Promo.Nom, Promo.DateDébut, Promo.DateFin FROM Promo WHERE Promo.Magasin = 70 AND SUBSTRING ( Promo.CatProf, ( SELECT CatProf.ID FROM CatProf WHERE CatProf.ID = ? ), 1 ) = 'X'
Mais je ne suis pas certain qu'on puisse imbriquer un SELECT dans SUBSTRING() de cette façon. En tout cas MSQuery m'annonce que "l'argument 2 de la fonction SUBSTRING est incorrecte".

J'ai aussi essayé avec une sous requête comme ceci...
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 SELECT Promo.ID, Promo.Magasin, Promo.Nom, Promo.DateDébut, Promo.DateFin, CategPro.ID AS Variable FROM Promo, ( SELECT CatProf.ID FROM CatProf WHERE CatProf.ID = ? ) AS CategPro WHERE Promo.Magasin = 70 AND SUBSTRING(Promo.CatProf, Variable, 1) = 'X'
... Sans succès.

Je commence à désespérer. Avez-vous d'autres idées ? Ou un correctif à faire sur mes requêtes ?

Merci.

Catab

01/12/2017, 17h21
CinePhil

Citation:

Il a préféré identifier les catégories professionnelles visées par une promo grâce à un code (croyez-moi ça vaut le détour...).
Un string de 100 caractères avec des "X" et des espace " ". La position d'un X dans la chaîne de caractère correspond à l'ID d'une catégorie professionnelle visée.

Quelle horreur ! 8O

Mon pauvre ! Il est vendredi soir, rentre chez toi et réfléchis lundi à la reconception de ce truc inepte !

Salut à tous.

Vous devez remplacer votre "string" par une clef étrangère. Voici un exemple de ce qu'il faut faire sous MySql :

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
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
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `categorie`
--------------
 
--------------
CREATE TABLE `categorie`
( `id`         INTEGER UNSIGNED  NOT NULL auto_increment PRIMARY KEY,
  `categorie`  VARCHAR(255)      NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `categorie` (`categorie`) values
  ('Entreprise générale'),('Menuisier'),('Peintre'),('Plafonneur')
--------------
 
--------------
select * from `categorie`
--------------
 
+----+---------------------+
| id | categorie           |
+----+---------------------+
|  1 | Entreprise générale |
|  2 | Menuisier           |
|  3 | Peintre             |
|  4 | Plafonneur          |
+----+---------------------+
--------------
DROP TABLE IF EXISTS `promotion`
--------------
 
--------------
CREATE TABLE `promotion`
( `id`       INTEGER UNSIGNED  NOT NULL auto_increment PRIMARY KEY,
  `magasin`  VARCHAR(255)      NOT NULL,
  `nom`      VARCHAR(255)      NOT NULL,
  `datdeb`   DATE              NOT NULL,
  `datfin`   DATE              NOT NULL,
  `catprof`  INTEGER UNSIGNED  NOT NULL,
  CONSTRAINT `FK_CATEGORIE` FOREIGN KEY (`catprof`) REFERENCES `categorie` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `promotion` (`magasin`,`nom`,`datdeb`,`datfin`,`catprof`) values
  ('magasin 01', 'nom 01', '2017-11-01', '2017-12-31', 1),
  ('magasin 02', 'nom 02', '2017-11-02', '2017-12-30', 2),
  ('magasin 03', 'nom 03', '2017-11-03', '2017-12-29', 3),
  ('magasin 04', 'nom 04', '2017-11-04', '2017-12-28', 4),
  ('magasin 05', 'nom 05', '2017-11-05', '2017-12-27', 1),
  ('magasin 06', 'nom 06', '2017-11-06', '2017-12-26', 2),
  ('magasin 07', 'nom 07', '2017-11-07', '2017-12-25', 3),
  ('magasin 08', 'nom 08', '2017-11-08', '2017-12-24', 4),
  ('magasin 09', 'nom 09', '2017-11-09', '2017-12-23', 1),
  ('magasin 10', 'nom 10', '2017-11-10', '2017-12-22', 2),
  ('magasin 11', 'nom 11', '2017-11-11', '2017-12-21', 3),
  ('magasin 12', 'nom 12', '2017-11-12', '2017-12-20', 4)
--------------
 
--------------
select * from `promotion`
--------------
 
+----+------------+--------+------------+------------+---------+
| id | magasin    | nom    | datdeb     | datfin     | catprof |
+----+------------+--------+------------+------------+---------+
|  1 | magasin 01 | nom 01 | 2017-11-01 | 2017-12-31 |       1 |
|  2 | magasin 02 | nom 02 | 2017-11-02 | 2017-12-30 |       2 |
|  3 | magasin 03 | nom 03 | 2017-11-03 | 2017-12-29 |       3 |
|  4 | magasin 04 | nom 04 | 2017-11-04 | 2017-12-28 |       4 |
|  5 | magasin 05 | nom 05 | 2017-11-05 | 2017-12-27 |       1 |
|  6 | magasin 06 | nom 06 | 2017-11-06 | 2017-12-26 |       2 |
|  7 | magasin 07 | nom 07 | 2017-11-07 | 2017-12-25 |       3 |
|  8 | magasin 08 | nom 08 | 2017-11-08 | 2017-12-24 |       4 |
|  9 | magasin 09 | nom 09 | 2017-11-09 | 2017-12-23 |       1 |
| 10 | magasin 10 | nom 10 | 2017-11-10 | 2017-12-22 |       2 |
| 11 | magasin 11 | nom 11 | 2017-11-11 | 2017-12-21 |       3 |
| 12 | magasin 12 | nom 12 | 2017-11-12 | 2017-12-20 |       4 |
+----+------------+--------+------------+------------+---------+
--------------
drop view if exists `vue`
--------------
 
--------------
create view `vue` as
  select      t1.id,
              t1.magasin,
              t1.nom,
              t1.datdeb,
              t1.datfin,
              t2.id       as catprof,
              t2.categorie
        from  `promotion` as t1
  inner join  `categorie` as t2
          on  t2.id = t1.catprof
    order by  t1.id
--------------
 
--------------
select * from `vue`
--------------
 
+----+------------+--------+------------+------------+---------+---------------------+
| id | magasin    | nom    | datdeb     | datfin     | catprof | categorie           |
+----+------------+--------+------------+------------+---------+---------------------+
|  1 | magasin 01 | nom 01 | 2017-11-01 | 2017-12-31 |       1 | Entreprise générale |
|  2 | magasin 02 | nom 02 | 2017-11-02 | 2017-12-30 |       2 | Menuisier           |
|  3 | magasin 03 | nom 03 | 2017-11-03 | 2017-12-29 |       3 | Peintre             |
|  4 | magasin 04 | nom 04 | 2017-11-04 | 2017-12-28 |       4 | Plafonneur          |
|  5 | magasin 05 | nom 05 | 2017-11-05 | 2017-12-27 |       1 | Entreprise générale |
|  6 | magasin 06 | nom 06 | 2017-11-06 | 2017-12-26 |       2 | Menuisier           |
|  7 | magasin 07 | nom 07 | 2017-11-07 | 2017-12-25 |       3 | Peintre             |
|  8 | magasin 08 | nom 08 | 2017-11-08 | 2017-12-24 |       4 | Plafonneur          |
|  9 | magasin 09 | nom 09 | 2017-11-09 | 2017-12-23 |       1 | Entreprise générale |
| 10 | magasin 10 | nom 10 | 2017-11-10 | 2017-12-22 |       2 | Menuisier           |
| 11 | magasin 11 | nom 11 | 2017-11-11 | 2017-12-21 |       3 | Peintre             |
| 12 | magasin 12 | nom 12 | 2017-11-12 | 2017-12-20 |       4 | Plafonneur          |
+----+------------+--------+------------+------------+---------+---------------------+
--------------
select  *
  from  `vue`
 where  catprof = 2
--------------
 
+----+------------+--------+------------+------------+---------+-----------+
| id | magasin    | nom    | datdeb     | datfin     | catprof | categorie |
+----+------------+--------+------------+------------+---------+-----------+
|  2 | magasin 02 | nom 02 | 2017-11-02 | 2017-12-30 |       2 | Menuisier |
|  6 | magasin 06 | nom 06 | 2017-11-06 | 2017-12-26 |       2 | Menuisier |
| 10 | magasin 10 | nom 10 | 2017-11-10 | 2017-12-22 |       2 | Menuisier |
+----+------------+--------+------------+------------+---------+-----------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

En utilisant une clef étrangère, il est plus facile de gérer vos catégories professionnelles !

@+

Salut à tous.

@ Catab : je vous rappelle que nous sommes dans le forum consacré à MySql.
Voici un exemple de comment gérer votre substring sous MySql.

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
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
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`      INTEGER UNSIGNED  NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `lib`     VARCHAR(255)      NOT NULL,
  `chaine`  VARCHAR(255)      NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test` (`lib`,`chaine`) values
  ('un',  'XXOX'),
  ('deux','XOXX')
--------------
 
--------------
select * from `test`
--------------
 
+----+------+--------+
| id | lib  | chaine |
+----+------+--------+
|  1 | un   | XXOX   |
|  2 | deux | XOXX   |
+----+------+--------+
--------------
set @pos = 3
--------------
 
--------------
select  lib
  from  `test`
 where  substring(`chaine`,@pos,1) = 'O'
--------------
 
+-----+
| lib |
+-----+
| un  |
+-----+
--------------
set @pos = 2
--------------
 
--------------
select  lib
  from  `test`
 where  substring(`chaine`,@pos,1) = 'O'
--------------
 
+------+
| lib  |
+------+
| deux |
+------+
--------------
set @pos = 1
--------------
 
--------------
select  lib
  from  `test`
 where  substring(`chaine`,@pos,1) = 'O'
--------------
 
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

14/12/2017, 14h11
Catab

Artemus24.

Merci pour ta proposition. Je suis pleinement conscient qu'il devrait y avoir une clé étrangère plutôt que cette ineptie de string. Je dirais même qu'il doit y avoir une table de jointure avec deux clés étrangères puisqu'il s'agit d'une relation n,n.

Malheureusement je n'ai aucun pouvoir de décision sur l'architecture de la DB. Je ne fais que manipuler ces données. Celle-ci est d'ailleurs vouée à être remplacée tellement elle est obsolète. Pour le coup, je dois faire avec ce que j'ai sous la main.

Pour ce qui est de ta solution avec le substring, c'est bel et bien ce que j'avais pensé. Le hic, c'est la position du caractère vérifié qui est variable. Malheureusement je ne peux pas introduire de variable telle que tu le fais. En raison des limitations de Microsoft Query, tout doit tenir dans une requête SELECT avec, au mieux, un ou plusieurs points d'interrogation dans la clause WHERE en guise de paramètres.

Je comprends que sur ce point, ça dépasse le cadre d'un forum MySQL. J'espérais juste de l'aide pour ma syntaxe.

Je m'excuse aussi pour le retard de ma réponse, je n'ai pas été notifié des vôtres.

Quoi qu'il en soit, ce n'est pas bien grave, ce problème attendra.

Catab.
15/12/2017, 02h33
Artemus24
Salut Catab.

Citation:

Envoyé par Catab

Le hic, c'est la position du caractère vérifié qui est variable.

Il n'y a pas de hic. Dans mon exemple, j'ai externalisé la partie variable afin de vous montrer que la requête est toujours la même.

Citation:

Envoyé par Catab

tout doit tenir dans une requête SELECT avec, au mieux, un ou plusieurs points d'interrogation dans la clause WHERE en guise de paramètres.

Juste une petite remarque, l'exemple que je vous ai donné est du mysql pur.
Vous avez raison d'utiliser une requête préparée afin de passer en paramètre (le ?) la partie variable.

Par exemple :
Code:

1 2 3 PREPARE stmt FROM "select lib from `test` where substring(`chaine`,?,1) = 'O'"; EXECUTE stmt USING 2; DEALLOCATE PREPARE stmt;
Citation:

Envoyé par Catab

Je précise également que j'utilise MSQuery pour extraire mon résultat et l'afficher dans Excel.

Nous sommes dans le forum MySql, et de ce fait, je réponds comme s'il s'agissait d'une question consacrée à MySql.
Je ne connais pas MSQuerry. Le mieux est de vous adresser au forum consacré à Excel et MSQuerry.

@+