Problème CASE avec multiples conditions dont IS NULL

Bonjour,

Je souhaite tester si COLONNE1 et COLONNE2 sont vides ou contiennent un espace ' '

J'ai testé cette requête mais j'ai une erreur.
C'est apparemment le IS NULL qui pose problème car si je le remplace par 'abc' ça fonctionne.

Code:

---

1 2	UPDATE MA_TABLE_TEST SET COLONNE1 = ( CASE COLONNE2 WHEN 'VAR1' THEN CASE WHEN COLONNE3 in (IS NULL,' ') THEN 'OK1' ELSE 'OK2' END WHEN 'VAR2' THEN CASE WHEN COLONNE3 in (IS NULL,' ') THEN 'OK3' ELSE 'OK4' END END )

---

Pourriez-vous m'aider svp ?

Merci

Code:

---

CASE WHEN COLONNE3 IS NULL OR COLONNE3 = ' ' THEN 'OK1'

---

Citation:

---

Envoyé par 7gyY9w1ZY6ySRgPeaefZ

Code:

---

CASE WHEN COLONNE3 IS NULL OR COLONNE3 = ' ' THEN 'OK1'

---

---

Merci !
Ça fonctionne maintenant

Tu peux aussi utiliser cette syntaxe :

Code:

---

1 2	CASE WHEN coalesce(COLONNE3, ' ') = ' ' THEN 'OK1'

---

L'avantage c'est que tu peux indexer coalesce(COLONNE3, ' ') et du coup la condition devient sargable.

Dans le cas present ISNULL suffit et est sans doute meme plus performant :-)

Code:

---

CASE WHEN ISNULL(COLONNE3, ' ') = ' ' THEN 'OK1'

---

Comme ça tu as deux possibilités
L'avantage que peut avoir le COALESCE c'est qu'il permet les parametres en cascade

Code:

---

COALESCE(COLONNE3,COLONNE2,COLONNE1,' ')=' '

---

Le premier non NULL à gagné :-)

Citation:

---

Envoyé par olibara

Dans le cas present ISNULL suffit et est sans doute meme plus performant :-)

---

Faudrait argumenter un peu plus, surtout pour la performance, parce que pour moi, c'est blanc bonnet et bonnet blanc.

Citation:

---

Envoyé par 7gyY9w1ZY6ySRgPeaefZ

Faudrait argumenter un peu plus, surtout pour la performance, parce que pour moi, c'est blanc bonnet et bonnet blanc.

---

"On" m'a déjà dit (SQLPro faisait partie du "on") la même chose.

Le ISNULL(), spécifique à SQL Server, serait plus optimisé et parfois changerait même complètement le plan d'exécution.
J'avoue ne pas comprendre non plus comment SQL Server peut ne pas considérer COALESCE(arg1, arg2) comme un simple alias de ISNULL(arg1, arg2).

Je suppose, mais sans en être complètement certain, qu'il existe un ou plusieurs cas où les deux fonctions ne font pas la même chose :
- mixité des types
- résultat forcément non null
- autre ?

L'exemple ci-dessous (avec SQL Server 2016 Express) montre effectivement une différence de comportement avec les arguments NULL :

Code:

---

1 2 3 4 5 6 7 8

select isnull(1, 2); select isnull(null, null); select isnull(1, 'a');   select coalesce(1, 2); select coalesce(null, null); select coalesce(1, 'a');

---

Msg 4127, Level 16, State 1, Line 6
Au moins un des arguments de COALESCE doit être une expression qui n'est pas la constante NULL.

Le ISNULL(null, null) ne pose pas de problème pourtant. Visiblement, COALESCE() a un type de retour "not null".

Pourtant, l'exemple suivant passe sans problème :

Code:

---

1 2 3 4 5 6

declare @a int; declare @b int; set @a = null; set @b = null; select coalesce(@a, @b);

---

Donc il faut au moins un argument littéral non null, mais si c'est une variable (ou une colonne, j'imagine) ça passe sans problème avec que des null...
Très étrange...

Ah, ou alors j'ai compris ! C'est une histoire de type !

Il faut au moins une variable typée (donc différence de la valeur littérale "null" qui n'est pas typée) pour que coalesce sache quel type retourner.
Pourtant, passer des arguments de type différent ne le fait pas sauter...
Étrange...

Code:

---

1 2 3 4 5 6 7

declare @a int; declare @b varchar; set @a = 1; set @b = 'a'; select coalesce(@a, @b); select isnull(@a, @b);

---

Ça marche

Code:

---

1 2 3 4 5 6 7

declare @a int; declare @b varchar; set @a = 1; set @b = null; select coalesce(@a, @b); select isnull(@a, @b);

---

Ca marche aussi

Code:

---

1 2 3 4 5 6 7

declare @a int; declare @b varchar; set @a = null; set @b = 'a'; select coalesce(@a, @b); select isnull(@a, @b);

---

Ca plante pour isnull aussi bien que pour coalesce...

J'avoue ne pas comprendre la logique profonde du truc...

Merci bien de l'explication !
Il faudrait que MS fasse un gros ménage dans les fonctions SQL Server, il y a tellement de vielles fonctions bancales toujours en activité.
C'est là qu'Oracle a encore une longueur d'avance avec des librairies bien plus solides et plus fournies mais pas non plus complètement au dessus de tout soupçon - j'ai déjà débuggé et corrigé une de leur fonction...

Tu rigoles ou quoi ? C'est farci de fonctions redondantes propriétaires imbitables à commencer par les TO_machin..... !

A +

bonjour,

Citation:

---

Envoyé par StringBuilder

Le ISNULL(), spécifique à SQL Server, serait plus optimisé et parfois changerait même complètement le plan d'exécution.

---

C'est en effet le cas.

Citation:

---

Envoyé par StringBuilder

J'avoue ne pas comprendre non plus comment SQL Server peut ne pas considérer COALESCE(arg1, arg2) comme un simple alias de ISNULL(arg1, arg2).

---

Justement parce que les deux fonctions ne sont pas tout à fait identiques même si elles adressent le même besoin.

Citation:

---

Envoyé par StringBuilder

Je suppose, mais sans en être complètement certain, qu'il existe un ou plusieurs cas où les deux fonctions ne font pas la même chose :
- mixité des types
- résultat forcément non null
- autre ?

---

Le type de retour n'est en effet pas le même.
- ISNULL renvoie le type du premier argument
- COALESCE renvoie le type de l'argument ayant la plus forte précédence :

Code:

---

1 2 3 4

DECLARE @a VARCHAR(3) DECLARE @b VARCHAR(4) = 'toto'   SELECT COALESCE(@a, @b) AS r_coalesce, ISNULL(@a, @b) as r_isnull

---

==>

r_coalesce	r_isnull
toto	        tot

Il existe également un différence de comportement notable :
Avec un COALESCE, les expressions sont interprétées une fois pour tester la nullité, et une deuxième fois pour renvoyer la valeur non NULL, ce qui n'est pas vraiment génant quand on teste une simple colonne, mais peut le devenir quand on fait appel a une sous requête, ou une fonction couteuse :

Code:

---

1 2 3 4 5 6 7 8 9 10

CREATE FUNCTION UDF_TEST () RETURNS INT AS BEGIN DECLARE @x int = 0; WHILE (@x < 1000000) SET @x += 1; RETURN 0;   END

---

Code:

---

SELECT ISNULL(dbo.udf_test(), 0)

---

 SQL Server \endash Temps d'exécution*: 
, Temps UC = 452*ms, temps écoulé = 587*ms.

Code:

---

SELECT COALESCE(dbo.udf_test(), 0)

---

 SQL Server \endash Temps d'exécution*: 
, Temps UC = 889*ms, temps écoulé = 1239*ms.

Cela peut même donner des résultats inattendus :

Code:

---

1 2 3 4 5 6 7 8

CREATE FUNCTION UDF_TEST (@i VARCHAR(50)) RETURNS INT AS BEGIN   RETURN CASE WHEN @i like '[0-8]%' THEN NULL ELSE 1 END;   END

---

Code:

---

SELECT COALESCE(dbo.udf_test(NEWID()), 0)

---

peut parfois renvoyer :

NULL

Alors qu'un ISNULL renverra toujours 0 ou 1;

Merci beaucoup pour cette explication parfaitement limpide :)

Reste à comprendre pourquoi COALESCE est aussi mal implémenté (car autant pour le type de retour, je comprends la différence, entre l'implémentation historique non standard, et l'implémentation normée, autant le coup de la double évaluation dénote une grave anomalie d'optimisation !)

Et surtout, comme indiqué avec l'appel à une fonction non déterministe, le résultat de COALESCE est tout simplement :
- faux (puisque ça ne ramène pas la valeur initiale, mais la valeur calculée une seconde fois)
- illogique (puisque ça peut ramener NULL alors que c'est justement fait pour l'éviter !)

Mon intégrisme du "tout normé" en prend un coup :D Je crois que les ISNULL() vont fleurir un peu partout dans mon code :D

non, en fait ce n'est ni faux, ni illogique. ça correspond juste à une certaine vérité avec une certaine logique :)

COALESCE n'est ni plus ni moins qu'une forme abrégée de CASE (au même titre que NULLIF) :
COALESCE(A,B,C,...) est un équivalent de CASE WHEN A IS NOT NULL THEN A ELSE COALESCE(b,c,...) END.

dés lors, le comportement devient en effet logique, au même titre que SELECT NEWID(), NEWID() renvoie deux valeurs différentes.

D'ailleurs, on n'obtiendra plus de NULL si l'on écrit :

Code:

---

1 2	SELECT COALESCE(val, 0) FROM (SELECT dbo.udf_test(NEWID()) AS val) T

---

Dans ce cas en effet, la fonction ne sera appelée qu'une fois pour la table dérivée.

Citation:

---

Envoyé par StringBuilder

Donc il faut au moins un argument littéral non null, mais si c'est une variable (ou une colonne, j'imagine) ça passe sans problème avec que des null...
Très étrange...

Ah, ou alors j'ai compris ! C'est une histoire de type !

---

Non, là aussi c'est une histoire de norme.
Comme COALESCE est en fait un CASE, il n'est pas admis de ne spécifier que des NULL comme résultat. COALESCE(NULL,NULL) est donc rejeté au même titre que :

Code:

---

1 2 3 4 5

SELECT CASE WHEN 1 = 2 THEN NULL WHEN 1 = 1 THEN NULL END

---