How does this CASE expression reach the ELSE clause?
The written form of the query is expanded to:
Channel =
CASE
WHEN ABS(CHECKSUM(NewId())) % 10 = 0 THEN 'Baby Only'
WHEN ABS(CHECKSUM(NewId())) % 10 = 1 THEN 'Club'
WHEN ABS(CHECKSUM(NewId())) % 10 = 2 THEN 'Drug'
WHEN ABS(CHECKSUM(NewId())) % 10 = 3 THEN 'Food'
WHEN ABS(CHECKSUM(NewId())) % 10 = 4 THEN 'Internet'
WHEN ABS(CHECKSUM(NewId())) % 10 = 5 THEN 'Liquidators'
WHEN ABS(CHECKSUM(NewId())) % 10 = 6 THEN 'Mass'
WHEN ABS(CHECKSUM(NewId())) % 10 = 7 THEN 'Military'
WHEN ABS(CHECKSUM(NewId())) % 10 = 8 THEN 'Other'
WHEN ABS(CHECKSUM(NewId())) % 10 = 9 THEN 'Speciality'
ELSE '*NONE*' -- How is this ever getting reached?
END
A new value for NEWID
is used in each test.
A new "random" number will be calculated for every WHEN clause - you can instead use a derived table:
SELECT ID, Name,
Channel =
CASE Rand
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
( SELECT
id,
name,
ABS(CHECKSUM(NewId())) % 10 Rand
FROM
retailshelf_nil...account A
) zzz;
or a CROSS APPLY subquery:
SELECT A.ID, A.Name,
Channel =
CASE zzz.Rand
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
CROSS APPLY
( SELECT
ABS(CHECKSUM(NewId())) % 10
) zzz (Rand);
That way NewID()
is called only once per record.
A similar scneario was resolved here.
The T-SQL documentation explains this phenomenon (granted it's for Sybase but apparently still applies to SQL Server):
Expressions that reference the
rand
function, thegetdate
function, and so on, produce different values each time they are evaluated. This can yield unexpected results when you use these expressions in certain case expressions. For example, the SQL standard specifies that case expressions with the form:case expression when value1 then result1 when value2 then result2 when value3 then result3 ... end
are equivalent to the following form of case expression:
case expression when expression=value1 then result1 when expression=value2 then result2 when expression=value3 then result3 ... end