SQL Functions View
The SQL Functions View enables you to create, edit and view SQL Functions for various different types within the application. Each one has an associated .net clr assembly containing the corresponding clr function that can be called from within sql.
SQL Function Properties
SQL Functions have the following properties:
| Name | Description |
|---|---|
| [Key] [Required] Name |
The unique name for this function that will be used in sql to refer to it. |
| Type | The type of clr user defined function to be imported:
|
| Enabled | Indicates if this sql function is enabled and should be imported into the database. |
| Assembly Name | The name of the assembly to use when importing this clr sql assembly into the database |
| External Name | The name of the class in the assembly to use to call the aggregate methods on for calculating the result in sql |
| Create SQL | The actual sql following the Create Aggregate sql command defining the schema of your user defined clr function holding the inputs and output. Such as : CREATE AGGREGATE WeightedAvg (@value int, @weight int) RETURNS int As illustrated in the SQL Server CLR User Defined Functions here. |
| Is System | Indicates if this is a system SQL Function that should not be edited by the user. |
| Is Public | Indicates if this sql function is public or only visible to the current user. |
| Version | The current version of this SQL Function |
| Description | A user friendly description of the calculation performed by this SQL Function |
Tip
Once created you can use the function itself in a dataset by calling the aggregate function with its Name and required parameters, and setting the Has Custom Aggregate field to true. The system will then include your custom calculation automatically when running this sql query according to your specified grouping levels. All automatically, and most importantly all from standard sql as fast and efficient as possible.
This brings the power of custom calculations to your query tools.
Note
A SQL Function has the standard set of commands and functions when being viewed from a grid. See our standard grid functionality.
When viewed from the grid a Data Template has the following custom commands:
Change CLR Assembly:
This allows you to select the .net dll assembly that contains your sql function into the LemonEdge platform.
Save Template:
This allows you to download the .net dll assembly for this SQL Function into your local system.