Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How does this CASE expression reach the ELSE clause?

Tags:

People also ask

Does a case statement have to have an else?

The CASE statement always goes in the SELECT clause. CASE must include the following components: WHEN , THEN , and END . ELSE is an optional component.

What happens if you leave off the else clause in a SQL CASE statement?

If we leave out the ELSE clause, NULL values will be returned instead of "Married". As a best practice, it's a good idea to always include an ELSE argument. The WHEN condition can also use different data types. Any data type can be used in the WHEN condition, as long as the result is either true or false.

Can you use case in where clause SQL?

CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.


I need to load some test data into the Channel field on my Account table. The Channel can be one of 10 different values, so I thought I'd randomly assign the Channel one of the values using a CASE expression along with ABS(CHECKSUM(NewId())) % 10 like so:

SELECT 
    id,
    name,
    Channel = 
      CASE ABS(CHECKSUM(NewId())) % 10
        WHEN 0 THEN 'Baby Only'
        WHEN 1 THEN 'Club'
        WHEN 2 THEN 'Drug'
        WHEN 3 THEN 'Food'
        WHEN 4 THEN 'Internet'
        WHEN 5 THEN 'Liquidators'
        WHEN 6 THEN 'Mass'
        WHEN 7 THEN 'Military'
        WHEN 8 THEN 'Other'
        WHEN 9 THEN 'Speciality'
        ELSE '*NONE*'            -- How is this ever getting reached?
      END
FROM 
    retailshelf_nil...account A

Since I'm using modulo 10 I thought the only possible values should be 0-9. But when I run the above code, I'm finding that the ELSE clause is indeed being reached and that my data is coming up with 'NONE' on some records as shown:

id                  name    Channel
001L000000KpgFqIAJ  Acct1   *NONE*
001L000000KpgFrIAJ  Acct2   Mass
001L000000KpgFsIAJ  Acct3   Club
001L000000KpgFtIAJ  Acct4   *NONE*
001L000000KpgFuIAJ  Acct5   Baby Only
001L000000KpgFvIAJ  Acct6   *NONE*
001L000000KpgFwIAJ  Acct7   Mass

Can someone please explain what logical error I've made that's allowing the ELSE clause to be reached?

When I run a simple test to just generate the random number like so:

SELECT
    RadomNum = ABS(CHECKSUM(NewId())) % 10
FROM 
    retailshelf_nil...account A
ORDER BY 
    1

All the numbers generated are from 0-9 as expected, so what's different about this first SQL?

And is there a workaround to ensure that ELSE is never reached?