question

Rock-1494 avatar image
Rock-1494 asked ·

Handling If else conditions in stream analytics query

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.

azure-stream-analytics
12 comments
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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:

Hope this helps. Let us know if you have further query.

0 Votes 0 · ·
Rock-1494 avatar image Rock-1494 KranthiPakala-MSFT ·

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)








0 Votes 0 · ·

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.

0 Votes 0 · ·

Hi @Rock-1494.

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

Thank you

0 Votes 0 · ·

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

0 Votes 0 · ·

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

30514-image.png

Please suggest
Thank you .


0 Votes 0 · ·
asaquery.txt (900 B)
image.png (39.6 KiB)

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://docs.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

0 Votes 0 · ·

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.

0 Votes 0 · ·

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.

0 Votes 0 · ·

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

0 Votes 0 · ·

Hi @Rock-1494,

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

THank you

0 Votes 0 · ·

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

0 Votes 0 · ·
Rock-1494 avatar image
Rock-1494 answered ·

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.

Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Rock-1494 avatar image
Rock-1494 answered ·

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.

Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.