[11g] Classement et filtres sur des varchars alphanumériques

Bonjour,


Je suis en présence d'un comportement surprenant lié au classement de données de type varchar2 : order by et les clauses de filtre ne semblent pas classer de la même manière.

Un exemple valant généralement mieux qu'un long discours, voici les requêtes dont les résultats me semblent peu cohérents :

Code:

---

1 2 3 4 5 6 7 8 9

select numero_dossier from plop order by numero_dossier;   select numero_dossier from plop where numero_dossier > 'MOA01' order by numero_dossier; select numero_dossier from plop where numero_dossier between 'MOA01' and '26082' order by numero_dossier;   select numero_dossier from plop where numero_dossier between '26082' and 'MOA01' order by numero_dossier;   select numero_dossier from plop where numero_dossier between '105340' and '26082' order by numero_dossier;

---

La première requête me fournit un certain nombre de résultats, ordonnés de la façon suivante :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

MOA01 105340 105340 105340 105340 17470 22940/41 26080 26081 26082 26083 26084 26085 26087 26089 2706 2706 2706   etc...

---

Les requêtes 2 et 3 ne fournissent AUCUN résultat (alors que 'MOA01' semble être considéré comme la valeur la plus petite de ma donnée numero_dossier) :(

La requête 4 renvoie toutes les données possibles sauf

Code:

---

1 2 3 4 5 6 7 8 9

105340 105340 105340 105340 17470 22940/41 26080 26081

---

que la clause order by présente bien comme comprises entre mes deux valeurs.

La 5e et dernière requête renvoie, quant à elle, un résultat cohérent avec l'ordre :

Code:

---

1 2 3 4 5 6 7 8 9 10

105340 105340 105340 105340 17470 22940/41 26080 26081 26082

---

Quelqu'un aurait-il des explications à m'apporter par rapport à cette problématique ? des pistes de solution (sans remettre en cause la structure des données) ?

D'avance merci.

Bonjour,

si vous jouez cette requête :

Code:

---

1 2 3

SELECT * FROM nls_session_parameters WHERE parameter IN ('NLS_COMP', 'NLS_SORT');

---

Obtenez vous comme résultat BINARY pour NLS_COMP et un nom de langue (sur ma base de test FRENCH) pour NLS_SORT. Ce qui veut dire que pour les comparaison, la base passe par les valeurs binaires des valeurs et pour les tris elle passe par un ordre alhpabetique.

Pour votre problème, essayez de passer en mode lunguistique pour les comparaisons :

Code:

---

alter session set nls_comp=LINGUISTIC;

---

Oracle contrairement à ses concurrent refuse d'intégrer les collations de la norme SQL, qui permettent de régler élégamment ce genre de problématiques, mais utilise l'imbitable NLS (global à la session) pour tenter d'apporter une solution.
Jouez avec le NLS en l'indiquant au niveau de la session ou dans votre requête :
http://ss64.com/ora/syntax-nls.html

A +

Merci à tous les deux.


Effectivement, une modification du nls_comp rend les résultats plus cohérents.
Ne me reste plus qu'à trouver comment modifier ce paramètre pour une requête spécifiquement via Hibernate (ce serait trop facile si c'était pour taper directement une base :P)

Comme ceci :

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

With plop (numero_dossier) as ( select cast(column_value as varchar2(10)) from table(sys.odcivarchar2list('MOA01', '105340', '105340', '105340', '105340', '17470', '22940/41', '26080', '26081', '26082', '26083', '26084', '26085', '26087', '26089', '2706', '2706', '2706')) ) select numero_dossier from plop where NLSSORT(numero_dossier, 'NLS_SORT=French') between NLSSORT('MOA01', 'NLS_SORT=French') and NLSSORT('26082', 'NLS_SORT=French') order by numero_dossier asc;   NUMERO_DOSSIER -------------- MOA01 105340 105340 105340 105340 17470 22940/41 26080 26081 26082

---

Mais je suis d'accord avec SQLPro, ce serait beaucoup plus simple d'écrire, comme sous les autres SGBD :

Code:

---

1 2 3 4

select numero_dossier from plop where numero_dossier between 'MOA01' and '26082' collate 'French' order by numero_dossier asc;

---