Discussion :

[StringBuilder]

Bonjour,

Version Oracle :

Oracle Database 10g Release 10.1.0.5.0 - Production

J'ai une table qui contient entre autres deux champs :
- DATFIN VARCHAR(8) qui contient une date au format YYYYMMDD
- HEUFIN VARCHAR(4) qui contient une heure au format HH24MI

La concaténation des deux me donne l'heure de fin d'un traitement.

Je cherche à trouver toutes les lignes dont la date de fin de traitement est inférieure à sysdate moins 2 minutes (traitements terminés il y a moins de 2 minutes)

Voici une première requête qui me permet de retrouver tous les traitements avec mon calcul :

Code :

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

1
2
3
4
5
6
 
select ut_spl.batch, sysdate - to_date(ut_spl.datfin || ut_spl.heufin, 'YYYYMMDDHH24MI')
from ut_spl
where ut_spl.codsoc = 100
and ut_spl.st1 = 4
and ut_spl.datfin != ' ' and ut_spl.heufin != ' ';

Cette requête fonctionne parfaitement.
Je peux parcourir l'ensemble des lignes retournées sans lever la moindre exception.

Je modifie alors mon WHERE pour ne retourner que les lignes modifiés il y a moins de deux minutes :

Code :

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

1
2
3
4
5
6
7
 
select ut_spl.batch, sysdate - to_date(ut_spl.datfin || ut_spl.heufin, 'YYYYMMDDHH24MI')
from ut_spl
where ut_spl.codsoc = 100
and ut_spl.st1 = 4
and ut_spl.datfin != ' ' and ut_spl.heufin != ' '
and (sysdate - to_date(ut_spl.datfin || ut_spl.heufin, 'YYYYMMDDHH24MI')) <= 0.001388;

Et là c'est le drame : contre toute attente, je me prends dans la tête l'erreur suivante :

ORA-01841: (full) year must be between -4713 and +9999, and not be 0
01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0"
*Cause: Illegal year entered
*Action: Input year in the specified range

Pourtant, je n'ai pas de date fantaisiste. Et si j'en avais, la première requête provoquerait aussi une erreur !

Ké passa ?

Aussi, comment puis-je faire proprement et de façon performante ma comparaison avec "2 minutes" ? Parce que le 0.001388, ça fait un peu magic number sorti du chapeau...

[StringBuilder]

Un instant je me suis dis que le problème venait de l'évaluation des dates qui se faisait avant le contrôle != ' ' sur datfin et heufin.

J'ai donc fais cette requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
select s.batch, sysdate - to_date(s.datfin || s.heufin, 'YYYYMMDDHH24MI')
from (
  select ut_spl.batch, ut_spl.datfin, ut_spl.heufin
  from ut_spl
  where ut_spl.codsoc = 100
  and ut_spl.st1 = 4
  and ut_spl.datfin != ' ' and ut_spl.heufin != ' '
) s
where (sysdate - to_date(s.datfin || s.heufin, 'YYYYMMDDHH24MI')) <= 0.001388
;

Mais rien n'y fait, toujours la même erreur !

[StringBuilder]

Contrôle de la validité de mes dates :

Code :

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

1
2
3
4
5
6
 
select max(datfin), max(heufin), min(datfin), min(datfin), max(length(datfin)), max(length(heufin)), min(length(datfin)), min(length(heufin))
from ut_spl
where ut_spl.codsoc = 100
and ut_spl.st1 = 4
and ut_spl.datfin != ' ' and ut_spl.heufin != ' ';

20120220 2355 20010626 20010626 8 4 8 4

Aucune date ne semble donc hors range ou dans un format invalide...

[StringBuilder]

J'hallucine !

C'est bien un problème d'évaluation de la date et mes dates vides qui pose problème !

Et Oracle n'en a rien à secouer de ma sous-requête, il décide quand même d'évaluer le to_date avant de vérifier que la date n'est pas vide...

En faisant un min() et un having, je m'en sors...

Si ça peut servir à quelqu'un d'autre...

Code :

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

1
2
3
4
5
6
7
8
 
select ut_spl.batch
from ut_spl
where ut_spl.codsoc = 100
and ut_spl.st1 = 4
and ut_spl.datfin != ' ' and ut_spl.heufin != ' '
group by ut_spl.batch
having min(sysdate - to_date(ut_spl.datfin || ut_spl.heufin, 'YYYYMMDDHH24MI')) <= 0.001388;

Heureusement que dans mon cas précis, j'ai juste besoin de récupérer la liste des batch qui sont concernés...

[mnitu]

J'hallucine !

C'est bien un problème d'évaluation de la date et mes dates vides qui pose problème !

Et Oracle n'en a rien à secouer de ma sous-requête, il décide quand même d'évaluer le to_date avant de vérifier que la date n'est pas vide...

...

Pour paraphraser une loi de Murhy : dans des conditions bien déterminées de température, pression, humidité et autre variables l’optimiseur d’Oracle fait ce que lui bon semble.
Cherchez dans cette section les autres discussions sur ce problème pour voir des autres solutions.

[Waldar]

Encore un exemple du pourquoi on utilise les bons types de données.
Enfin si je me rappelle d'autres sujets, c'est un progiciel et vous n'avez pas vraiment le choix.

La bonne solution à mon avis c'est de traiter les "vides" dans une seule formule :

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
With TMP as
(
select 1 as tid, '20120220' as datfin, '2040' as heufin from dual union all
select 2       , ' '                 , '1912'           from dual union all
select 3       , '20120220'          , ' '              from dual union all
select 4       , ' '                 , ' '              from dual
)
select tid, datfin, heufin
     , to_date(coalesce(trim(datfin), '99991231') ||  coalesce(trim(heufin), '2359'), 'YYYYMMDDHH24MI') as dt
  from TMP;
 
       TID DATFIN   HEUFIN DT                 
---------- -------- ------ -------------------
         1 20120220 2040   2012-02-20 20:40:00
         2          1912   9999-12-31 19:12:00
         3 20120220        2012-02-20 23:59:00
         4                 9999-12-31 23:59:00

[lola06]

Bonjour,

Une petit remarque: dans SQL on utilise <> pour la différence et pas !=

[StringBuilder]

Pourquoi ?
Les deux syntaxes sont bien équivalentes et faisant partie de la norme non ?

[lola06]

Il me semble que la norme est bien <> et pas !=