Create SQL queries

SQL statements determine which fields are included in a report. There are a few things you will need to remember when working with SQL queries:

• SQLQuery Fields
• The SQL query expression can include one SQL table.
• SQL query expression must not exceed 8000 characters in length.
• Capitalization is very important when using myAvatar-specific syntax.
• The report developer must have access to the SQL table contained in the FROM statement. Access is granted in the User Definition form, Forms and Tables section - Select Tables for Product SQL Access field.
• The following tables cannot be included in an SQL query expression:
• SYSTEM.unique_patient_identifier - For client selection, select Other Entity Lookup in the Parameter Type field, and select Client in the Entity field.
• SYSTEM.episode_history - For episode selection, select a pre-defined Episode parameter in the Parameter Type field.

AND operators

OR operators

See the Widget Definition form to build SQL queries.

1. ---

   In the Parameter Type field (Parameter Definition section of the Report Definition form), select an SQL Query.

   ► Examples:

   Note: The SQL language is not case sensitive however, capitalization is very important when using myAvatar-specific syntax.

   • SELECT statement
     Used to choose data from a database.
     Specifies the table columns that are retrieved.
     For example, SELECT column_name FROM table_name
     Add clauses to the SELECT statement to refine the search.

   • FROM clause
     Specifies the table to search for data.
     Specifies the tables accessed.

   • WHERE clause
     Filters table rows which do not satisfy the search condition.
     Passes table row data that meets the search condition, and rejects rows that do not.
     Searches for records with specific characteristics.
     Specifies which table rows are used.
     The WHERE clause is optional; if it is missing, all table rows are used in the query.
     The WHERE clause can reference multiple parameters using AND, OR, and ().
     A statement performs an operation (such as SELECT) on table rows WHERE a stated condition is True.

   The syntax of the WHERE clause is:
   SELECT column_list
   FROM table_name
   WHERE condition
   The column list specifies which columns to display. The statement displays the columns in the list. The FROM clause specifies which table the columns will display.

   The following system generated parameters can be used in the WHERE clause:
   FACILITY
   STAFFID
   SYSTEMCODE
   SYSTEMDATE
   TXT - for SQL Query (Search) parameters.
   USERDESC
   USERID

2. Compare one value to another.

   • For every row in which the comparison is True value, the WHERE clause is satisfied, the SELECT executes on that row.

   • Table rows that the comparison is False are skipped.

   • An assertion may be True, False, or unknown. The unknown case arises if one or more elements in the assertion are null.
     = Equal to
     <> Not equal to
     > Greater than
     < Less than
     >= Greater than or equal
     <= Less than or equal
     Starts with (case sensitive)
     Starts with (non case sensitive)

   • Filter records based on more than one condition.
     All conditions separated by AND must be true to retrieve results.

   • Displays a record if either the first condition or the second condition is true.
     If any one of two or more conditions must be True to qualify a row for retrieval, use the OR connector.