Conversion en numérique d'un ID : erreur nombre non valide

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:

---

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:

---

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 ?

Tu peux creer une fonction IS_NUMBER

Code:

---

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:

---

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.

Nous avons réglé ca via PL nous :

Code:

---

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 !

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

Voilà, la solution c'est ça !

Code:

---

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

---

IS NULL <=> numérique

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

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

Citation:

---

Envoyé par LeoAnderson

Voilà, la solution c'est ça !

Code:

---

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:

---

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

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.