Table-Valued Functions in the FROM Clause

Table-Valued Functions in the FROM Clause

SELECT Name,DOB FROM Sample.SP_Sample_By_Name('A')

The following Dynamic SQL example specifies the same table-valued function. It uses the %Execute() method to supply parameter values to the ? input parameter:

  SET myquery="SELECT Name,DOB FROM Sample.SP_Sample_By_Name(?)"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute("A")
  DO rset.%Display()
  WRITE !,"End of A data",!!
  SET rset = tStatement.%Execute("B")
  DO rset.%Display()
  WRITE !,"End of B data"

A table-valued function can only be used in the FROM clause of either a SELECT statement or a DECLARE statement. A table-valued function name can be qualified with a schema name or unqualified (without a schema name); an unqualified name uses the default schema. In a SELECT statement FROM clause, a table-valued function can be used wherever a table name can be used. It can be used in a view or a subquery, and can be joined to other table-ref items using a comma-separated list or explicit JOIN syntax.

A table-valued function cannot be directly used in an INSERT, UPDATE, or DELETE statement. You can, however, specify a subquery for these commands that specifies a table-valued function.

InterSystems SQL does not define the EXTENTSIZE for a table-valued function, or the SELECTIVITY for table-valued function columns.