Adding Custom Database Functions or Fields

Tony Coffman -

Trouble viewing images? Click on images to enlarge.

 

Adding Custom Database Functions or Fields

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.

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.

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.

 

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