SQL ORDER BY avec CAST

Version imprimable

20/07/2011, 08h22
chatour

SQL ORDER BY avec CAST

Bonjour à tous
Mon transtypage échoue sur une colonne dont certaines chaines ne peuvent pas être tanstypées en nombre. Je pense qu'il s'agit d'un cas classique pour vous mais pas pour moi qui ne parle pas couramment le SQL
Merci pour vos conseils.
20/07/2011, 09h09
Rayek

Et ma boule de cristal m'indique qu'il manque le type de la base de données ainsi que la requête qui va bien afin de pouvoir aider ceux qui pourraient répondre à la question. :aie:
20/07/2011, 10h53
chatour

Excusez moi.
La base Firebird embedded
La requête qui va bien : ORDER BY CD.LONGUEUR DESC qui donne par exemple , sans surprise:
aucune
91,5
91
900
CAST échoue sur aucune
Dans CD.LONGUEUR je n'ai pas de NULL , seulement aucune et -
20/07/2011, 11h22
Rayek

Quel idée aussi de mettre un champ nommé Longueur avec un type varchar.

Solution possible si tu as la main sur la structure de la base de données.
1- Faire un update pour transformer aucune et - en -1 (a t on déja vu une longueur négative :p)
2- changer le type de champ en numeric(10,5).
3- quand tu vois -1 au niveau de l'affichage dans une grille ou autre tu marques aucune à la place
21/07/2011, 08h33
chatour

Bonjour à tous

Merci Rayek.
La base m'a été fournie sous forme de base Access seulement et je l'ai passée dans Firebird. En examinant un grand nombre de valeurs j'ai vu fréquemment Aucune et le tiret, ce qui expliquait le choix de Varchar. J'ai exclu un peu bêtement d'autres cas, rares mais bien présents, trouvés ensuite avec le CAST après codage des deux chaines connues, comme par exemple <18,9 ou >53,4, qui m'avaient échappé en lecture rapide. Cette situation m'apparaissant comme assez triviale, j'ai pensé qu'il existait une solution connue (un CAST amélioré, comme dans COVALESC? CASE avec SQLEXCEPTION? je ne vois pas ).
A défaut je vais recenser les exceptions dans Delphi et remplacer par exemple <18,9 par -1018,9 et >53,4 par -2053,4 etc..selon les cas rencontrés. Ainsi mes cas serons distingués et je pourrai retrouver mes valeurs si besoin est .
Pour la petite histoire, la valeur en question est une valeur moyenne, j'ai aussi le minimum et le maximum dans d'autres champs nommés malencontreusement MIN et MAX par le fournisseur, qui coincent avec le SQL
21/07/2011, 09h18
Rayek

Pour le min et max il te suffit d'utiliser le séparateur de champ de firebird (que je ne connais pas)
Dans Access c'était les [ et ], MySQL `, mais firebird je ne sais pas.
21/07/2011, 10h16
delagoutte

regarde la faq de firebird "How to convert string to number without getting any errors?" :
http://www.firebirdfaq.org/faq139/

tu mets ça dans une procédure stocké et ton problème devrait être réglé
21/07/2011, 12h07
chatour

Excellent Delagoutte, merci. Je subodorais le CAST amélioré et il existe. Il serait encore mieux s'il permettait la choix du retour car il renvoie zéro dans tous les cas, je verrai si je peux m'en contenter.
J'étais branché SQL et je n'ai pas fait de recherche du coté Firebird.

cela renvoi 0 car on lui dit de renvoyer 0 mais tu peut faire ce que tu veux.

Code:

1
2
3
4
5
6
7
DECLARE VARIABLE ch VARCHAR(10);
DECLARE VARIABLE nr INTEGER:
...
BEGIN
nr = CAST(ch AS INTEGER);
WHEN ANY DO nr = 0;
END

tu peut faire évoluer ta procédure pour avoir ce que tu veux
par exemple :

Code:

1
2
3
4
5
6
7
8
9
BEGIN
 if substring(ch from 1 to 1) ='<' then
    ch = replace(ch,'<','-10');
 if substring(ch from 1 to 1) ='>' then
    ch = replace(ch,'<','-20');
 if ch ='aucune' then
    ch = '-1';
nr = CAST(ch AS INTEGER);
WHEN ANY DO nr = 0;

La réponse complète à ton problème devrait être :

procedure stockée :

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
 
CREATE PROCEDURE CUSTOMCAST (
    CH VARCHAR (255))
RETURNS (
    NR FLOAT)
AS
BEGIN
  IF  (SUBSTRING(ch FROM 1 FOR 1) ='<') then
    ch = REPLACE(ch,'<','-10');
 
  IF (SUBSTRING(ch FROM 1 FOR 1) ='>') then
    ch = REPLACE(ch,'<','-20');
 
  if (ch ='aucune') then
    ch = '-1';
 
  ch = trim(ch);
 
  if (ch is null) then
    ch = '-1';
 
  BEGIN
    nr = CAST(ch AS float);
    WHEN ANY DO nr = 0;
  END
  SUSPEND;
END

ta réquête :

Code:

1
2
select CD.LONGEUR, (select * from customcast(CD.LONGEUR)) LONGEUR2 
from CD order by LONGEUR2 asc

il ne te reste plus qu'à adapter la procédure stockée selon tes besoins et les différents cas que tu rencontres

21/07/2011, 18h47
chatour

Quel style! Merci encore Delagoutte. C'est trop de sollicitude. Je suis persuadé que le SQL est ta langue maternelle. Je m'empresse d'ajouter que cela n'enlève rien à ton mérite. Pour moi il est beaucoup trop tard.