problème avec between

Version imprimable

05/03/2011, 21h55
philmot62

problème avec between
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:

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
06/03/2011, 09h29
iberserk

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

Passez par des clef technique comme un AUTO INCREMENT...
06/03/2011, 18h41
Népomucène
Citation:

Le between est exclusif

depuis quand ?

Citation:

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:

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 :

Citation:

Champ
--------
CR9
CRA

Du coup, si 'CR9' vient avant 'CRA' alors
BETWEEN 'CRA' AND 'CR9' ne peut rien renvoyer
06/03/2011, 19h45
iberserk

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...).
07/03/2011, 00h40
SQLpro

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 +
07/03/2011, 07h46
iberserk

Citation:

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...

Citation:

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 :ccool:

A+
07/03/2011, 09h23
SQLpro

Citation:

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 +
07/03/2011, 10h33
Népomucène
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:

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
07/03/2011, 15h27
SQLpro
Il suffit plus simplement de rajouter un COLLATE au bon endroit !
Code:

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 +

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:

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 ?

07/03/2011, 16h41
aieeeuuuuu
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:

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 !

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:

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 +

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

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

@aieeeuuuuu

Citation:

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

Citation:

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.

Citation:

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)

Citation:

Envoyé par aieeeuuuuu

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

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

Citation:

Envoyé par philmot62

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

Donc :
Code:

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.

Eh ben voilà ....:ccool:

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

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

08/03/2011, 12h06
SQLpro

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 +

Citation:

Envoyé par SQLpro

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

8O

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

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

Citation:

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:

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)
==>

Citation:

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,³,Û,Ü,Ù,Ú

:mur:

08/03/2011, 15h59
SQLpro

Ca dépend de la version de EBCDIC !!!
Mais la première (IBM 360) c'était ça !!!!!!!!!!

A +
08/03/2011, 16h04
aieeeuuuuu

Citation:

Envoyé par SQLpro

Ca dépend de la version de EBCDIC !!!

ha oui... car c'était trop simple sinon :lol:

et re :mur:

et bon courage à philmot62 !