View TVFs and function tables

For various use cases such as reports or Online Analytical Processing (OLAP) cubes, attribute values must be returned in columns that show a column for each attribute. You can use multiple joins to get this format, but the query strings become large and complex. As an alternative, you can create user-defined functions that return a table data type. These functions are known as Table-Valued Functions (TVFs). A TVF can be used where table or view expressions are allowed.

Function tables offer alternative syntax for use with third-party clients that do not allow execution of queries containing TVFs. You can generate custom TVFs and function tables with a wizard in PI SQL Commander Lite.

Note: By default, the data model for PI SQL Client consists of one catalog - Master. This catalog contains a set of tables and table-valued functions. For information about how to create other catalogs, see Use the template-specific data model wizard.

Use the Object Explorer pane to view TVFs and function tables.

1. To expand the Catalogs and schema folders, click a PI OLEDB Enterprise or PI SQL Client data source.

2. Do one of the following:

   • To view TVFs, click a Functions folder.

     TVFs are preceded by the icon.

   • To view function tables, click a Tables folder.

     Function tables are preceded by the icon.

     Note: PI SQL Client no longer has function tables.