Как это выражение CASE достигает предложения ELSE?

Мне нужно загрузить некоторые тестовые данные в поле Channel в моей учетной записи. Канал может быть одним из 10 различных значений, поэтому я подумал, что я случайно назначил один из каналов с помощью выражения CASE вместе с ABS(CHECKSUM(NewId())) % 10 следующим образом:

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

Поскольку я использую modulo 10, я думал, что только возможные значения должны быть 0-9. Но когда я запускаю приведенный выше код, я обнаружил, что предложение ELSE действительно достигнуто и что мои данные приходят с "NONE" на некоторые записи, как показано:

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

Может кто-нибудь объяснить, какую логическую ошибку я сделал, чтобы разрешить предложение ELSE?

Когда я запускаю простой тест, чтобы просто генерировать случайное число, например:

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

Все сгенерированные числа от 0 до 9, как ожидалось, так что же отличается от этого первого SQL?

И есть ли способ обхода, чтобы ELSE никогда не был достигнут?

Ответы

Ответ 1

Письменная форма запроса расширена до:

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

В каждом тесте используется новое значение для NEWID.

Ответ 2

Новое "случайное" число будет вычисляться для каждого предложения WHEN - вместо этого вы можете использовать производную таблицу:

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;

или подзапрос CROSS APPLY:

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);

Таким образом, NewID() вызывается только один раз на запись.

Аналогичное scneario было разрешено здесь.

Документация T-SQL объясняет это явление (предоставлено для Sybase, но, по-видимому, все еще применяется к SQL Server):

Выражения, ссылающиеся на функцию rand, функцию getdateи т.д., производят разные значения каждый раз, когда они оцениваются. Эта могут дать неожиданные результаты, когда вы используете эти выражения в определенных case case. Например, стандарт SQL указывает, что случай выражения с формой:
case expression
    when value1 then result1
    when value2 then result2
    when value3 then result3
...
end
эквивалентны следующей форме выражения случая:
case expression
    when expression=value1 then result1
    when expression=value2 then result2
    when expression=value3 then result3
...
end

Ответ 3

Что касается второго вопроса,

CHECKSUM (NewId()) иногда возвращает отрицательные результаты, которые не соответствуют ни одному из условий case. Если отрицательное число делится на любое число, результат будет отрицательным. Выполните следующий запрос,

declare @v nvarchar(50) = newid()
select CHECKSUM(@v),@v,CHECKSUM(@v) % 10