azureazure-cosmosdbazure-stream-analyticsstream-analytics

Default field value if a condition satisfies in Azure Stream Analytics SAQL


I have a requirement to set value of one field as per value of another field available in the document.

Below is the SAQL which I tried for obtaining the requirement.

WITH INPUT01 AS (
    SELECT
        input.id AS ID,
        ROUND(input.sig,5) AS SIG,
        input.dtcTriggeredDateTime.dateTime.time AS TIME,
        input.Description as DESC
        FROM [inputsignals] as input
    WHERE GetArrayLength(input.abc) =0 AND (input.Description = 'xyz' OR input.Description = 'pqr')

SELECT 
    ID,
    SIG,
    TIME,
    001 AS NUMID 
INTO [cosmosDB]
FROM INPUT01
WHERE DESC = 'abc'

SELECT 
    ID,
    SIG,
    TIME,
    002 AS NUMID 
INTO [cosmosDB]
FROM INPUT01
WHERE DESC = 'pqr'

If the DESC is abc then NUMID should be 001 and if DESC is pqr then NUMID should be 002.

Instead of writing the last two select statements separately is there any options to write it in a single SELECT query as something like below?

SELECT 
    ID,
    SIG,
    TIME,
    001 AS NUMID  (If DESC = abc)
    002 AS NUMID  (If DESC = pqr)
INTO [cosmosDB]
FROM INPUT01

Solution

  • Please use CASE...WHEN... operator in SA SQL:

    SELECT
        CASE
          WHEN DESC = 'abc' THEN 001
          WHEN DESC = 'pqr' THEN 002 
       END as NUMID  
    FROM INPUT01