TO_DATE fonctionne dans un SELECT mais provoque une erreur ORA-01861 (littéral non concordant) dans un WHERE !

**lasyan3** · 16/03/2011, 14h33

Bonjour,

Je rencontre un souci avec les dates dans Oracle (je sais, c'est pas original).
J'ai la requête suivante :

Code :

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

1
2
3
4
5
6
7
SELECT   CV.VALUE,
         TO_DATE (CV.VALUE, 'YYYY/MM/DD  HH24:MI:SS') AS date_demande
  FROM      workcase w
         INNER JOIN
            case_variable CV
         ON CV.tcid = w.id AND CV.variable = 'ddi_sap_date_demande'
 WHERE   w.id = 306634

Qui me renvoie le résultat suivant :

Code :

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

1
2
VALUE = 2011/03/16  00:00:00
DATE_DEMANDE = 16/03/2011

Manifestement, la requête fonctionne donc (la colonne value est de type varchar2(2000) pour info).
Par contre, si je rajoute une clause where comme suit :

Code :

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

1
2
3
4
5
6
7
8
SELECT   CV.VALUE,
         TO_DATE (CV.VALUE, 'YYYY/MM/DD  HH24:MI:SS') AS date_demande
  FROM      workcase w
         INNER JOIN
            case_variable CV
         ON CV.tcid = w.id AND CV.variable = 'ddi_sap_date_demande'
 WHERE   w.id = 306634
         AND TO_DATE (CV.VALUE, 'YYYY/MM/DD  HH24:MI:SS') = SYSDATE

J'obtiens une erreur ORA-01861 !
Comment se fait-il qu'une telle erreur se produise dans le where, alors même que dans le select la conversion to_date fonctionne ?! Je veux dire, à partir du moment où la conversion du varchar2 en date fonctionne, pourquoi sa comparaison avec sysdate échoue ?
Merci d'avance.

**lasyan3** · 16/03/2011, 14h43

Envoyé par lasyan3

Manifestement, la requête fonctionne donc (la colonne value est de type varchar2(2000) pour info).

to_date formate une chaine en date (d'où son nom) et non l'inverse !

Invité · 16/03/2011, 14h58

Envoyé par lasyan3

to_date formate une chaine en date (d'où son nom) et non l'inverse !

Effectivement, j'ai écris une très belle c*nn*ri*...

Je vais reprendre un café et fermé ma bouche....

**lasyan3** · 17/03/2011, 09h40

Malheureusement, cela ne change rien...

D'ailleurs, je précise que si je retire toute la clause WHERE, la requête fonctionne, le to_date renvoie null si la valeur de cv.value est null aussi.

Je précise aussi que je dispose de 3 bases : une de test, une de prod et une perso.
Sur la test, j'ai l'erreur ORA-1861.
Sur la prod, c'est une erreur ORA-1860 (input value not long enough for date format).
Sur ma base perso, par contre, je n'ai aucune erreur !

J'ai comparé le contenu de la table nls_session_parameters sur les 3 bases, elles sont identiques.

De plus, voici les versions des bases :
Base de test : Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
Base de prod : Oracle9i Release 9.2.0.7.0 - 64bit Production
Base perso : Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

Est-ce que cela pourrait être lié au fait que les bases de test et de prod sont en 64bit ?

**mnitu** · 17/03/2011, 10h38

Est-ce qu’il y a un index sur CV.VALUE ?

**laurentschneider** · 17/03/2011, 10h54

quel est le résultat de

Code :

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

1
2
3
4
5
6
7
 
SELECT   CV.VALUE
  FROM      workcase w
         INNER JOIN
            case_variable CV
         ON CV.tcid = w.id AND CV.variable = 'ddi_sap_date_demande'
 WHERE   w.id = 306634

**lasyan3** · 17/03/2011, 10h58

laurentschneider > la requête renvoie le résultat suivant :

Code :

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

"2011/03/16  00:00:00"

mnitu > non, par contre il y a un index sur la colonne variable.

Merci.

**mnitu** · 17/03/2011, 11h09

Votre requête corresponde en fait à la requête suivante:

Code :

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

1
2
3
4
5
6
7
 
SELECT   CV.VALUE,
         TO_DATE (CV.VALUE, 'YYYY/MM/DD  HH24:MI:SS') AS date_demande
  FROM  case_variable CV
 WHERE   CV.tcid = 306634
   AND CV.variable = 'ddi_sap_date_demande'
   AND TO_DATE (CV.VALUE, 'YYYY/MM/DD  HH24:MI:SS') = SYSDATE

Il doit y avoir une valeur non-date dans la colonne CV.VALUE parmi les enregistrements qui ont CV.variable = 'ddi_sap_date_demande'

[Edit]
La requête suivante devrait se planter avec la même erreur

Code :

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

1
2
3
4
5
 
SELECT   CV.VALUE,
         TO_DATE (CV.VALUE, 'YYYY/MM/DD  HH24:MI:SS') AS date_demande
  FROM  case_variable CV
 WHERE  CV.variable = 'ddi_sap_date_demande'

[/EDIT]

**lasyan3** · 17/03/2011, 11h19

Envoyé par mnitu

La requête suivante devrait se planter avec la même erreur

Code :

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

1
2
3
4
SELECT   CV.VALUE,
         TO_DATE (CV.VALUE, 'YYYY/MM/DD  HH24:MI:SS') AS date_demande
  FROM  case_variable CV
 WHERE  CV.variable = 'ddi_sap_date_demande'

[/EDIT]

Justement, non, elle fonctionne ! C'est ce que je disais au début, le select fonctionne sans problème, mais le where plante ! C'est ça qui est énorme !

Envoyé par mnitu

Il doit y avoir une valeur non-date dans la colonne CV.VALUE parmi les enregistrements qui ont CV.variable = 'ddi_sap_date_demande'

A part des valeurs à null, toutes les valeurs sont du bon format, j'ai vérifié.

Envoyé par mnitu

Votre requête corresponde en fait à la requête suivante:

Code :

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

1
2
3
4
5
6
SELECT   CV.VALUE,
         TO_DATE (CV.VALUE, 'YYYY/MM/DD  HH24:MI:SS') AS date_demande
  FROM  case_variable CV
 WHERE   CV.tcid = 306634
   AND CV.variable = 'ddi_sap_date_demande'
   AND TO_DATE (CV.VALUE, 'YYYY/MM/DD  HH24:MI:SS') = SYSDATE

Alors là c'est fort : cette requête fonctionne ! Ce serait lié à la jointure ?! WTF !!!

**mnitu** · 17/03/2011, 11h33

Et cette requête ça fonctionne ?

Code :

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

1
2
3
4
5
 
SELECT   CV.VALUE,
         TO_DATE (CV.VALUE, 'YYYY/MM/DD  HH24:MI:SS') AS date_demande
  FROM  case_variable CV
 WHERE  CV.tcid = 306634

**lasyan3** · 17/03/2011, 12h49

Non, mais c'est normal car la table CASE_VARIABLE contient des valeurs de variables, donc elle possède au moins 2 colonnes : "variable" qui contient le nom de la variable, et "value" qui contient sa valeur.
Dans mes requetes, je me limite à la variable "ddi_sap_date_demande" car c'est elle que je veux récupérer dans la table.
Sans cette condition, je vais récupérer toutes les variables de la table, dont celles qui ne sont pas des dates (d'où l'erreur).

**laurentschneider** · 17/03/2011, 13h07

bien sûr donc, rajoute sap au case

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT   CV.VALUE,
         CASE WHEN w.id = 306634 AND CV.variable = 'ddi_sap_date_demande'
THEN
             TO_DATE (CV.VALUE, 'YYYY/MM/DD  HH24:MI:SS') 
         END AS date_demande
  FROM      workcase w
         INNER JOIN
            case_variable CV
         ON CV.tcid = w.id AND CV.variable = 'ddi_sap_date_demande'
 WHERE   w.id = 306634
         AND CASE WHEN w.id = 306634 AND CV.variable = 'ddi_sap_date_demande'
THEN
             TO_DATE (CV.VALUE, 'YYYY/MM/DD  HH24:MI:SS') 
         END = SYSDATE

**mnitu** · 17/03/2011, 13h11

Dans le cas où l’optimiseur accède aux donnés via la colonne tcid et ensuite filtre avec to_date il y a plantage.
Dans le cas ou il prend le chemin qui accède ou filtre d’abord via la condition CV.variable = 'ddi_sap_date_demande' il n’y a plus d’anomalie.
Reprenez la proposition fait par laurent mais au lieu de tester w.id = 306634 dans le CASE testez variable = 'ddi_sap_date_demande'

**lasyan3** · 17/03/2011, 13h19

La dernière requête de laurent fonctionne.

A noter aussi que si je déplace la condition "AND CV.variable = 'ddi_sap_date_demande'" du inner join dans le where (sans utiliser de case), ça fonctionne aussi.

Il y a des chose qui m'échappent dans cette histoire, mais au moins je ne suis plus bloqué. Merci à tous !

**mnitu** · 17/03/2011, 13h22

Envoyé par lasyan3

...Il y a des chose qui m'échappent dans cette histoire, mais au moins je ne suis plus bloqué. Merci à tous !

Pourtant je viens de vous l'expliquer.
Dans la requête proposé par laurent le case sur w.id CASE WHEN w.id = 306634 ne sert à rien en fait.

**lasyan3** · 17/03/2011, 13h34

Oui, l'important était de filtrer sur le nom de la variable, afin d'être certain de ne récupérer que les valeurs pouvant être converties en date.

Je ne pensais simplement pas qu'il était nécessaire de refiltrer à nouveau dans le WHERE, vu qu'il y a déjà un filtre sur la jointure, et que le SELECT fonctionne sans CASE.

**mnitu** · 17/03/2011, 13h49

Oracle transforme votre requête comme bon il lui semble.
Dans ce cas écrivez votre requête ainsi

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT   CV.VALUE,
         TO_DATE (CV.VALUE, 'YYYY/MM/DD  HH24:MI:SS') AS date_demande
  FROM      workcase w
         INNER JOIN
         (Select * from  case_variable where variable = 'ddi_sap_date_demande') CV
         ON CV.tcid = w.id 
 WHERE   w.id = 306634
         AND TO_DATE (CV.VALUE, 'YYYY/MM/DD  HH24:MI:SS') = SYSDATE

**laurentschneider** · 17/03/2011, 13h59

Mouais, la façon dont Oracle interprète les conditions "WHERE" et les jointures "ON" n'est vraiment pas glorieuse.

Avec le CASE, on est toujours plus tranquille, avec une clause where, ou sous-requête, un hint ou autre, ça peut toujours planter au prochain patch... Optimizer Tuning oblige

**lasyan3** · 17/03/2011, 14h02

C'est exactement la requête à laquelle je pensais, faire un inner join sur un select déjà filtré, mais vous voulez rire ? Ca ne marche pas ! J'ai une erreur ORA-01861.
Par contre, en ajoutant un CASE ou la condition sur "ddi_sap_date_demande" dans le WHERE avant le TO_DATE, ça refonctionne bien sur.

**mnitu** · 17/03/2011, 14h30

Oui je me suis dit après que

Oracle transforme votre requête comme bon il lui semble.

peut être aussi valable pour ma proposition.

TO_DATE fonctionne dans un SELECT mais provoque une erreur ORA-01861 (littéral non concordant) dans un WHERE !

SQL Oracle

Discussions similaires

Partager

Partager