Vue Oracle et to_number

Bonjour à tous,

Je vous expose un problème rencontré que je n'arrive pas à résoudre.

Je crée une vue Oracle (sous Oracle 11g) à partir d'une table avec une colonne que je suis obligé de convertir en numérique pour des raisons de compatibilité entre plusieurs applis donc je fais un :

Citation:

---

CREATE VIEW ma_vue (v_col_num)
AS SELECT to_number(t_col_varchar) FROM ma_table WHERE EstNumerique(t_col_varchar)=1

---

EstNumerique étant une fonction me permettant d'exclure d'éventuelles données non numérique dans ma table d'origine

Jusque là tout va bien (la colonne v_col_num étant bien en format numérique)

Si je fais un :

Citation:

---

SELECT * FROM ma_vue

---

pas de problème mes données sont OK

par contre dés que je fais un :

Citation:

---

SELECT * FROM ma_vue WHERE v_col_num=99999

---

J'ai une erreur ORA-01722: Nombre non valide

Je précise que la syntaxe

Citation:

---

SELECT * FROM ma_vue WHERE v_col_num='99999'

---

renvoit la même erreur.

Je ne vois vraiment pas où ça coince mais j'ai sûrement loupé quelque chose.

Merci d'avance pour votre aide.

NM

Tout simplement parce que cette requête-ci :

Code:

---

1 2 3

SELECT * FROM ma_vue WHERE v_col_num = 99999

---

Est transformée par Oracle en ceci :

Code:

---

1 2 3 4

SELECT to_number(t_col_varchar) FROM ma_table WHERE EstNumerique(t_col_varchar) = 1 AND to_number(t_col_varchar) = 99999

---

L'ordre des filtres dans le where n'est pas garanti.

Merci pour votre réponse mais du coup je ne comprends pas pourquoi cela coince puisque le where devrait exclure les valeurs non numériques non ?

Du coup y'a t'il une alternative dans la construction de la vue ?

Merci de m'accorder un peu de temps.

NM

Citation:

---

Envoyé par tegestobis

Merci pour votre réponse mais du coup je ne comprends pas pourquoi cela coince puisque le where devrait exclure les valeurs non numériques non ?

---

Non, comme je vous l'ai écrit l'ordre des filtres dans le where n'est pas garanti.
Ce qui signifie qu'Oracle peut tout à fait cherche à appliquer to_number(t_col_varchar) = 99999 avant EstNumerique(t_col_varchar) = 1, ce qui provoquera donc votre erreur !

Pour les solutions, par exemple construisez votre vue ainsi (à essayer) :

Code:

---

1 2 3

SELECT t_col_varchar , case EstNumerique(t_col_varchar) when 1 then to_number(t_col_varchar) end as t_col_number FROM ma_table

---

Bonjour,

Je viens de tester : ça marche nickel.

Merci beaucoup pour votre aide.

Vous pouvez interdire à Oracle de transformer votre requête en sous-main, ce qui permettra de traiter d'abord la vue avant d'y appliquer la condition 99999.

Code:

---

1 2	SELECT /*+ NO_QUERY_TRANSFORMATION */ * FROM ma_vue WHERE v_col_num=99999;

---

Cela dit, ça reste un infâme bricolage.