Configuring Custom Queries - SQL Query - System Administration - On-Premises - English - Foundation 22.1 - OnBase - Essential - Premier - Standard - external - Standard - Essential - Premier

On-Premises System Administration
Platform
OnBase
Product
System Administration - On-Premises
Release
Foundation 22.1
License
Standard
Essential
Premier

SQL queries retrieve documents from OnBase using a custom SQL statement. The SQL statement contains all the parameters for document retrieval, so you do not have to select Document Types, retrieval keywords, or display or sort columns for the Custom Query.

CAUTION:

SQL Custom Queries do not respect Security Keywords in the OnBase Client. Users that execute SQL Custom Queries may be able to access documents they should not be allowed to access. This does not apply to the OnBase Core (for example, the Web Client). The OnBase Core respects Security Keywords when executing SQL Custom Queries.

Note:

It is recommended that only a database administrator set up a customized query after consulting with technical support.

To configure a SQL query:

  1. Select Queries | Custom Queries.
  2. Create a new query or select an existing SQL query and click Settings.

    If you are creating a new query, you must first assign the User Groups that will have rights to the query. Select all User Groups that will have rights to the query and click Add. Click Close.

    The Custom Query Options dialog box is displayed.

  3. In the Instructions field, type a description of the query for users who will be retrieving documents with this Custom Query. Text in this field is displayed when the user accesses the Custom Query.
  4. Under Custom Query Type, select Custom Written SQL.
    CAUTION:

    If you are creating a SQL query from an existing Custom Query, any display or sort columns configured for the query are removed. Display and sort columns will have to be re-configured if you change the SQL query back to its original Custom Query type.

  5. Click Edit SQL. The Customized Custom Query dialog box is displayed.
  6. Type the SQL parameters in the clause fields as described in the following table:

    Field

    Description

    From Clause

    Type the database table names to use for the query (up to 200 characters). Use commas to separate table names.

    Where Clause

    Type the specific criteria for the query (up to 64,000 characters). This can include joins to tables given in the From Clause as well as specific column values for the table.

    Order By Clause

    Type the criteria that will define the sort order of the results (up to 250 characters), such as datestored(date stored) or [column name] asc(ascending), or desc(descending), where [column name] is the column used for sorting.
    Note:

    The query engine will always use the fully qualified SQL table reference 'hsi.itemdata' in the SELECT clause for every column that needs to be returned. In addition, a SELECT COUNT will always use Count(hsi.itemdata.itemnum). Because of this, these queries should not be configured with a table alias for hsi.itemdata, as doing so will cause execution problems. For example, "select count(hsi.itemdata.itemnum) from hsi.itemdata itd where itemnum = X" will generate an error because of the inclusion of the alias itd.

  7. Click Save.
  8. Configure the remaining available options as described under Configuring Custom Queries.