The CASE statement always goes in the SELECT clause. CASE must include the following components: WHEN , THEN , and END . ELSE is an optional component.
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.
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?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With