locked
T-SQL: JOINS in FROM are not supported RRS feed

  • Question

  • Hi colleagues,
    First time doing these stuff in Azure. The following SQL statement ends up in throwng an error that: "JOINS predicate is not time bounded". Does it mean that JOINS are not supported in FROM here?

    Thanks+regards,

    SELECT S.XWay, S.Dir, S.Seg, 2*(V.Count-150)*(V.Count-150)
    FROM input1 AS S JOIN input2 AS V
    ON S.XWay = V.XWay and S.Dir = V.Dir;

    Tuesday, August 25, 2015 10:08 PM

Answers

  • Joins are supposed in Azure Stream Analytics but there must be a time window as part of the join conditions.  Please refer to the JOIN reference:

    https://msdn.microsoft.com/en-us/library/azure/dn835026.aspx.


    Thursday, August 27, 2015 2:01 AM
  • EXISTS is not a valid function in ASA but you could do a LEFT JOIN and look for NULLs. 

    Something like

    SELECT S.XWay, S.Dir, S.Seg, 2*(V.Count-50)*(V.Count-50) AS ctoll
    FROM lrinputlav4toll AS S 
    JOIN lrinputcarscount4toll AS V
    	ON S.XWay = V.XWay and S.Dir = V.Dir and S.Seg = V.Seg
    		and S.speed < 40
    		and DATEDIFF(minute,S,V) BETWEEN 0 AND 1
    LEFT JOIN lrinputsegaccident4toll AS A
    			ON S.XWay = A.XWay and S.Dir = A.Dir and S.Seg = A.Seg
    				and DATEDIFF(minute,S,A) BETWEEN 0 AND 1
    WHERE A.XWay IS NULL

    • Marked as answer by AmirWMT Friday, August 28, 2015 6:41 PM
    Thursday, August 27, 2015 5:27 PM

All replies

  • Joins are supposed in Azure Stream Analytics but there must be a time window as part of the join conditions.  Please refer to the JOIN reference:

    https://msdn.microsoft.com/en-us/library/azure/dn835026.aspx.


    Thursday, August 27, 2015 2:01 AM
  • Thanks so much Tim. I resolved that issue. I now have trouble with sub-queries.
    I have tested every component of this independently and they are good.

    But when They get put together by BY EXISTS, the inner sub query is rejected as not supported.
    Thanks for your help again.

    ELECT S.XWay, S.Dir, S.Seg, 2*(V.Count-50)*(V.Count-50) AS ctoll
        FROM lrinputlav4toll AS S JOIN lrinputcarscount4toll AS V
            ON S.XWay = V.XWay and S.Dir = V.Dir and S.Seg = V.Seg
                and S.speed < 40
                and DATEDIFF(minute,S,V) BETWEEN 0 AND 1
         WHERE  NOT EXISTS 
                   (SELECT A.XWay, A.Dir,A.Seg FROM lrinputsegaccident4toll AS A 
          JOIN lrinputlav4toll AS S
                    ON S.XWay = A.XWay and S.Dir = A.Dir and S.Seg = A.Seg
                    and DATEDIFF(minute,S,A) BETWEEN 0 AND 1
                   WHERE A.XWay = S.XWay AND S.Dir = A.Dir AND S.Seg = A.Seg);

    Thursday, August 27, 2015 4:37 AM
  • EXISTS is not a valid function in ASA but you could do a LEFT JOIN and look for NULLs. 

    Something like

    SELECT S.XWay, S.Dir, S.Seg, 2*(V.Count-50)*(V.Count-50) AS ctoll
    FROM lrinputlav4toll AS S 
    JOIN lrinputcarscount4toll AS V
    	ON S.XWay = V.XWay and S.Dir = V.Dir and S.Seg = V.Seg
    		and S.speed < 40
    		and DATEDIFF(minute,S,V) BETWEEN 0 AND 1
    LEFT JOIN lrinputsegaccident4toll AS A
    			ON S.XWay = A.XWay and S.Dir = A.Dir and S.Seg = A.Seg
    				and DATEDIFF(minute,S,A) BETWEEN 0 AND 1
    WHERE A.XWay IS NULL

    • Marked as answer by AmirWMT Friday, August 28, 2015 6:41 PM
    Thursday, August 27, 2015 5:27 PM
  • Excellent Tim...appreciate it...looking good...

    Could you point me to a crash course on ASA T-SQL development/examples pls? 
    Cheers


    • Edited by AmirWMT Thursday, August 27, 2015 6:52 PM
    Thursday, August 27, 2015 5:58 PM
  • I would start with the documentation page and then either the fraud detection or the Twitter sentiment tutorials.

    - Tim

    Thursday, August 27, 2015 7:39 PM
  • Thanks Tim. I have implemented these two articles.
    I need to beef up my T-SQL dev in ASA...
    I appreciate any pointer to that...
    Cheers

    Thursday, August 27, 2015 8:21 PM
  • Hi Tim,
    One more mystery :)

    Thanks again for your valuable time.
    Pls excuse my novice.

    Why isnt OR predicate allowed here? Complains that make sure there are no OR predicates ...

    Works fine with AND instead of OR.

    Thanks.

    SELECT I1.TollId, I1.EntryTime, I2.ExitTime, I1.LicensePlate, DATEDIFF(minute,I1.EntryTime,I2.ExitTime) AS DurationinMinutes 
    FROM Input1 I1 TIMESTAMP BY EntryTime 
    JOIN Input2 I2 TIMESTAMP BY ExitTime
    ON I1.TollId=I2.TollId
    AND I1.LicensePlate=I2.LicensePlate
    OR I1.EntryTime = I2.ExitTime
    AND DATEDIFF(minute,I1,I2) BETWEEN 0 AND 15

    Thursday, August 27, 2015 10:29 PM
  • I've learned the T-SQL functions by reading the documentation on MSDN. I'm not sure of any tutorials that provide more instruction than the ones you have already completed.

    I'm also not sure about the OR, I've never tried an OR in a JOIN for ASA.  I know it is supported in the WHERE clause. 

    You should close this thread by marking it answered and open up a new one with the error message you are seeing for the OR issue because someone else might know the answer or a workaround.

    - Tim

    Friday, August 28, 2015 5:17 PM
  • OR is definitely allowed. Just make sure you use brackets around non-temporal part of your query.

    Since ASA queries infinite stream of data it requires specifying time bounds for operations like JOINs and GROUP BY.

    SELECT I1.TollId, I1.EntryTime, I2.ExitTime, I1.LicensePlate, DATEDIFF(minute,I1.EntryTime,I2.ExitTime) AS DurationinMinutes
     FROM Input1 I1 TIMESTAMP BY EntryTime
     JOIN Input2 I2 TIMESTAMP BY ExitTime
     ON (I1.TollId=I2.TollId
     AND I1.LicensePlate=I2.LicensePlate
    OR I1.EntryTime = I2.ExitTime)
     AND DATEDIFF(minute,I1,I2) BETWEEN 0 AND 15

    Saturday, September 19, 2015 4:11 PM