Un CASE dans un WHERE

**CinePhil** · 30/01/2017, 09h28

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 :

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

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 ?

**mnitu** · 30/01/2017, 14h40

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.

**al1_24** · 30/01/2017, 15h55

Envoyé par CinePhil

Code :

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

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 :

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

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

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

Code :

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

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

**CinePhil** · 30/01/2017, 16h23

Pas bête !
Je ne pense jamais à ces combinaisons de logique pure pour simplifier la syntaxe.

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.

**CinePhil** · 30/01/2017, 16h56

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

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
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 ?

**mnitu** · 30/01/2017, 17h04

Le plus simple

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
 
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.

**CinePhil** · 30/01/2017, 17h08

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 :

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
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

**CinePhil** · 30/01/2017, 17h11

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 !

**alassanediakite** · 30/01/2017, 17h56

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

Code :

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

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 :

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

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

@+

**CinePhil** · 31/01/2017, 08h56

Envoyé par alassanediakite

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

Code :

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

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 :

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

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 :

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

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 !

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.

**mnitu** · 31/01/2017, 09h22

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.

**CinePhil** · 31/01/2017, 11h32

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 :

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

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

**mnitu** · 31/01/2017, 14h16

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.

**Waldar** · 31/01/2017, 14h38

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

**CinePhil** · 31/01/2017, 15h03

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

Un CASE dans un WHERE [11gR2]

SQL Oracle

Discussions similaires

Partager

Partager