CODEWITHSUNDEEP
t-sqljoinazure-stream-analytics
Sam
Sam

Reputation: 1327

Stream Analytics: Inner Join two subquery

I am using two subquery in Stream Analytics so that I can run two AzureML functions. 

WITH subquery as (
    SELECT
        id as id,
        username as username,
        try_cast(startTime as datetime) as startTime,
        try_cast(endTime as datetime) as endTime,
        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,
        rms,fmed,fpeak,sample_entropy,
        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,
        try_cast(startTime as datetime) as startTime,
        try_cast(endTime as datetime) as endTime,
        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,
        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,
    username as username,
    startTime as startTime,
    endTime as endTime,
    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


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

I try to use Inner Join to join two subquery but it works for second query and do not work for first query. When I use the Inner Join at the first query, it will show an error.

Invalid column name: 'id'. Column with such name does not exist.

Any solution for that?

Upvotes: 0

Views: 407

Answers (1)

Aλeᵡ
Aλeᵡ

Reputation: 491

Since you joining two sources subquery and subquery2, you need to qualify columns with the name of the source like you did in the ON clause (subquery.id = subquery2.id).

Unqualified names only allowed when you have a single source, like in the subquery step as an example.

Change column references to fully qualify them like this: SELECT subquery.id as id, subquery.username as username, subquery.startTime as startTime, subquery.endTime as endTime, ...

Upvotes: 2

Related Questions