Adding Custom Database Functions or Fields

Cory Fifield -

Trouble viewing images? Click on images to enlarge.

 

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.

 

Adding Custom Database Functions or Fields

Below are the steps for creating a custom Database Function or DBF

 

To add a Custom Formula or Function to a View, Right Click on the Report Fields area on the Tables and Fields tab.

mceclip0.png

 

Next, type in your Custom Field or Function in the box below and Click OK.

mceclip3.png

 

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.

mceclip4.png

 

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:

mceclip6.png

 

Example DATEADD:

This example returns a value that is 7 days ago.

 

Database Function:

DATEADD('dd',-7,{fn CURDATE()})

 

Example DATEADD Output:

mceclip1.png

 

Have more questions? Submit a request

Comments

  • Avatar
    Jeremy Mapes

    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.

  • Avatar
    Tony Coffman

    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.

  • Avatar
    Robert Richardson

    I agree - having documentation to reference re: commonly used syntax would be very handy. (Cache).

  • Avatar
    Tony Coffman

    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

    Edited by Tony Coffman