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.

  1. In Admin Console, on the Documents Tree, right-click SQL Functions and click New SQL Function.

    The New SQL Function window appears.

  2. In the Name field, enter a name for the SQL function and click OK.

    The SQL Function editor appears.

  3. 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.

  4. To use these functions, go to Studio and open a metric or record.

    You can now, for example, create a derived field.

  5. In the SQL Functions section, select Common. Next, click and drag the function to the editor.
    Note You can also use smart input in the field. Type something in the field. The system will prompt you with the possible variants.
  6. 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.