issue with CASE combined with an OR

Question

SELECT  
    Siren,
    CASE    WHEN Code_Juridique LIKE 'M%' AND Enseigne IS NOT NULL AND Enseigne <> '' --ok
                THEN 'Enseigne : ' + Enseigne
            WHEN (Sigle IS NULL OR Sigle ='')
                AND (Enseigne IS NULL OR Enseigne ='')
                THEN '' -- ok
            WHEN
                 (Sigle IS NOT NULL OR Sigle <> '' ) THEN 'Sigle : ' + Sigle
            ELSE 'Sigle / Enseigne : ' + Sigle + ' / ' + Enseigne
        END as SigleEnseigne1,
        Sigle,
        Enseigne,
        Code_Juridique
        FROM    #JohnJack

The code is straightforward.

Issue lies with the third when as you can see below

I should have have nothing on my 4th and 5th line, yet it is giving me Sigle :

What I want is to have the column SigleEnseigne1 on the 4th and 5th line , to be empty

Thanks for your insights

What are you expecting? The query seems to be acting as expected. Your query says that if Sigle IS NOT NULL OR Sigle <> ''. Well, Sigle is not null, so it fulfills a part of the OR which means that it passes that criteria. That's how OR works. — Tom H, Dec 23 '15 at 14:15
Hi @tom-h, the issue is on line 4, the column `sigle` is not empty ... — Andy K, Dec 23 '15 at 14:21
It would probably be useful if you gave the expected output for each case (or row). I think that I know what you're trying to do, but it's easier if I don't have to guess. — Tom H, Dec 23 '15 at 14:25
@TomH, For the column `SigleEnseigne1` on line 1, it is giving me the desired results. On line 2 as well. On line 3, this is the expected results. On line 4, I should have `NULL` or `''` yet I'm having `Sigle :`and on line 5, I should have `NULL` or `''`, yet I'm having `Sigle :` — Andy K, Dec 23 '15 at 14:27
I believe that shadow's answer will get you what you need (or close to it), so I've given that an upvote. If his answer doesn't work for some reason then I'll take a stab at it if it seems clear enough why his isn't working. — Tom H, Dec 23 '15 at 14:32
Why not do this ISNULL(sigle,'') != '' instead of (Sigle IS NULL OR Sigle ='') — Josh, Dec 23 '15 at 14:51
Of course it's not empty. If Sigle is blank (''), it fulfills the 3rd WHEN in your CASE, because it's not null. So you should expect it to give you `Sigle :`. — Tab Alleman, Dec 23 '15 at 14:58
But you used an OR, so it doesn't matter if you have `Sigle <> ''`. If Sigle is NOT NULL, then the whole OR expression is true, so the 3rd WHEN is returned by the CASE expression. Did you mean to use AND instead of OR? — Tab Alleman, Dec 23 '15 at 15:02
@TabAlleman If there is anything on the column `Sigle`, I want to see `Sigle :`. If there is nothing (e.g NULL OR ''), I want to see nothing — Andy K, Dec 23 '15 at 15:06
@taballeman When doing that, I have `Sigle / Enseigne : / SIM` for the 4th line — Andy K, Dec 23 '15 at 15:11
Thanks to the closing snipers, btw. I only got love for you :) — Andy K, Dec 23 '15 at 15:12
I think you must have some special invisible characters in your Sigle column then. http://stackoverflow.com/questions/8655909/whats-the-best-way-to-identify-hidden-characters-in-the-result-of-a-query-in-sq — Tab Alleman, Dec 23 '15 at 15:13
Just @taballeman said... copy past a sigle cell to word, not notepad. Just curious. — shadow, Dec 23 '15 at 15:17
Andy #JohnJack is a temporary table, right? How do you populate it? Maybe there is the problem. Wild guess if my initial hypothesis is true, about temp table — shadow, Dec 23 '15 at 15:29
@shadow when I'm doing it through the normal table, I have the same issue. I need some time to figure it out. Tomorrow is Christmas here. I will take some time tomorrow morning to deal with that. — Andy K, Dec 23 '15 at 15:31
Ok my friend. Merry Christmas then!!! Hope you will come up with the solution! — shadow, Dec 23 '15 at 15:33
@shadow, I've done that `SELECT Sigle,LEN(Sigle) FROM #JohnJack` — Andy K, Dec 23 '15 at 15:34
Me too. Fingers crossed. Merry Christmas to you as well, @shadow — Andy K, Dec 23 '15 at 15:34
Hi @shadow, I found my issue. I will post my query tomorrow as there is no internet at work today -_- — Andy K, Dec 28 '15 at 17:51

shadow · Accepted Answer · 2015-12-23T14:37:01.250

3

Try this:

SELECT  
    Siren,
    CASE    WHEN ( Code_Juridique LIKE 'M%' ) AND ( IsNull( Enseigne, '' ) <> '' )
                THEN 'Enseigne : ' + Enseigne

            WHEN ( IsNull( RTrim(LTrim(Sigle)), '') = '') AND ( IsNull( Enseigne, '' ) = '')
                THEN '' -- ok

            WHEN ( IsNull( RTrim(LTrim(Sigle)), '' ) <> '' ) 
                THEN 'Sigle : ' + RTrim(LTrim(Sigle))
            ELSE
                'Sigle / Enseigne : ' + IsNull( RTrim(LTrim(Sigle)), '' ) + ' / ' + Enseigne
        END as SigleEnseigne1,
        Sigle,
        Enseigne,
        Code_Juridique
        FROM    #JohnJack

edited Dec 23 '15 at 14:37

answered Dec 23 '15 at 14:26

shadow

1,883
1
16
24

Hi @shadow, nope. Sorry – Andy K Dec 23 '15 at 14:30
So I guess that sigle is not null and also not empty string – shadow Dec 23 '15 at 14:35
That's the thing @shadow buddy, in sqlserver, it is giving me nothing. When I do a copy-paste and put the contents between quotes, I have nothing ... – Andy K Dec 23 '15 at 14:36
nope @anton, the `ltrim` and `rtrim`are brilliant idea but something seems wrong in my datafield as it does not display what I want – Andy K Dec 23 '15 at 14:42
try: select '-->'+sigle+'<--' from #JohnJack. sigle is text, right? (char, varchar etc) – shadow Dec 23 '15 at 14:47
@AndyK Do a `LEN(Sigle)` to verify if `Sigle` is anything other than whitespaces. Remember `LEN()` trims trailing whitespaces by default. – domenicr Dec 23 '15 at 14:54
@shadow, it is giving me zero on `Sigle` ... #damn I'm losing my nerves – Andy K Dec 23 '15 at 14:57
@AndyK Try using my variation with `LEN()` – domenicr Dec 23 '15 at 15:06
@domenicr, I've just answered you – Andy K Dec 23 '15 at 15:09
@AndyK I meant try using my answer, it is a variation of shadows answer. See below I posted it maybe 20 minutes ago. – domenicr Dec 23 '15 at 15:11
@shadow many thanks for the help , last Wednesday. Lack of time yesterday eve in writing my answer (Lady , kids , cat etc ...) – Andy K Dec 29 '15 at 08:57
@AndyK Not to mention my friend! Happy new year! – shadow Dec 29 '15 at 09:10
happy new year @shadow! :) – Andy K Dec 29 '15 at 09:11

domenicr · Answer 2 · 2015-12-23T15:03:23.470

2

Besides stating the obvious that a (TRUE OR FALSE) = TRUE I would simplify and bullet proof the code by using ISNULL() and LEN().

SELECT  
Siren,
CASE    WHEN Code_Juridique LIKE 'M%' AND LEN(ISNULL(Enseigne,'')) > 0  --ok
            THEN 'Enseigne : ' + Enseigne
        WHEN (LEN(ISNULL(Sigle, '')) = 0)
            AND (LEN(ISNULL(Enseigne, '')) = 0) 
            THEN '' -- ok
        WHEN
             LEN(ISNULL(Sigle, '')) > 0 THEN 'Sigle : ' + Sigle
        ELSE 'Sigle / Enseigne : ' + ISNULL(Sigle, '') + ' / ' + ISNULL(Enseigne, '')
    END as SigleEnseigne1,
    Sigle,
    Enseigne,
    Code_Juridique
    FROM    #JohnJack

How would your code react if those fields contain whitespaces? LEN automatically trims trailing whitespaces.

edited Dec 23 '15 at 15:03

answered Dec 23 '15 at 14:43

domenicr

352
3
14

Hi @domenicr , thanks for your input. Alas, it is giving me `Sigle / Enseigne : / SIM` for the 4th line and `Sigle / Enseigne : / MONTMORILLON CARBUANTS, SE MB ENERGIE FIOUL,` for the 5th line – Andy K Dec 23 '15 at 15:08
@domenicr Nice approach! I can't understand what is wrong with sigle. – shadow Dec 23 '15 at 15:09
@AndyK To satisfy our curiosity please do a `SELECT LEN(Sigle), LENB(Sigle)` and tell us the results. – domenicr Dec 23 '15 at 15:16
@domenicr, `len(sigle)` is giving me zero on the 4th and 5th lines, as I've put it in my examples. `lenb` is no longer supported in my sqlserver version – Andy K Dec 23 '15 at 15:19
@AndyK My bad please do a SELECT LEN(Sigle), DATALENGTH(Sigle) and tell us the results – domenicr Dec 23 '15 at 15:26
@domenicr until the 6th line, which is normal : I have zeros – Andy K Dec 23 '15 at 15:30
@domenicr many thanks for the help , last Wednesday. Lack of time yesterday eve in writing my answer (Lady , kids , cat etc ...) – Andy K Dec 29 '15 at 08:57

Anton · Answer 3 · 2015-12-23T14:37:49.437

1

This line is causing your probelm:

    (Sigle IS NOT NULL OR Sigle <> '' ) THEN 'Sigle : ' + Sigle

...but that is only obvious because you state that you dont want this result. Other than that the code acts as would be expected.

The simplest solution would be to take out:

  'Sigle : ' + Sigle

but that may or may not be precisely what you are looking for. Based on the given information it is the solution but there is not an abundance of information to work off

If you are trying to get non null values to print then it should be an AND rather than an OR. When you use an OR it will return true if EITHER condition is true.

edited Dec 23 '15 at 14:37

answered Dec 23 '15 at 14:19

Anton

422
2
9

(Sigle IS NOT NULL OR Sigle <> '' ) This line is checking for sigle not null OR sigle = any non empty string. In other words if sigle contains anything at all that line will print out – Anton Dec 23 '15 at 14:31
issue is there are nothing with I can see with my human eyes ... notepad++ is not showing anything either ... – Andy K Dec 23 '15 at 14:32
What is sigle supposed to contain? and what is the desired output in each case? From my point of view it is difficult to tell why this is not the desired result. – Anton Dec 23 '15 at 14:34
try changing the OR to an AND – Anton Dec 23 '15 at 14:38

score 1 · Answer 4 · answered Dec 23 '15 at 14:47

I'm not sure why you have this when

        WHEN (Sigle IS NULL OR Sigle = '')
            AND (Enseigne IS NULL OR Enseigne ='')
            THEN '' -- ok

As what you want is SigleEnseigne1 to be NULL or '' when Sigle is NULL or '' don't you need this when instead

WHEN (Sigle IS NULL OR Sigle = '') THEN ''

There can also be the problem that Sigle is not the empty string and has whitespaces. You can use the LTRIM() and RTRIM() functions

score 0 · Answer 5 · answered Dec 29 '15 at 08:55

It took me the weekend but actually, I figured out My mistake.

Sorry for the mess

The answer is below

 SELECT  
 Siren,
 CASE    WHEN ( Code_Juridique LIKE 'M%' ) AND ( IsNull( Enseigne, '' ) <> '' )
             THEN 'Enseigne : ' + Enseigne
         WHEN ( IsNull( RTrim(LTrim(Sigle)), '') = '') AND ( IsNull( Enseigne, '' ) = '')
             THEN '' -- ok
         WHEN (Sigle IS NOT NULL OR Sigle <> '') AND (Enseigne IS NULL OR Enseigne = '') 
             THEN 'Sigle : ' + RTrim(LTrim(Sigle))
         WHEN (Sigle IS NULL OR Sigle = '') AND (Enseigne IS NOT NULL OR Enseigne <> '') 
             THEN ''
         WHEN
             (Sigle IS NOT NULL OR Sigle <> '') AND (Enseigne IS NOT NULL OR Enseigne <> '') 
             THEN 'Sigle / Enseigne : ' + IsNull( RTrim(LTrim(Sigle)), '' ) + ' / ' + Enseigne
     END as SigleEnseigne1
     FROM    John_Jack

Have a great end of the year

issue with CASE combined with an OR

5 Answers5