CODEWITHSUNDEEP
azure-stream-analytics
Aparna
Aparna

Reputation: 845

In Azure Stream analytics Query giving error in CASE statement

I have some input data coming. There are 3 columns, fqn, v and t. The column v can have a string value or a number. If the fqn is like %production% then the v will be a number else it is a string. So I would have data like 

FQN,V,T
productioncount,23,time 
state,RUN,time 
productioncount,45,time etc

So I hve a case clause in the stream analytics query, but it looks like the other fqn's(which have v values like RUN etc) are also being processed. I get the following error 

"Cannot cast value 'RUN' to type 'bigint' in expression 'cast ( v as BIGINT )'.."

My query is as follows

SELECT
 CASE
WHEN fqn like '%Production%' THEN ( max(cast(v as BIGINT))-(min(cast(v as BIGINT)))) 
ELSE MAX(V)
END AS V,
fqn,min(t) as timeslot
INTO
[finalalloutput]
FROM
[finalallinput] timestamp by t
group by TumblingWindow(minute, 1),fqn

Also I would like to change

ELSE MAX(V)

to reflect the TOP value. Maybe Max(V) would also give me an error because it is a string.

Upvotes: 0

Views: 1365

Answers (1)

Konstantin Zoryn
Konstantin Zoryn

Reputation: 621

You need to apply CASE WHEN ... END logic over aggregate function arguments, not over aggregate function results. Otherwise max is computed over all values and is causing the error you quoted.

Another problem is that Stream Analytics does not support max aggregate function over string values. What is your expectation when you apply MAX over string values? What are you trying to compute in your scenario?

Here is a query that computes same results for "Production" values as your query and returns last non-production event in the window:

WITH Step1 AS
(
    SELECT *, CASE WHEN fqn LIKE '%Production%' THEN 1 ELSE 0 END AS IsProduction
    FROM [finalallinput] TIMESTAMP BY t
),
Step2 AS
(
SELECT 
    MAX (CASE WHEN isProduction = 1 THEN cast(v AS BIGINT) ELSE NULL END) - MIN(CASE WHEN IsProduction = 1 THEN cast(v AS BIGINT) ELSE NULL END)   AS ProductionV,    
    TopOne() OVER (ORDER BY IsProduction ASC, t DESC) lastNonProductionEvent
    ,fqn
    ,min(t) AS timeslot
FROM Step1
GROUP BY 
    TumblingWindow(minute, 1)
    ,fqn
)

SELECT 
    fqn, timeslot, ProductionV, lastNonProductionEvent.v NonProductionV
INTO [finalalloutput]
FROM Step2

Upvotes: 1

Related Questions