Un CASE dans un WHERE

Version imprimable

Bonjour,

Lié à mon problème de MERGE, j'ai essayé ce type de requête pour voir si une mise à jour de la table par rapport à sa référence est nécessaire :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 SELECT * FROM PLEMENAGER.TYPE_POPULATION rf WHERE NOT EXISTS ( SELECT * FROM GRHUM.TYPE_POPULATION act WHERE act.C_TYPE_POPULATION = rf.C_TYPE_POPULATION AND act.LC_TYPE_POPULATION = rf.LC_TYPE_POPULATION AND act.LL_TYPE_POPULATION = rf.LL_TYPE_POPULATION AND CASE WHEN act.REF_REGLEMENTAIRE IS NULL AND rf.REF_REGLEMENTAIRE IS NULL THEN TRUE ELSE FALSE END AND act.TEM_FONCTIONNAIRE = rf.TEM_FONCTIONNAIRE AND act.TEM_ATOS = rf.TEM_ATOS AND act.TEM_ITARF = rf.TEM_ITARF AND act.TEM_ENS_SUP = rf.TEM_ENS_SUP AND act.TEM_ENSEIGNANT = rf.TEM_ENSEIGNANT AND act.TEM_2DEGRE = rf.TEM_2DEGRE AND act.TEM_HOSPITALIER = rf.TEM_HOSPITALIER AND act.TEM_BIBLIO = rf.TEM_BIBLIO AND act.TEM_CARRIERE = rf.TEM_CARRIERE AND act.D_CREATION = rf.D_CREATION AND act.D_MODIFICATION = rf.D_MODIFICATION AND act.TEM_1DEGRE = rf.TEM_1DEGRE AND act.TEM_VISIBLE = rf.TEM_VISIBLE AND CASE WHEN act.D_OUVERTURE IS NULL AND rf.D_OUVERTURE IS NULL THEN TRUE ELSE FALSE END AND CASE WHEN act.D_FERMETURE IS NULL AND rf.D_FERMETURE IS NULL THEN TRUE ELSE FALSE END );
J'ai utilisé des case parce que je ne pouvais pas utiliser l'égalité sur les colonnes contenant des NULL (en logique SQL, NULL <> NULL).

Mais ORacle renvoie cette erreur :

Citation:

ORA-00920: opérateur relationnel non valide
00920. 00000 - "invalid relational operator"
*Cause:
*Action:
Erreur à la ligne 11, colonne 5

Et la ligne 11 colonne 5, c'est le début de la ligne qui suit celle contenant le premier CASE.
J'ai essayé d'encadrer le CASE avec des parenthèses mais ça ne change rien.

Que c'est qui ne va pas dans ma requête ?

30/01/2017, 14h40
mnitu

Le case bien sûr. Comme je ne vois pas bien ce que vous voulez faire peut être que vous pouvez lire ce topique sur Ask Tom.
30/01/2017, 15h55
al1_24
Citation:
Envoyé par CinePhil

Code:

1 2 3 (...) AND CASE WHEN act.REF_REGLEMENTAIRE IS NULL AND rf.REF_REGLEMENTAIRE IS NULL THEN TRUE ELSE FALSE END (...);
Ça ne pourrait pas simplement s'écrire ainsi, expression qui sera fausse dès que l''un des deux est non null ? :

Code:

AND act.REF_REGLEMENTAIRE IS NULL AND rf.REF_REGLEMENTAIRE IS NULL

Sinon, si tu tiens vraiment au CASE, tu peux aussi faire :

Code:

AND CASE WHEN act.REF_REGLEMENTAIRE IS NULL AND rf.REF_REGLEMENTAIRE IS NULL THEN 1 ELSE 0 END = 1

;)
30/01/2017, 16h23
CinePhil

Pas bête !
Je ne pense jamais à ces combinaisons de logique pure pour simplifier la syntaxe. :D
Merci, je vais essayer ça.

EDIT :
Pas tout à fait bon encore, cependant parce que si les deux colonnes sont non nulles, la ligne est retournée alors qu'elle ne devrait pas. :(

EDIT 2 :
Avec ta solution du CASE se terminant par = 1, ça ne fonctionne pas non plus. J'ai d'ailleurs du mal à comprendre la syntaxe, du coup. :?

Avec ton idée de CASE = 1, j'ai fait cette requête :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 SELECT * FROM PLEMENAGER.TYPE_POPULATION rf WHERE NOT EXISTS ( SELECT * FROM GRHUM.TYPE_POPULATION act WHERE act.C_TYPE_POPULATION = rf.C_TYPE_POPULATION AND act.LC_TYPE_POPULATION = rf.LC_TYPE_POPULATION AND act.LL_TYPE_POPULATION = rf.LL_TYPE_POPULATION AND (CASE WHEN act.REF_REGLEMENTAIRE IS NULL AND rf.REF_REGLEMENTAIRE IS NULL THEN 1 ELSE 0 END) = 1 AND act.TEM_FONCTIONNAIRE = rf.TEM_FONCTIONNAIRE AND act.TEM_ATOS = rf.TEM_ATOS AND act.TEM_ITARF = rf.TEM_ITARF AND act.TEM_ENS_SUP = rf.TEM_ENS_SUP AND act.TEM_ENSEIGNANT = rf.TEM_ENSEIGNANT AND act.TEM_2DEGRE = rf.TEM_2DEGRE AND act.TEM_HOSPITALIER = rf.TEM_HOSPITALIER AND act.TEM_BIBLIO = rf.TEM_BIBLIO AND act.TEM_CARRIERE = rf.TEM_CARRIERE AND act.D_CREATION = rf.D_CREATION AND act.D_MODIFICATION = rf.D_MODIFICATION AND act.TEM_1DEGRE = rf.TEM_1DEGRE AND act.TEM_VISIBLE = rf.TEM_VISIBLE AND (CASE WHEN act.D_OUVERTURE IS NULL AND rf.D_OUVERTURE IS NULL THEN 1 ELSE 0 END) = 1 AND (CASE WHEN act.D_FERMETURE IS NULL AND rf.D_FERMETURE IS NULL THEN 1 ELSE 0 END) = 1 ) ORDER BY rf.C_TYPE_POPULATION;
J'obtiens toutes les lignes de la table de référence (rf). :?

Si je commente toutes les lignes du WHERE sauf la première, j'obtiens zéro ligne, ce qui est normal.

Si je décommente ensuite toutes les lignes du WHERE sauf celles concernant des colonnes de date, j'ai toujours zéro lignes.

Si je décommente AND act.D_CREATION = rf.D_CREATION, j'obtiens 4 lignes de résultat, dont 3 ne devraient pas apparaître car les dates sont identiques dans les deux tables ! :(

Et si je continue à décommenter en finissant par les CASE, je finis par avoir toutes les lignes en résultat. :(

Y aurait-il un problème, chez Oracle, avec la comparaison des dates ?

EDIT : En fait, je m'aperçois que même sans les CASE, ma requête me donne un mauvais résultat mais je ne sais pas pourquoi ! Faut-il explicitement convertir avec ces horribles TO_CHAR(TO_DATE pour avoir une comparaison juste ? :weird:

Le plus simple

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
 
SQL> create table emp1 as select * from emp
  2  /
Table created
 
SQL> select *
  2  from emp
  3  minus
  4  select * 
  5  from emp1
  6  /
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 
SQL>

Les zones de type date chez Oracle contient toujours l'heure aussi.

Bon, je finis par arriver à un résultat semble t-il satisfaisant avec cette requête que je trouve tirée par les cheveux !

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
SELECT *
FROM PLEMENAGER.TYPE_POPULATION rf
WHERE NOT EXISTS
(
  SELECT *
  FROM GRHUM.TYPE_POPULATION act
  WHERE act.C_TYPE_POPULATION = rf.C_TYPE_POPULATION
    AND act.LC_TYPE_POPULATION = rf.LC_TYPE_POPULATION
    AND act.LL_TYPE_POPULATION = rf.LL_TYPE_POPULATION
    AND (
        CASE 
            WHEN act.REF_REGLEMENTAIRE IS NULL AND rf.REF_REGLEMENTAIRE IS NULL THEN 1 
            WHEN act.REF_REGLEMENTAIRE = rf.REF_REGLEMENTAIRE THEN 1
            ELSE 0 
        END
    ) = 1
    AND act.TEM_FONCTIONNAIRE = rf.TEM_FONCTIONNAIRE
    AND act.TEM_ATOS = rf.TEM_ATOS
    AND act.TEM_ITARF = rf.TEM_ITARF
    AND act.TEM_ENS_SUP = rf.TEM_ENS_SUP
    AND act.TEM_ENSEIGNANT = rf.TEM_ENSEIGNANT
    AND act.TEM_2DEGRE = rf.TEM_2DEGRE
    AND act.TEM_HOSPITALIER = rf.TEM_HOSPITALIER
    AND act.TEM_BIBLIO = rf.TEM_BIBLIO
    AND act.TEM_CARRIERE = rf.TEM_CARRIERE
    AND TO_CHAR(act.D_CREATION) = TO_CHAR(rf.D_CREATION)
    AND TO_CHAR(act.D_MODIFICATION) = TO_CHAR(rf.D_MODIFICATION)
    AND act.TEM_1DEGRE = rf.TEM_1DEGRE
    AND act.TEM_VISIBLE = rf.TEM_VISIBLE
    AND 
    (
        CASE 
            WHEN act.D_OUVERTURE IS NULL AND rf.D_OUVERTURE IS NULL THEN 1 
            WHEN TO_CHAR(act.D_OUVERTURE) = TO_CHAR(rf.D_OUVERTURE) THEN 1
            ELSE 0 
        END
    ) = 1
    AND 
    (
        CASE 
            WHEN act.D_FERMETURE IS NULL AND rf.D_FERMETURE IS NULL THEN 1 
            WHEN TO_CHAR(act.D_FERMETURE) = TO_CHAR(rf.D_FERMETURE) THEN 1
            ELSE 0 
        END
    ) = 1
)
ORDER BY rf.C_TYPE_POPULATION

30/01/2017, 17h11
CinePhil

Je n'avais pas pensé non plus au MINUS mais il me retourne 4 lignes dont 3 qui, a priori, ne devraient pas être retournées, sous réserve de vérification plus approfondie.

EDIT : Je confirme que trois lignes ne devraient pas être retournées. :(
Dommage, c'était beaucoup plus simple !

Salut
coalesce ne peut-il pas faire l'affaire?
C'est à dire, remplacer...

Code:

1
2
3
4
5
6
7
AND (
        CASE 
            WHEN act.REF_REGLEMENTAIRE IS NULL AND rf.REF_REGLEMENTAIRE IS NULL THEN 1 
            WHEN act.REF_REGLEMENTAIRE = rf.REF_REGLEMENTAIRE THEN 1
            ELSE 0 
        END
    ) = 1

par

Code:

AND coalesce(act.REF_REGLEMENTAIRE,0)=coalesce(rf.REF_REGLEMENTAIRE,0)

Citation:
Envoyé par alassanediakite
Salut
coalesce ne peut-il pas faire l'affaire?
C'est à dire, remplacer...
Code:

1 2 3 4 5 6 7 AND ( CASE WHEN act.REF_REGLEMENTAIRE IS NULL AND rf.REF_REGLEMENTAIRE IS NULL THEN 1 WHEN act.REF_REGLEMENTAIRE = rf.REF_REGLEMENTAIRE THEN 1 ELSE 0 END ) = 1
par

Code:

AND coalesce(act.REF_REGLEMENTAIRE,0)=coalesce(rf.REF_REGLEMENTAIRE,0)

@+
Piste intéressante mais voici ce que j'obtiens avec cette requête :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 SELECT * FROM PLEMENAGER.TYPE_POPULATION rf WHERE NOT EXISTS ( SELECT * FROM GRHUM.TYPE_POPULATION act WHERE act.C_TYPE_POPULATION = rf.C_TYPE_POPULATION AND act.LC_TYPE_POPULATION = rf.LC_TYPE_POPULATION AND act.LL_TYPE_POPULATION = rf.LL_TYPE_POPULATION AND COALESCE(act.REF_REGLEMENTAIRE, 0) = COALESCE(rf.REF_REGLEMENTAIRE, 0) AND act.TEM_FONCTIONNAIRE = rf.TEM_FONCTIONNAIRE AND act.TEM_ATOS = rf.TEM_ATOS AND act.TEM_ITARF = rf.TEM_ITARF AND act.TEM_ENS_SUP = rf.TEM_ENS_SUP AND act.TEM_ENSEIGNANT = rf.TEM_ENSEIGNANT AND act.TEM_2DEGRE = rf.TEM_2DEGRE AND act.TEM_HOSPITALIER = rf.TEM_HOSPITALIER AND act.TEM_BIBLIO = rf.TEM_BIBLIO AND act.TEM_CARRIERE = rf.TEM_CARRIERE AND TO_CHAR(act.D_CREATION) = TO_CHAR(rf.D_CREATION) AND TO_CHAR(act.D_MODIFICATION) = TO_CHAR(rf.D_MODIFICATION) AND act.TEM_1DEGRE = rf.TEM_1DEGRE AND act.TEM_VISIBLE = rf.TEM_VISIBLE AND COALESCE(act.D_OUVERTURE, 0) = COALESCE(rf.D_OUVERTURE, 0) AND COALESCE(act.D_FERMETURE, 0) = COALESCE(rf.D_FERMETURE, 0) ) ORDER BY rf.C_TYPE_POPULATION
Résultat :

Citation:

ORA-00932: types de données incohérents ; attendu : DATE ; obtenu : NUMBER
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
Erreur à la ligne 25, colonne 35

La ligne 25 est l'avant dernier COALESCE et la position 35 est le zéro du premier COALESCE de cette ligne. Décidément, les dates et Oracle, c'est chiant ! :aie:
J'ai essayé de mettre un horrible TO_CHAR sur les colonnes de date mais je me retrouve avec une erreur similaire, cette fois avec attendu CHAR et obtenu NUMBER.

Je conserve ma requête d'hier soir et je garde l'idée du COALESCE potentiellement pour d'autres tables.

Merci.

31/01/2017, 09h22
mnitu

Citation:

Envoyé par CinePhil

Je n'avais pas pensé non plus au MINUS mais il me retourne 4 lignes dont 3 qui, a priori, ne devraient pas être retournées, sous réserve de vérification plus approfondie.

EDIT : Je confirme que trois lignes ne devraient pas être retournées. :(
Dommage, c'était beaucoup plus simple !

Signe que vous faite une erreur.
Et je n'ai pas compris pourquoi vous comparez deux dates (probablement) via un To_Char() avec un format par défaut. Probablement que vous devez utiliser trunc pour vous débarrasser de la partie heure de la date.

J'interprète le mauvais résultat que j'obtiens comme un signe que les deux valeurs de dates (de la table de référence et de la table à mettre à jour) que je vois identiques dans SqlDeveloper (au format JJ/MM/AA) ne sont effectivement peut-être pas identiques à la précision au delà du jour près mais je n'ai pas besoin de la précision à l'heure, minute, seconde et pouillème de trois fois rien du tout de seconde. :) Je considère donc "visuellement" que ces dates sont identiques et que ces lignes à dates identiques ne sont pas à mettre à jour.

Quand je fais cette requête plus simple, j'obtiens 4 lignes dont 3 ont des dates "visuellement" identiques dans les deux colonnes D_CREATION_x. C'est quand même pas top, je trouve !
Code:

1 2 3 4 5 6 SELECT rf.C_TYPE_POPULATION, rf.D_CREATION D_CREATION_ref, act.D_CREATION D_CREATION_act FROM PLEMENAGER.TYPE_POPULATION rf INNER JOIN GRHUM.TYPE_POPULATION act ON act.C_TYPE_POPULATION = rf.C_TYPE_POPULATION WHERE rf.D_CREATION <> act.D_CREATION

31/01/2017, 14h16
mnitu

Sans offense "visuellement" on ne peut pas faire la différence entre un bug et une erreur de programmation.
Les fait est que les dates en Oracle ont toujours le composant temps! Et c'est à vous, le programmeur, de connaitre ce détail et de comprendre ses implications lors de l'écriture de vos requêtes.
Visuellement vous ne pouvez pas voir une date ; ce qui est affiché c'est le résultat de la transformation du contenu d'une zone de type date en chaine des caractères. Et cela se fait en appliquant un format par défaut. Si ce format ne corresponde pas à votre souhaite vous n'avez qu'à utiliser le format explicite qui vous convient.
31/01/2017, 14h38
Waldar

Citation:

Envoyé par CinePhil

que je vois identiques dans SqlDeveloper (au format JJ/MM/AA)

Il ne faut pas subir mais simplement modifier ce paramétrage par défaut :
Outils -> Préférences -> Base de données -> NLS -> Format de date : YYYY-MM-DD HH24:MI:SS
Idem pour les deux suivantes (timestamp et timestamp with timezone) : YYYY-MM-DD HH24:MI:SS.FF & YYYY-MM-DD HH24:MI:SS.FF TZR
31/01/2017, 15h03
CinePhil

Donc, effectivement, les deux dates ont une heure différente.