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

[lasyan3]

Bonjour,

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

Code :

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 :

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 :

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]

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 !

[7gyY9w1ZY6ySRgPeaefZ]

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]

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]

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

[laurentschneider]

quel est le résultat de

Code :

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]

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

Code :

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

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

Merci.

[mnitu]

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

Code :

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 :

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]

Envoyé par mnitu

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

Code :

[/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 :

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

[mnitu]

Et cette requête ça fonctionne ?

Code :

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]

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]

bien sûr donc, rajoute sap au case

Code :

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]

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]

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]

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]

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]

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

Code :

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]

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]

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]

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.