Trouble seeing the images? Right click on images and open in new tab to enlarge or zoom in on the page (Ctrl + mousewheel).
What is a Custom Database Function?
A custom database function or field is special SQL syntax that your database allows within a SQL select statement. This is commonly used for Mathematical Computations, Concatenations, Substrings etc. Custom Database Functions and Field's syntax are passed directly to the database for evaluation and processing. So any supported function for a database platform is available, and varies by the provider (MS SQL, Oracle etc).
For supported Cache specific ODBC functions, check here.
Video Tutorial:
Adding Custom Database Functions or Fields:
Below are the steps for creating a custom Database Function or DBF.
Download the sample view to follow along (click here).
To add a Custom Formula or Function to a View, Right Click on the Report Fields area on the Tables and Fields tab.
Next, type in your Custom Field or Function in the box below and Click OK.
When the Custom Field or Function is added to your View it will have the @DBF prefix in front of the item. To edit the Custom Field or Function, simply double click on it.
IMPORTANT NOTE: To save the Database Function locally, click the Save / Update Locally button when adding or editing your database functions.
Example Stat Date:
This example has date values stored differently that need to be concatenated into a single date value.
ARCOSTATS.ARCOSMO (Months) is stored as a 1-12 value
ARCOSTATS.ARCOSDAY (Days) is stored as a 1-31 value
ARCOSTATS.ARCOSYR (Year)
The following function will concatenate the Month, Day and Year into a mm/dd/yyyy format. It's important to note the added '-' in between each to separate the values being added to the date. In order to use this function you would need to replace the fields in orange with your fields.
Database Function:
{fn CONVERT(STRING(SQLUser.ARCOSTATS.ARCOSYR,'-',SQLUser.ARCOSTATS.ARCOSMO,'-',SQLUser.ARCOSTATS.ARCOSDAY),SQL_DATE)}
Example Output:
Example DATEADD:
This example returns a value that is 7 days ago.
Database Function:
DATEADD('dd',-7,{fn CURDATE()})
Example DATEADD Output:
Example SQL DBF: Concat(DATEPART(mm,Orders.OrderDate) , '/1/' , DATEPART(yyyy,Orders.OrderDate))
Comments
4 comments
There are no examples of of the content that might be used in the filter. I have tried to copy simple SQL statements and this fails every time. For example, maybe I want the year from @LASTMONTH. I would assume it to be SELECT DATEPART(year, @LASTMONTH)
but that doesn't seem to work. Examples would help.
Hello Jeremy,
Database Functions code is sent directly to the database to evaluate. @LastMonth is not a valid expression to send to a SQL or Cache database, @LastMonth is a reserved word for VDM used specifically for filtering. To get the results from last month your database function would return a date value. You'd then apply a filter for the database function where it is equal to @LastMonth.
I agree - having documentation to reference re: commonly used syntax would be very handy. (Cache).
Hello Robert,
Jeremy's question was related to Reserved Words and Special Data Filtering Options. These are processed locally and then sent to the database as actual dates. Additional documentation and examples of Special Date Filter Options is available here. https://support.bridgeworksllc.com/hc/en-us/articles/200375239-Special-Date-Filtering-Options.
As for supported Cache specific ODBC functions. Please check out the link below on InterSystems website. Custom Database Functions and Field's syntax are passed directly to the database for evaluation and processing. So any supported function for a database platform is available, and varies by the provider (MS SQL, Oracle etc).
https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_FUNCTIONS
Please sign in to leave a comment.