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?
