Discussion :

[ZashOne]

bonjour
j ai une table qui contient un champ varchar2 sensé représenté une date mais certaine données tres vieille sont sous une mauvaise forme (ex : 32/02/1994).

J ai ecrit une fonction qui verifie si la valeur est convertible en date et qui s appele IS_VALID_VAR_DATE.

Le probleme c'est que si je lance la requete

Code :

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

select count(*) from (select * from mytable where is_valid_var_date(macolonne) = 1 and macolonne is not null) myview where to_date(macolonne,dd/mm/yyyy) < sysdate)

Je me retrouve avec une erreur Ora-1839.

Apres avoir fait un explain plan je voi que Oracle a joint les 3 filtres sur macolonne en un filtre.

J' ai essayé le HINT NO_MERGE mais j ai le meme filtre. il a juste rajouté une vue a partir de la table.

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
 
----------------------------------------------------------------------------------------
| Id  | Operation           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                  |     1 |    27 |   515   (2)| 00:00:07 |
|   1 |  SORT AGGREGATE     |                  |     1 |    27 |            |          |
|   2 |   VIEW              |                  |   141 |  3807 |   515   (2)| 00:00:07 |
|*  3 |    TABLE ACCESS FULL| MATABLE          |   141 |  1551 |   515   (2)| 00:00:07 |
----------------------------------------------------------------------------------------
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
--------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(TO_NUMBER(TO_CHAR(TO_DATE("MATABLE"."MACOLONNE",'dd/mm/yyyy'
              ),'yyyymmdd'))<20090901 AND "IS_VALID_VAR_DATE"("MACOLONNE")=1 AND "MACOLONNE" IS
              NOT NULL)

Etant en oracle 11.1 , le hint ORDERED_PREDICATES a disparu.

Quelqu'un a t'il une idée ?

Merci

[Invité]

pourquoi tu ne modifierais pas ta fonction pourqu'elle te renvoie une date si l'entré est valide et null sinon ?
du coup, tu aurais juste

Code :

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

WHERE isdate(macolonne) < sysdate

avec la fonction suivante :

Code :

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

1
2
3
4
5
6
7
8
9
10
create or replace
function isDate(x in varchar2) return date as 
   dt   date; 
begin 
   dt := to_date(x, 'DD/MM/YYYY'); 
   return dt; 
exception 
   when others then 
      return null; 
end;

[ZashOne]

ha oui c est une bonne idée , sinon de mon coté j ai trouvé aussi la possibilité de rajouté

Code :

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

and rownum < x

dans la sous requete avec x etant plus grand que le nombre de ligne par exemple 10000000000000000

Cela a pour effet dans de rajouter une etape STOP KEY dans l explain plan de la sorte que le filtre fusionné sur macolonne est coupé en deux suivant sa position dans la requete.

Par contre j'aimerai bien savoir si il y a un hint ou autre truc moins bricolage qui permet de contourner ce type de probleme que ce soit des dates du numerique (to_number) ou autre.

[mnitu]

Je pense qu’utiliser un hint ou une bricole pour ce type de problème est plutôt une mauvaise idée.

[Laurent_du_78]

Je pense qu’utiliser un hint ou une bricole pour ce type de problème est plutôt une mauvaise idée.

+1 comme on dit.

C'est un problème a traiter de façon fonctionnelle et pas avec des artifices de prédicats.

[pacmann]

Salut !

Dans le même genre d'idée, tu peux utiliser CASE :

Code :

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

1
2
 
WHERE CASE isvaliddate(macolonne) WHEN 1 THEN to_date(macolonne,dd/mm/yyyy) ELSE SYSDATE + 1 END < sysdate

[laurentschneider]

peut-être plus simple de comparer des caractères

Code :

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

1
2
3
select count(*) from t 
where substr(macolonne,7)||substr(macolonne,4,2)||substr(macolonne,1,2) 
< to_char(sysdate,'YYYYMMDD')

ou alors avec une petite regexp en calendrier 1901-2099

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT count(*) 
FROM t 
WHERE 
case when REGEXP_LIKE (macolonne, 
  '^((0[1-9]|1[0-9]|2[0-8])/(0[1-9]|1[0-2])/\d{4}|'||
  '(29|30)/(0[13-9]|1[0-2])/\d{4}|'||
  '31/(0[13578]|1[02])/\d{4}|'||
  '29/02/\d{2}([02468][048]|[13579][26]))')
then case when to_date(macolonne,'dd/mm/yyyy') < sysdate then 1
end end = 1

[ZashOne]

merci pour toute vos reponses,

pfff j ai un petit pocket regular expression et il n'y a meme pas 29/02/\d{2}([02468][048]|[13579][26]))
dedans.