Handling If else conditions in stream analytics query

Rock 41

Hi,

I have one stream analytics query where i want to handle the condition like If today is a weekday then execute code1 else code2.
As stream analytics is not supporting IF else or subquery in Case statement how to handle this? Can I get some guidance here.
Below is my sample code,which is not working

SELECT(
CASE
WHEN DATEPART(WEEKDAY, SysTime) in (1,7) THEN
(
SELECT count() as Eventcount
INTO servicebusqueue
FROM Input Group By tumblingwindow(minute,5) Having Count(*) > 10 ) ELSE ( SELECT count(*) as Eventcount INTO servicebusqueue FROM Input
Group By tumblingwindow(minute,5)
Having Count() < 10
)
END ) as DayStatus
FROM Input

Thanks in Advance.

KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2020-10-01T00:59:39.933+00:00
Hi @Rock-1494,

Welcome to Microsoft Q&A platform and thanks for your query.

From the above query shared, I don't see a ELSE argument in your query which is required while using CASE expression. Could you please try adding ELSE argument to your query?

For example:

SELECT CASE WHEN temperature < 60 THEN 'Alert' ELSE 'OK' END as currentStatus FROM sensor

For more info please refer this docs:

https://learn.microsoft.com/stream-analytics-query/case-azure-stream-analytics

https://learn.microsoft.com/azure/stream-analytics/stream-analytics-stream-analytics-query-patterns#specify-logic-for-different-casesvalues-case-statements

Sample forum thread for reference: https://stackoverflow.com/questions/53114778/nested-condition-for-azure-stream-analytics

Hope this helps. Let us know if you have further query.
Rock 41 Reputation points

2020-10-01T13:09:06.937+00:00

thanks @KranthiPakala-MSFT .
I do have Else part in my existing query . Below is the whole query for your reference.
SELECT(
ASE WHEN DATEPART(WEEKDAY, SysTime) in (1,7) THEN
(
SELECT count() as Eventcount
INTO ServiceBusQueue
FROM Input
Group By tumblingwindow(minute,5)
Having Count() > 10
)
ELSE
( SELECT count() as Eventcount
INTO ServiceBusQueue
FROM Input
Group By tumblingwindow(minute,5)
Having Count() <= 10
)
END ) as DayStatus
FROM Input
It is giving error at Select part, as per my understanding Subquery is not supported in to Case statement. Simple Case statement is working fine(without select)
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2020-10-01T22:58:08.343+00:00
Thanks for your response @Rock-1494.

Looks like Stream Analytics Product team has responded to the same query in Stackoverflow. Just sharing it here for other's reference: https://stackoverflow.com/questions/64126171/select-statement-in-a-case-statement

Here is the proposed query approach:

WITH agg AS ( SELECT [day] = DATEPART(WEEKDAY, EventTime), count = Count(*) FROM Input ) SELECT count FROM agg WHERE CASE WHEN [day] in (1,7) and count < 10 THEN 1 WHEN [day] in (2,3,4,5,6) and count > 10 THEN 1 ELSE 0 END = 1

Thank you.
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2020-10-02T19:50:01.457+00:00

Hi @Rock-1494.

Following up to see if the above suggestion was helpful. Let us know if you have further query.

Thank you
Rock 41 Reputation points

2020-10-05T13:59:15.24+00:00

HI @KranthiPakala-MSFT , sorry for late reply. I was on leave.
I will try this and let you know if it works.
Rock 41 Reputation points

2020-10-05T15:35:58.69+00:00

Hi @KranthiPakala-MSFT
It gives me error near count(*).
below is a whole query (see attached file30424-asaquery.txt)

Please suggest
Thank you .
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2020-10-06T19:34:34.677+00:00

Hi @Rock-1494 ,

Sorry for your experience. Could you please share the complete error message your are facing? Also have you got a chance to test your query locally by using visual studio code as described in this document? https://learn.microsoft.com/azure/stream-analytics/visual-studio-code-local-run-live-input. If not, could you please try that and see if that helps to identify the exact root cause.

Thanks
Rock 41 Reputation points

2020-10-06T19:43:19.95+00:00

Hi @KranthiPakala-MSFT , below is the error

"Aggregate function 'count' must have an OVER clause when used in SELECT statement without GROUP BY."

I am not sure if we can use Over with count(*) here.
Thanks.
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2020-10-06T22:18:50.79+00:00
Hi @Rock-1494, thanks for your response and additional details. Since there is no GROUP BY in your SELECT statement with aggregate function COUNT, it is erroring out. Can you please try having a group by in your agg step --> Group By tumblingwindow(minute,5)

agg AS ( SELECT [day] = DATEPART(WEEKDAY, EventTime), count = Count(*) FROM InputEprismEvents IE Group By tumblingwindow(minute,5)

Also in the input1 step I see SELECT DISTINCT , ASA doesn't support such DISTINCT. To get the distinct you may use the approach defined in this section of doc: Retrieve the first event in a window

Hope this helps. Please let us know how it goes.
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2020-10-06T22:22:19.187+00:00

Continuation to above comment:

If you would like to share any feedback/suggestion regarding DISTINCT functionality in ASA, I would recommend you to please share your idea in Azure Stream Analytics user voice forum: https://feedback.azure.com/forums/270577-azure-stream-analytics. And please do share the feedback link here once it is posted as other readers can up-vote and comment on your suggestion which would help to increase the priority of the feature implementation.

Let us know if you have further query.

Thanks
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2020-10-09T00:05:30.887+00:00

Hi @Rock-1494,

Following up to see if the above suggestion was helpful. Let us know if you have further query.

THank you
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2020-10-09T21:06:53.773+00:00

Hi @Rock-1494,

We still have not heard back from you. Just wanted to check if you are you still facing the issue? In case If you found a different solution, would you please share it here with the community? Otherwise, let us know and we will continue to engage with you on the issue.

Thank you

2 answers

Rock 41 Reputation points

2020-10-12T13:15:36.727+00:00

Hey @KranthiPakala-MSFT , i was on PTO and didnot got any chance to test it. I will check it today and update it. Thank you for patience.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Rock 41 Reputation points

2020-10-13T13:54:09.893+00:00

Hi @KranthiPakala-MSFT ,
Thanks for your patience.
I tried to add group by clause , but it requires all the fields in the select clause. So when we add EventTime in the group by it doesnot gives the desired output. It is grouping on every single values of eventTime(which is 1 second apart) and does not fulfill 5 minutes tumbling window group.
Please help.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment