2

I couldn't find solution for this problem.

I have table A with primary key ROW_ID, table B with same extern key and column SOMETHING. Also, I have function created like this:

CREATE FUNCTION FIND_SOMETHING_FOR_ID(ROW_ID INTEGER)
RETURNS TABLE(SOMETHING INTEGER)
BEGIN ATOMIC
RETURN
    SELECT SOME_SCALAR_FUNCTION(SOMETHING)
    FROM B b
    WHERE b.ROW_ID=ROW_ID;
END@

The thing I want to do is: for each ROW_ID in A get table returned by FIND_SOMETHING_FOR_ID and then get UNION of all tables.

Belgradensis
  • 43
  • 4

1 Answers1

0

According to the documentation, you can do what you want as:

select fsfi.*
from A a cross join
     table(find_something_for_id(a.row_id)) fsfi;

That is, a table-valued function can reference tables before it in the from clause, but not after it. (Note: I replaced the , in the from with cross join because I abhor commas in the from clause.)

By the way, SQL Server solves this problem with the cross apply operator.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786