SQL Functions
Define a custom SQL function to execute on a specific database type, such as SQL Server, Oracle, or MySQL if there is no appropriate Insight logarithm function. The function may also be common to all databases.
The custom SQL function can then be used in a derived field in a metric or record.
-
In
Admin Console,
on the
Documents Tree, right-click
SQL Functions and click
New SQL Function.
The New SQL Function window appears.
-
In the
Name field, enter a name
for the SQL function and click
OK.
The SQL Function editor appears.
-
Complete the following details:
-
Name: Name of the SQL function.
-
Function Type: Standard or Aggregate.
-
Category: Assign a category to this function so that it will be listed and saved under the category group. To use this function, it must be retrieved from this group in the expression editor.
-
Variable number of arguments: Select this option if the function accepts a variable number of arguments.
-
Parameters (comma-separated without spaces): List the input parameters to this function. The format is comma delimited without spaces. If the function accepts a variable number of arguments, enter "ListItem..."
-
Description: The function description.
-
DB Type: Use Default DBMS Template if you would like this function to work across all databases; otherwise, pick a specific database type and the function will only appear if the record or matrix fields are from the specific database type.
-
Representation: Use this space to enter the formula for the SQL function.
-
-
To use these functions, go to
Studio
and open a metric or record.
You can now, for example, create a derived field.
-
In the
SQL Functions section,
select
Common. Next, click and
drag the function to the editor.
You can also use smart input in the field. Type something in the field. The system will prompt you with the possible variants.
-
In the
Expression editor, enter
the number of parameters for the expression and click
OK.
The function appears in the expression editor. You can use this function to define the field.