This article describes the procedure to be followed by an Administrator, to modify the SQL functions in the Formula Builder.
Modifying formula functions is done when you require to update the functions commonly used by the users, or when you do not require the users to use a few functions. This can also be used if you have your own proprietary ODBC Driver with some built-in functions, and you require these to be available in the database.
In the server, login to the Microsoft SQL Server Management Studio and open the Yurbi database.
Execute the following steps:
Expand Tables and click the table dbo.sql_functions, where all the SQL functions are defined. Right-click and select Select Top 1000 Rows. You can view details such as name of the function, description, expression, type, database platform for this function, and so on.
To edit a function, right-click the table dbo.sql_functions and select Edit Top 200 Rows.
You can update the expression for the required function. For example, by default there are no parameters specified for varchar(). If you require the user to enter 4 numbers for a year, you can specify the parameter as 4 in the respective expression.
You can also add a new function with an expression. For this, you require to have the information of the database platform. This can be retrieved using the table dbo.db_platforms.
Navigate to the table dbo.db_platforms, right-click and select Select Top 1000 Rows. Details of the database platforms are listed.
You can also add a new database platform here if you have the information.
Note the database platform number you require, and then go to the table dbo.sql_functions. You can add a new function by entering details such as function name, description, expression, data type, and database platform number.
It is important to enter the database platform number because a function that works on MSSQL Server may not work as desired on Oracle.
Note: Some of the default formulas in the table dbo.sql_functions will function on the current version on MSSQL 2012. There are functions that may not work on MSSQL 2008. Therefore, you can edit the respective functions.