How to do conditional tests in Azure Stream Analytics

Question

I've been struggling for two days ... but keep hitting a brick wall that Azure Stream Analytics does not support variables.

I want to take a payload value, extract a particular byte, and output it...

This is easy:

SELECT
    device AS Device,
    dateadd(S, time, '1970-01-01') AS Time,
    data AS Payload,
    SUBSTRING (data, 3, 1) AS Counter,
    SUBSTRING (data, 6, 1) AS Sensor1Type,
    SUBSTRING (data, 8, 1) AS Sensor1State =

...etc

This returns in the output blob the Payload value, Counter, etc as follows

{"device":"F8A96","time":"2018-03-27T09:04:35.0000000Z","payload":"01b00200000002000000","counter":"b","sensor1type":"2","sensor1state":"0","sensor2type":"2","sensor2state":"0"}

I now want to turn the "sensor1state" into "Open" or "Closed" instead of 1 or 0.

I've tried with CASE, WITH, etc but can't get it right, because I'm working with the output of an expression (SUBSTRING) and not with a column.

For e.g. I've tried:

SELECT
  ....
   SUBSTRING (data, 8, 1) AS Sensor1State =
    CASE 
      WHEN SUBSTRING (data, 8, 1) = 1 THEN 'Closed' ELSE 'Open'
    END,
...

But it gives me a syntax error.

Any ideas? Stream Analytics does not support variables, so I can't use that. I'm a major noob, so don't know how to perform tricks.

UPDATE:

After hacking away a bit longer, solved my own problem, working query:

SELECT 
    device AS Device,
    dateadd(S, time, '1970-01-01') AS Time,
    data AS Payload,
    SUBSTRING (data, 3, 1) AS Counter,
    SUBSTRING (data, 6, 1) AS Sensor1Type,
    SUBSTRING (data, 14, 1) AS Sensor2Type,

    CASE SUBSTRING (data, 8, 1)
    WHEN '1' THEN 'Closed' 
    ELSE 'Open'
    END as Sensor1State,

    CASE SUBSTRING (data, 16, 1)
    WHEN '1' THEN 'Closed' 
    ELSE 'Open'
    END as Sensor2State

INTO
    iotoutput
FROM
    iotinputs
WHERE
    Device = 'F8A96'

NOTE: I've not seen it documented anywhere, but there seems to be a bug in the StreamAnalytics code parser, if your "INTO" statement is not directly after your SELECT, before FROM, it gives an extremely unhelpful syntax error.

Can you post the complete usql script and the syntax error details? — Peter Bons
Whoohoo while adding the code and explaining the error, I worked out what I was doing wrong. — D0dja

Joe zhang Joe zhang · Accepted Answer · 2018-03-27T16:56:58

you can use define a java script UDF for this convert logic and then reference it in asa script.

function get_status(time)
{
   var res = time.substring(8, 1);
   if(res == "1") return "Closed";
   else if(res == "0") return "Open";
   else return "Unknown";
}

How to do conditional tests in Azure Stream Analytics

1 Answers