SQL Queries
User Guide Contents

The CATSWeb SQL Query page allows users to create advanced queries using SQL (Structured Query Language). When SQL Query is installed, two new employee or personality permissions appear under Queries and Filters.

If a user has been granted the Create SQL Queries permission, a New SQL Query link appears under the Special Function section on the Query Menu page. Clicking on the New SQL Query link opens the SQL Query page. A knowledge of SQL is required to create an SQL Query.

Only SQL Select statements can be entered. Certain words are considered to be reserved and cannot be used in an SQL Query. Some examples are listed below.

  • Insert, Update, Delete, Truncate, Create, Alter, Drop, etc.
Important
SQL commonly prefixes unicode strings (such as those that are used in SQL Select
statements) with an N (National Language Character Set).

Example: Personality=N'Administrator' Or Personality=N'Full User'

Here is an example of an SQL query:

Select IncidentID As 'Issue Number', Category From Incident Where EnteredBy=N'Bob Johnson'

SQL queries may be saved and reused. To recall saved SQL queries, use the Manage Saved Queries page.

SQL queries can include a variety of tokens to make them generally applicable to many users. These tokens include:

Token Meaning
{My Company} Your company name.
{My Name} The name of the current CATSWeb user.
{My Department} The department of the current CATSWeb user.
{My Department Name} The descriptive department name of the current CATSWeb user.
{My Home Department} The home department of the current CATSWeb user.
{My E-mail} The E-mail address of the current CATSWeb user.
{My Group} The group of the current CATSWeb user.
{My ID} The employee ID of the current CATSWeb user.
{My Personality} The personality of the current CATSWeb user.


Here is an example of using tokens in an SQL query:

Select IncidentID As 'Issue Number', Category From Incident Where EnteredBy= N'{My Name}'

Note the single apostrophes that surround the token. When the token is replaced with the current CATSWeb user name, the user name will be a text parameter. SQL requires that apostrophes surround any text parameters.

Optional Links in Results Section

This section allows URL links in the resulting data to be defined. This enables the user to click on a linked field and jump to the view page for the record (for example).
  • Link Field - The field name(s) returned by the SQL Query that is to become a link. Multiple field names may be listed and separated (delimited) by a pair of pipe characters ("||"). For example: "Customer||PartNumber||Process" defines 3 fields. Each field defined must be accompanied by a Link to URL entry (i.e. if 3 fields are defined, there must be 3 URLs). If the number of fields is different from the number of URLs, CATSWeb will ignore all of the field and URL settings.
  • Link to URL - The URL to link the query output field to, including optional replaceable parameters. Multiple URLs may be listed and separated (delimited) by a pair of pipe characters ("||"). The number of URLs listed must match the number of link fields listed. The following table lists relative URLs which may be used to refer to CATSWeb records.
    CATSWeb
    Record    		 Desired
    Action    		 URL (relative)
    Issue Add main.aspx?WCI=Main&WCE=AddIssue&WCU=n%3DBasic%2520Issue%2520Form
    Issue Edit main.aspx?WCI=Main&WCE=EditNoncon&WCU=r%3d999
    Issue View main.aspx?WCI=Main&WCE=ViewIssue&WCU=r%3d999
    Action Add main.aspx?WCI=Main&WCE=AddAction&WCU=n%3DBasic%2520Action%2520Form
    Action Edit main.aspx?WCI=Main&WCE=EditAction&WCU=r%3d
    Action View main.aspx?WCI=Main&WCE=ViewAction&WCU=r%3d
    Subtask Add main.aspx?WCI=Main&WCE=AddSubtask&WCU=r%3d999%7C%2A%7Eq%3dBasic%2520Action%2520Form%7C%2A%7Ef%3d2%7C%2A%7EWCU=u%3dCategory%3dStandard
    Subtask Edit main.aspx?WCI=Main&WCE=EditSubtask&WCU=r%3d
    Subtask View main.aspx?WCI=Main&WCE=ViewSubtask&WCU=r%3d
  • URL Parameter Replacements - Optional parameter replacements that CATSWeb makes by substituting data from the current record of the query results into the URL. Parameter replacements are in the form of "XYZ=Customer" where "XYZ" are the characters in the URL to replace, and "Customer" is the name of a field in the results. In this example, all occurrences of "XYZ" in the URL would be replaced with the value of the Customer field (e.g. "YourBigCustomerName").

    Note that the length of the characters to replace is not at all related to the length of the data that replaces them. The length of the characters to be replaced need only be long enough to guarantee their uniqueness in the URL. CATSWeb replaces those characters with the data specified, regardless of how long that data may be.

    Multiple parameter replacements may be listed and separated (delimited) by a pair of pipe characters ("||"). For example: "XYZ=Customer||ZXQ=PartNumber||QQQ=Process" defines 3 replacements in the URL.

     The following table lists relative URLs which may be used to refer to CATSWeb records. In each case, the character string "ZZZZ" is a placeholder for the CATSWeb record number. The URL Parameter Replacements field should be configured to replace this character string when the URL is used from a drill down link. Or, replace "ZZZZ" with the literal record number if the intention is to always navigate to that specific record:

    CATSWeb Record URL (relative)
    Issue main.aspx?WCI=Main&WCE=ViewIssue&WCU=r%3dZZZZ
    Action main.aspx?WCI=Main&WCE=ViewAction&WCU=r%3dZZZZ
    Subtask main.aspx?WCI=Main&WCE=ViewSubtask&WCU=r%3dZZZZ

Ownership Settings, Query Charts and Indicators, Query Options and Save Options are the same as for a standard Query.