Stream Analytics: Source 'subquery' can only be used in temporal predicate using 'datediff' function

Question

I am querying the data in stream analytics with two subqueries using WITH. I want to combine the data from two subqueries and put them into SQL. So, I am using JOIN function.

WITH subquery as (
    SELECT
        id as id,
        deviceId as deviceId,
        username as username,
        try_cast(localtime as datetime) as localtime,
        AC as AC, FM as FM, UC as UC, 
        DL as DL, DS as DS, DP as DP,
        LB as LB, ASTV as ASTV, MSTV as MSTV,
        ALTV as ALTV, MLTV as MLTV, Width as Width,
        Min as Min, Max as Max, Nmax as Nmax,
        Nzeros as Nzeros, Mode as Mode, Mean as Mean,
        Median as Median, Variance as Variance, Tendency as Tendency,
        EventProcessedUtcTime as EventProcessedUtcTime,
        Distress(AC,FM,UC,DL,DS,DP,1,LB,ASTV,MSTV,ALTV,MLTV,
            Width,Min,Max,Nmax,Nzeros,Mode,Mean,Median,Variance,
            Tendency,1,1,1,1,1,1,1,1,1,1,1,1) as resultFHR
    FROM
        iot
    ),

subquery2 as (
    SELECT
        id as id,
        deviceId as deviceId,
        username as username,
        try_cast(localtime as datetime) as localtime,
        rms,fmed,fpeak,sample_entropy,
        EventProcessedUtcTime as EventProcessedUtcTime,
        Labour("",1,1,1,"",rms,fmed,fpeak,sample_entropy,"","") as resultUC
    FROM
        iot
    )

SELECT
    id as id,
    deviceId as deviceId,
    username as username,
    localtime as localtime,
    AC as AC, FM as FM, UC as UC, 
    DL as DL, DS as DS, DP as DP,
    LB as LB, ASTV as ASTV, MSTV as MSTV,
    ALTV as ALTV, MLTV as MLTV, Width as Width,
    Min as Min, Max as Max, Nmax as Nmax,
    Nzeros as Nzeros, Mode as Mode, Mean as Mean,
    Median as Median, Variance as Variance, Tendency as Tendency,
    EventProcessedUtcTime as EventProcessedUtcTime,
    resultFHR.[classes] as distress,
    resultFHR.[probabilities] as distressProbability,
    resultUC.[classes] as labour,
    resultUC.[probabilities] as labourProbability
INTO
    sql
FROM
    subquery 
INNER JOIN 
    subquery2 ON subquery.id = subquery2. id 
              AND DATEDIFF(second, subquery, subquery2) BETWEEN 0 AND 20

It throws an error at the last line:

Source 'subquery' can only be used in temporal predicate using 'datediff' function.

Example:

SELECT input1.a, input2.b FROM input1 JOIN input2 ON DATEDIFF(minute, input1, input2) BETWEEN 0 AND 10.

Please make sure there is no 'or' in temporal predicates.

I have followed the example given but it still has error. How can I combine the two subqueries?

Answer 1

The syntax error might be caused by the typo you have in your last two lines. Try replace "subquerry" with "subquery" like below:

FROM
    subquery 
INNER JOIN 
    subquery2 ON subquery.id = subquery2.id 
              AND DATEDIFF(second, subquery, subquery2) BETWEEN 0 AND 20