locked
CASE not working outside Aggregation function RRS feed

  • Question

  • Hi

    I noticed that case expressions are not working if they are not encapsulated inside an aggregate function like SUM or MAX.

    For example below code does not compile.

    SELECT (CASE SensorValue 1 THEN 'High Error' END) as ValueDescription

    FROM inputeventhub

    GROUP BY SensorValue, TumblingWindow(second, 10) 

    But this one does

    SELECT MAX((CASE SensorValue 1 THEN 102 END)) as ValueDescription

    FROM inputeventhub

    GROUP BY SensorValue, TumblingWindow(second, 10) 


    Monday, December 15, 2014 2:32 PM

Answers

  • There is indeed a limitation, but it is not as broad.  Rather, we do not support CASE on aggregation keys.  You can easily work around it by saying:

    WITH 
    Threshold AS
    (    
        SELECT (CASE WHEN SensorValue=1 THEN 'High Error' ELSE NULL END) as ValueDescription
        FROM inputeventhub
    )
    SELECT ValueDescription, count(*) from Threshold
    GROUP BY ValueDescription, TumblingWindow(second, 10) 
    
    There is no performance difference.

    • Proposed as answer by Lev Novik - MSFT Monday, December 15, 2014 5:59 PM
    • Marked as answer by Gökhan Kurt Tuesday, December 16, 2014 10:48 AM
    Monday, December 15, 2014 5:57 PM