Writing Successful Database Queries

Keep the following general points in mind when writing queries in SQL to retrieve data from the Accolade database:

  • In Accolade, queries are limited to the SELECT statement. A basic query has the following form:

SELECT ColumnName, ColumnName, ...
FROM ViewName

  • Only one FROM statement is allowed in each query and it must be following by a reporting view name with the appropriate prefix. A single query can reference multiple reporting views.
  • Queries can also contain a WHERE clause:

SELECT ColumnName, ColumnName, ...
FROM ViewName
WHERE ColumnName < ColumnName2, ...

  • Queries in Accolade are not case sensitive, with the exception of the LinkableName query token.
  • Use ANSI style SQL syntax in your query to ensure that it works correctly.
  • Accolade queries are designed to prevent the corruption of data or the alteration of the database structure. To ensure database integrity, the following keywords can only be used within a WHERE clause:
  • DELETE
  • DROP
  • CREATE
  • INSERT
  • UPDATE
  • GO
  • When writing queries for list metrics, to filter the items in the list, insert query field codes into a WHERE clause in the query. If you use a query that returns multiple columns in a list metric, the metric displays only the first column. For the list of query field codes, see Query Field Codes Reference.

Substitution Tokens

Queries can contain tokens that insert data, such as a link to a project, into the results of the query. The following tokens are allowed in Accolade queries:

  • Query field codes - A field code, much like an Accolade field code, that you can insert into a WHERE clause to restrain the list items in a query-based list metric. See Query Field Codes Overview.

The list metrics created can be either single select or multi-select and either individual or cascading, where the values displayed in the child metric in a cascading list depend on what was selected in the parent metric. The list metrics can be designated as filter metrics and used to filter projects, for example on the Resource Editor, Search, and other pages.

  • LinkableName - Inserted instead of the ProjectName column displays the project's name as a link to the Accolade project.
  • $USERID$ - Inserted in a WHERE clause, restricts the information the query returns to only data to which the user viewing the report has access.
Learn More: