Can I use multiple WITH clauses?

or How many ASes to a WITH?

By far the easiest way to organize a complex Stream Analytics query is to break it up into several parts using the WITH clause.  You can test individual parts one at a time, and the query is so much easier to read!  But a quirk of SQL syntax often trips people up, and makes them think that only a single sub-query can be used, because a query can only have one WITH statement.  Lucky for all of us, this is not true!

While a query can only have one WITH, the WITH can have as many AS clauses as it wants to, separated by commas.  For instance:

NormalReadings AS
      FROM Sensor
      WHERE Reading < 100 AND Reading > 0
Averages AS
      SELECT SensorId, AVG(Reading) as AvgNormalReading
      FROM NormalReadings
      GROUP BY SensorId, TumblingWindow(minute, 1)
BadAverages AS
      SELECT *
      FROM Averages
      WHERE AvgNormalReading < 10
SELECT * INTO outputAlerts FROM BadAverages
SELECT * INTO outputLog FROM NormalReadings


While we are at it, note that in Stream Analytics the WITH clause applies to the entire job, not just the SELECT statement that immediately follows it.  That is why the

SELECT * INTO outputLog FROM NormalReadings

above is valid. 

Convenient, eh?  Check out the docs for more details.

Comments (7)

Cancel reply

  1. Ali says:

    Thanks for interesting Article.

    Using With clauses i'm trying to achieve a Missing value substitution scenario, but having a tough time.

    The scenario is that Data is flowing through the stream, there are records which have Null values which need  to be substituted with the running average of last e.g. 10 minutes.

    At this point im trying to use the With clause to calculate the average and Case statement to substitute where i find a Null. however, i have been unable to figure out how to join the average from With clause to the main Select clause.

    Any help will be appreciated.

  2. says:

    Hi Ali!

    I think you were on the right path!  Here is how I would do it.  Assume that the input is 'requests', and that the property named 'value' is sometimes missing.

    WITH averages AS


       SELECT AVG(value) as avg

       FROM requests TIMESTAMP BY ts

       GROUP BY SlidingWindow(ss, 5)




       value = CASE WHEN requests.value IS NULL THEN averages.avg ELSE requests.value END

    FROM averages

       JOIN requests TIMESTAMP BY ts

       ON DATEDIFF(ms, requests, averages) = 0

  3. Ali says:

    Hi Lev,

    Thanks for your reply, following is my query which i have built by taking guidance from your reply.



      Select AVG([Sensor_1]) AS [Sensor_1]

      From [input]

      GROUP BY SlidingWindow(second, 5)


    SELECT [input].[ID]



       WHEN [input].[Sensor_1] = 0

       THEN [MV].[Sensor_1]

       ELSE [input].[Sensor_1]

    END [Sensor_1]





    FROM [MV]

    JOIN [input]

    ON DateDiff(second, [input], [MV]) = 0

    Using following data, sometimes i get duplicated records, i think the join is doing 6×2 rather than 6×1







    Any ideas where i'm going wrong?

  4. says:

    This will happen if two of your events have the same timestamp, or precisely 5 seconds apart.  Is that what is happening, you think?  Where do you get your timestamps from?  

  5. Ali says:

    I'm not using the timestamp. For us the time at which a record reaches Stream Analytics is its timestamp.

  6. Bhushan says:

    Thanks for the article. Its useful.

    I have written query same like this one. and for my stream analytics i have two outputs defined, 1 Azure Table storage and 2. PowerBI. Whenever I write two select statements like above  –

    SELECT * INTO outputAlerts FROM BadAverages

    SELECT * INTO outputLog FROM NormalReadings

    I'm getting the following stream analytics validation error –

    Stream Analytics job has validation errors: Job will exceed the maximum amount of Event Hub Receivers. Activity Id: '175b8fcc-cacc-42b0-8a38-26fa2cb0ef42-2015-09-24 06:40:24Z'.

    Can you please help me out ?

    1. Van Nguyen says:

      I also have this issue with Event Hub Receivers. Can anybody help me, plzzzzz ?

Skip to main content