problème avec between

**philmot62** · 05/03/2011, 21h55

Bonjour,
J'ai une liste de valeurs du type

Champ1
CRA
CRB
CRC
CRD
CR1
CR2

et j'ai une table ou j'ai une correspondance du type

Debut Fin Valeur
CRA CR9 ABCD

Lorsque je fais mon instruction

Code :

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

1
2
3
4
select * from xxx,yyy
 
where 
Champ1 between Debut and Fin

Rien n'est extrait.

Je pense qu'il s'agit d'un probleme ascii vs EBCDIC ,
Mais comment régler ce problème.

Merci de votre aide.

Philippe

**iberserk** · 06/03/2011, 09h29

BETWEEN sur des charactères!
Le between est exclusif (correspond a > and < et non à >= and <=)

Passez par des clef technique comme un AUTO INCREMENT...

**Népomucène** · 06/03/2011, 18h41

Le between est exclusif

depuis quand ?

BETWEEN sur des charactères

BETWEEN fonctionne sur des caractères, date, numériques (mais pas sur les TEXT et autres BLOB)

Donc le problème est ailleurs.
Il est probable que dans le classement par défaut de la base,
'CRA' vienne après 'CR9' et donc ne puisse pas renvoyer de valeur.

Pour tester, exécuter le test suivant :

Code :

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

1
2
3
4
5
6
7
8
CREATE TABLE #TEST (Champ varchar(50))
 
INSERT INTO #Test values('CR9')
INSERT INTO #Test values('CRA')
 
SELECT * FROM #Test ORDER BY Champ
 
DROP TABLE #Test

Vous aurez certainement :

Champ
--------
CR9
CRA

Du coup, si 'CR9' vient avant 'CRA' alors
BETWEEN 'CRA' AND 'CR9' ne peut rien renvoyer

**iberserk** · 06/03/2011, 19h45

Je me confonds en excuse BETWEEN n'est pas exclusif!
C'est çà de répondre un dimanche matin :-)

Bien sûr que le BETWEEN fonctionne sur les caractères je suis seulement choqué de son utilisation, résultat d'une modélisation souvent désastreuse (je suis payé à temps plein pour reprendre les applications "modélisées" par des développeurs qui sont tombées en prod...).

**SQLpro** · 07/03/2011, 00h40

Cela peut être une modélisation parfaitement correcte car elle répond en totalité à la forme normale domaine clef !
J'avais hélas donné un exemples de cette forme normale sur wikipédia. malheureusement un crétin l'a supprimé.. Il s'agissait d'un exemples avec des véhicules et la catégorie des véhicules. De 0 à 3,5 T le véhicule est classé VL, au delà de 3.5 t le véhicule est classé PL. Il y a bien un domaine (une plage de valeur) pour une clef (la catégorie). C'est la forme normale Domaine clef et on la rencontre plus souvent qu'on ne le croit. Par exemple les tarifs d'affranchissement en sont un autre exemple.

A +

**iberserk** · 07/03/2011, 07h46

Cela peut être une modélisation parfaitement correcte car elle répond en totalité à la forme normale domaine clef

Nous sommes d'accord mais cela ne veux pas dire qu'elle soit optimale...

modélisation SOUVENT désastreuse

Vous êtes le mieux placé pour savoir que la modélisation est souvent laissée à des développeurs dont ce n'est pas le métier et qui ne sont pas très sensibles aux problématiques de montée en charge, de non redondance des données ETC.

Heureusement on peut s'appuyer quotidiennement sur vos travaux pour les remettre dans le droit chemin

A+

**SQLpro** · 07/03/2011, 09h23

Envoyé par iberserk

Nous sommes d'accord mais cela ne veux pas dire qu'elle soit optimale...

Justement si ! Une forme normale => modélisation optimale !

A +

**Népomucène** · 07/03/2011, 10h33

Bon, pour en revenir au problème qui turlupine philmot62,
si c'est bien le classement qui lui joue un tour, voici une petite solution simple et pas chère qui règle la question :

Code :

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

1
2
3
4
5
select * from xxx,yyy 
where Champ1 between 
     CASE WHEN Debut<=Fin THEN Debut ELSE Fin END 
and 
     CASE WHEN Debut>Fin THEN Debut ELSE Fin END

**SQLpro** · 07/03/2011, 15h27

Il suffit plus simplement de rajouter un COLLATE au bon endroit !

Code :

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

1
2
3
4
5
6
 
SELECT * 
FROM xxx
       INNER JOIN yyy
             ON ...
WHERE Colonne1 COLLATE French_CI_AI BETWEEN Debut AND Fin

Et pour apprendre le SQL, mon site web (http://sqlpro.developpez.com/cours/s...er/collations/), comme mon bouquin, peuvent vous être utiles !

A +

**Népomucène** · 07/03/2011, 15h57

SQLpro, ton site est, certes, impressionnant, mais le COLLATE French_CI_AI ne résout pas le problème :

On suppose que les deux tables xxx et yyy existent avec un classement quelconque.
Si on essaye de changer le comportement du BETWEEN avec COLLATE French_CI_AI, il ne se passe rien

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
CREATE TABLE xxx (Champ1 varchar(10))
INSERT INTO xxx values ('CRA')
INSERT INTO xxx values ('CRB')
INSERT INTO xxx values ('CRC')
INSERT INTO xxx values ('CRD')
INSERT INTO xxx values ('CR1')
INSERT INTO xxx values ('CR2')
 
CREATE TABLE yyy (Debut varchar(10), Fin varchar(10), Valeur varchar(10))
INSERT INTO yyy values ('CRA', 'CR9', 'ABCD')
 
-- ne renvoie rien :
SELECT * FROM xxx,yyy 
WHERE Champ1 COLLATE French_CI_AI BETWEEN Debut AND Fin
 
-- petite bidouille qui fonctionne :
SELECT * FROM xxx,yyy 
WHERE Champ1 BETWEEN 
     CASE WHEN Debut<=Fin THEN Debut ELSE Fin END 
AND 
     CASE WHEN Debut>Fin THEN Debut ELSE Fin END
 
 
DROP TABLE xxx, yyy

peut-être avec un autre COLLATE alors ?

**aieeeuuuuu** · 07/03/2011, 16h41

Bonjour,

En effet, je ne sais pas quelle collation pour classer '1' > 'A'...

Par contre, pour ta solution Nepumocene, je crains qu'elle ait des effets indésirables, par exemple, une recherche

Code sql :

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

1
2
 
BETWEEN 'CRB' AND 'CR9'

fera surement ressortir 'CRA'...

Je pense que la solution est bien de créer une table contenant l'ordre voulu :
ID | Code | Ordre

et de faire une jointure dessus, puis filtrer avec le BETWEEN sur ordre !

**SQLpro** · 07/03/2011, 16h50

Ta bidouille est bonne, mais mélanger des lettres et des chiffres ne donne jamais rien de bon. Car a l'envers celle là ne marcherait pas :

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
CREATE TABLE xxx (Champ1 varchar(10))
INSERT INTO xxx VALUES ('CRA')
INSERT INTO xxx VALUES ('CRB')
INSERT INTO xxx VALUES ('CRC')
INSERT INTO xxx VALUES ('CRD')
INSERT INTO xxx VALUES ('CR1')
INSERT INTO xxx VALUES ('CR2')
 
CREATE TABLE yyy (Debut varchar(10), Fin varchar(10), Valeur varchar(10))
INSERT INTO yyy VALUES ('AAA', '999', 'ABCD')
 
-- ne renvoie rien :
SELECT * FROM xxx,yyy 
WHERE Champ1 COLLATE French_BIN BETWEEN Debut AND Fin
 
-- petite bidouille qui fonctionne :
SELECT * FROM xxx,yyy 
WHERE Champ1 BETWEEN 
     CASE WHEN Debut<=Fin THEN Debut ELSE Fin END 
AND 
     CASE WHEN Debut>Fin THEN Debut ELSE Fin END

Le problème est que l'on ne peut pas redéfinir l'ordonnancement des caractères 1 à 1 !

A +

**Népomucène** · 07/03/2011, 17h29

Dans cette bidouille, je m'en suis tenu strictement aux données du problème posé par philmot62.

@aieeeuuuuu

fera surement ressortir 'CRA'...

C'est bien 'CRA' qui sort (philmot62 se plaint de n'avoir aucun résultat, là il a au moins quelque chose)

Ta solution de créer une table de tri est évidement l'arme absolue.
Cependant la colonne Ordre de ta table doit certainement être saisie "à la main",
il ne faut pas que la liste des codes soit trop longue ...

@SQLpro

a l'envers celle là ne marcherait pas

C'est inexact elle continue de fonctionner dans les deux cas :
INSERT INTO yyy VALUES ('CRA', 'CR9', 'ABCD')
ou bien
INSERT INTO yyy VALUES ('CR9', 'CRA', 'ABCD')

L'exemple avec
INSERT INTO yyy VALUES ('AAA', '999', 'ABCD')
ne peut jamais fonctionner car 'AAA' ou '999' sont toujours inférieurs aux valeurs de Champ1 de xxx.

C'est intéressant comme discussion, je n'avais pas encore eu l'idée d'utiliser COLLATE "à la volée" dans une requête.

Le problème est que l'on ne peut pas redéfinir l'ordonnancement des caractères 1 à 1

En effet. Si on était vraiment dans ce cas de figure, il faudrait créer une table de classement avec une colonne ayant son propre classement par caractère composant le champ1 d'origine ...

La solution est probablement avant la mise en exploitation de la base de données ; à savoir une bonne codification cohérente des données qui ne prête à aucune confusion.
(Je ne sais pas si la base de philmot62 est réellement une base professionnelle ou s'il s'agit d'un test qu'il a fait sur un coin de table)

**Waldar** · 07/03/2011, 17h30

Envoyé par aieeeuuuuu

En effet, je ne sais pas quelle collation pour classer '1' > 'A'...

L'auteur répond déjà à cette question :

Envoyé par philmot62

Je pense qu'il s'agit d'un probleme ascii vs EBCDIC

Donc :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT * FROM xxx,yyy 
WHERE Champ1 BETWEEN Debut AND Fin collate SQL_EBCDIC037_CP1_CS_AS
 
Champ1     Debut      Fin        Valeur
---------- ---------- ---------- ----------
CRA        CRA        CR9        ABCD
CRB        CRA        CR9        ABCD
CRC        CRA        CR9        ABCD
CRD        CRA        CR9        ABCD

J'ai pris une des collations EBCDI au hasard parmi les huit disponibles sur SQL-Server 2005.

**Népomucène** · 07/03/2011, 17h45

Eh ben voilà ....

et d'ailleurs, les deux syntaxes (Waldar, SQLpro) fonctionnent :

Code :

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

1
2
3
4
5
6
 
SELECT * FROM xxx,yyy 
WHERE Champ1 COLLATE SQL_EBCDIC037_CP1_CS_AS BETWEEN Debut AND Fin
 
SELECT * FROM xxx,yyy 
WHERE Champ1 BETWEEN Debut AND Fin COLLATE SQL_EBCDIC037_CP1_CS_AS

Reste à savoir si c'est le résultat attendu par philmot62

**SQLpro** · 08/03/2011, 12h06

Bien vu pour l'EBCDIC car originellement le jeu de caractères (profondement debile de IBM) mélange lettre et nombre dans le classement.
En effet les lettres sont numérotés de 0 à 9 et les chiffres de A à F
donc le classement est :
ABCDEFGHIJ01234 KLMNOPQRST56789 UVWXYZ

A +

**aieeeuuuuu** · 08/03/2011, 12h48

Envoyé par SQLpro

En effet les lettres sont numérotés de 0 à 9 et les chiffres de A à F

"Pourquoi faire simple quand on peut faire compliqué"...

J'en perds mon SQL_Latin1 (<== désolé, j'ai pas pu m'empecher

)

donc le classement est :
ABCDEFGHIJ01234 KLMNOPQRST56789 UVWXYZ

A +

au passage, le script ci-dessous semble indiquer un classement différent quoiqu'aussi farfelu (mais chiffres et lettres ne sont pas mélangés) :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
WITH CTE as(
	SELECT 1 AS num, CHAR(1) COLLATE SQL_EBCDIC285_CP1_CS_AS AS car
	UNION ALL
	SELECT num + 1 , CHAR(num + 1) COLLATE SQL_EBCDIC285_CP1_CS_AS 
	FROM cte
	WHERE num < 255
	)
SELECT car
FROM cte
ORDER BY  car
OPTION (MAXRECURSION 0)

==>

a,b,c,d,e,f,g,h,i,«,»,ð,ý,þ,±,°,j,k,l,m,n,o,p,q,r,ª,º,æ,‚,Æ,¤,µ,˜,s,t,u,v,w,x,y,z,¡,¿,Ð,Ý,Þ,®,¢,[,¥,·,©,§,¶,¼,½,¾,ˆ,],¯,¨,´,×,{,A,B,C,D,E,F,G,H,I,*,ô,ö,ò,ó,õ,},J,K,L,M,N,O,P,Q,R,¹,û,ü,ù,ú,ÿ,\,÷,S,T,U,V,W,X,Y,Z,²,Ô,Ö,Ò,Ó,Õ,0,1,2,3,4,5,6,7,8,9,³,Û,Ü,Ù,Ú