4

Is there an alternative in HANA sql for the "cross apply" operator as available in MsSql? Or is there a way to apply a function for values in a table? Something like

select T.*, F.* from T cross join someFunction(T.Value) F
florin
  • 405
  • 4
  • 20
  • 1
    The `cross apply` in SQL Server is called `lateral join` in standard SQL. Maybe that's what Hana is using. –  Sep 24 '14 at 17:42
  • maybe not (but the song is nice) – florin Sep 25 '14 at 09:01
  • Can you explain what you expect the result set to look like? – AHaberl Sep 26 '14 at 14:04
  • Same as in MsSql: I want to execute a sql function for each row in table T and use the value of the column values returned by that function. I have a sql query that I don't want to use as sub-query or join (too long and same query will be used in multiple places). And I want to return the output of that query though a function that is applied for each T.Value. Before talking about performance you should know that there would be 0 - 5 records that the function will be applied for. – florin Oct 13 '14 at 09:12

1 Answers1

-1

You can use the cross join in SAP HANA, that's not a problem. However, if you want to apply a function to all entries of T.VALUE then the query needs to look like this:

select T.*, someFunction(T.Value)
from T cross join F
Lars Br.
  • 9,949
  • 2
  • 15
  • 29
  • 2
    Just to say that this is not exactly the same. In the case of "from T cross join someFunction(T.Value) F" the function can change the number of records returned for each record of T. In case of "select T.*, someFunction(T.Value) from T cross join F" the number of records in table F will be the same for allbrecords of T. –  Feb 16 '16 at 14:21
  • +1 to Urbino for mentioning the above. Another limitation of this is when you have a table-valued function from which you want to return multiple values (e.g. "select T.*, F.c1, F.c2 from T cross join someFunction(T.Value) F"): You'd have to repeat the function call and write "select T.*, (select c1 from someFunction(T.Value)), (select c2 from someFunction(T.Value)) from T" in HANA. – mlkammer Feb 29 '16 at 16:23
  • Sorry, misundertanding on my side about what the lateral join exactly does. However, @mlkhammers remark about accessing the returned columns is not correct in SPS 11 (current SAP HANA release). You can simply address the columns via . as if it was a normal table.
     – Lars Br. Mar 03 '16 at 02:15