Vue Oracle et to_number [Résolu]

[tegestobis]

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 :

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 :

SELECT * FROM ma_vue

pas de problème mes données sont OK

par contre dés que je fais un :

SELECT * FROM ma_vue WHERE v_col_num=99999

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

Je précise que la syntaxe

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

[Waldar]

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.

[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 ?

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

Merci de m'accorder un peu de temps.

NM

[Waldar]

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

[tegestobis]

Bonjour,

Je viens de tester : ça marche nickel.

Merci beaucoup pour votre aide.

[Pomalaix]

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.