none
USQL - CROSS APPLY

    Question

  • Hi ,

    In TSQL i could do something like :

    SELECT * FROM foo CROSS APPLY (SELECT TOP(1) * FROM bar WHERE foo.A = bar.A ORDER by bar.b ) 

    I cant quite get the right syntax in USQL ,  is a similar operation supported ?


    Dave Ballantyne ---- http://sqlblogcasts.com/blogs/sqlandthelike/

    Wednesday, February 3, 2016 8:29 PM

Answers

  • Hi Dave

    CROSS APPLY is supported in U-SQL but not on subqueries or correlated subqueries due to the complexity of implementing it efficiently in a scale-out environment.

    Today CROSS APPLY only supports EXPLODE on SQL.MAP and SQL.ARRAY and will soon also support general IEnumerable<T> C# expressions.

    So one way to address the above would be to join the two rowsets and aggregate the candidates into an array and then use CROSS APPLY to explode the array.


    Michael Rys

    • Proposed as answer by Michael Amadi Thursday, February 4, 2016 7:45 AM
    • Marked as answer by Dave_Ballantyne Sunday, February 7, 2016 9:05 PM
    Wednesday, February 3, 2016 8:37 PM
    Moderator

All replies

  • Hi Dave

    CROSS APPLY is supported in U-SQL but not on subqueries or correlated subqueries due to the complexity of implementing it efficiently in a scale-out environment.

    Today CROSS APPLY only supports EXPLODE on SQL.MAP and SQL.ARRAY and will soon also support general IEnumerable<T> C# expressions.

    So one way to address the above would be to join the two rowsets and aggregate the candidates into an array and then use CROSS APPLY to explode the array.


    Michael Rys

    • Proposed as answer by Michael Amadi Thursday, February 4, 2016 7:45 AM
    • Marked as answer by Dave_Ballantyne Sunday, February 7, 2016 9:05 PM
    Wednesday, February 3, 2016 8:37 PM
    Moderator
  • Hi Michael,

    Thanks for the super fast response :)

    Ill give that a go,  as alternative i was thinking of using RowNumber and filtering in a subsequent query.  Its going to take time to find what the 'best' way of handling such problems will be.



    Dave Ballantyne ---- http://sqlblogcasts.com/blogs/sqlandthelike/

    Wednesday, February 3, 2016 8:47 PM
  • Cool. Please share your results of your investigation :)

    Michael Rys

    Thursday, February 11, 2016 10:45 PM
    Moderator