Discussion :

[Alexandre T]

Bonjour,

Je travaille sur une base de production très mal montée. Les merisiens ou autres adeptes de bases bien propres en seraient médusés. Mais je dois faire avec, je n'ai pas le choix.

Sur la base de développement je lance (en simplifiant la requête):

Code :

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

1
2
3
4
 
SELECT max(TO_NUMBER(msgid)) as msgid_max
  FROM messages
 WHERE TO_NUMBER(msgid) >= 10 AND TO_NUMBER(msgid) < 50

Cela marche très bien. Mais je vois venir la question sur la nature de colonne alors j'y réponds de suite : Oui les messages sont stockés en chaine de caractère, je sais c'est nul, mais que voulez-vous...

Malheureusement en production, et bien j'ai des chaines de caractères du genre 'TOTO'... Du coup je ne peux pas les convertir en string. Ce même script retourne la fatidique erreur :

Code :

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

Erreur nombre non valide

Ma question, comment faire pour lui dire que si msgid n'est pas un nombre alors la valeur est zéro ?

[plaineR]

Tu peux creer une fonction IS_NUMBER

Code :

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

1
2
3
4
5
6
7
8
9
create or replace function is_number (msg_id in varchar2) 
return number is
begin
   v_num := to_number (msg_id);
   return (1);
exception
   when others then return (0);
end;
/

Et tu adaptes ta requête de la manière suivante :

Code :

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

1
2
3
select max (decode (is_number(msg_id), 1, to_number(msg_id), 0))
from messages
where ...

Une autre solution peut-être un fonction qui te renvoie soit msg_id, soit 0 dans le même principe que celle proposée.

[sygale]

Nous avons réglé ca via PL nous :

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
 
function f_ret_numeric(xvar_num in varchar2) return number
   as
   --------------------------------------------------------------------------
   -- But   : retourne un nombre sinon 0 si c'est pas un nombre
   -- Param : xvar_num     IN : chaine à tester
   --         return : 0 si ce N'est PAS un nombre
   --                  le nombre si c'est un nombre
   --------------------------------------------------------------------------
      wnumber     number;
   begin
      wnumber := to_number(xvar_num);
      return wnumber ;
   exception
      when others then
         return 0;
   end;

Ah la normalisation c'est pas si mal qd meêm bon courage à toi en tout cas !

[Alexandre T]

Oui.... Mais je n'ai pas le droit d'utiliser le PL ... Je sais c'est pénible, mais que voulez-vous... Bon je vais essayer une astuce à deux francs si sous... avec des REPLACE pour remplacer toutes les valeurs qui ne sont pas des chiffres par des zéros...

[LeoAnderson]

Voilà, la solution c'est ça !

Code :

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

 Where LENGTH (TRIM (TRANSLATE ( MsgID,  ' +-.0123456789',' ') ) ) IS NULL

IS NULL <=> numérique

[Alexandre T]

J'avais un problème de connexion, j'ai juste pu noté résolu Mais pas pu poster mon message de remerciement Merci

[orafrance]

on aurait aussi pu passer par une table temporaire ou un trigger mais bon

[Magnus]

Voilà, la solution c'est ça !

Code :

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

 Where LENGTH (TRIM (TRANSLATE ( MsgID,  ' +-.0123456789',' ') ) ) IS NULL

IS NULL <=> numérique

En faisant une recherche avancée je suis tombé sur ce post et cette solution ne me semble pas correcte :

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
25
8i CYRIL> SELECT '1'
  2  FROM DUAL
  3  WHERE LENGTH (TRIM (TRANSLATE ( '-12.0345',  ' +-.0123456789',' ') ) ) IS NULL;
 
'
-
1
 
1 ligne sélectionnée.
 
8i CYRIL> SELECT '1'
  2  FROM DUAL
  3  WHERE LENGTH (TRIM (TRANSLATE ( 'this is a text',  ' +-.0123456789',' ') ) ) IS NULL;
 
aucune ligne sélectionnée
 
8i CYRIL> SELECT '1'
  2  FROM DUAL
  3  WHERE LENGTH (TRIM (TRANSLATE ( '-12.-12',  ' +-.0123456789',' ') ) ) IS NULL;
 
'
-
1
 
1 ligne sélectionnée.

Eh oui la dernière instruction indique que la chaîne "-12.-12" correspond effectivement à un nombre valide, ce qui n'est pas le cas

La raison de cet échec est que le nombre de séparateur de décimales, de signes négatif, etc. est décisif dans la détermination du fait que la variable de caractères passée en paramètre est un nombre ou non.

Vous me corrigerez si je me trompe.