Querying the Database

SELECT Clause Order of Execution

The operation of a SELECT statement can be understood by noting its semantic processing order (which is not the same as the SELECT syntax order). The clauses of a SELECT are processed in the following order:

1. FROM clause — specifies a table, a view, multiple tables or views using JOIN syntax, or a subquery.

2. WHERE clause — restricts what data is selected using various criteria.

3. GROUP BY clause — organizes the selected data into subsets with matching values; only one record is returned for each value.

4. HAVING clause — restricts what data is selected from groups using various criteria.

5. select-item — selects a data fields from the specified table or view. A select-item can also be an expression which may or may not reference a specific data field.

6. DISTINCT clause — applied to the SELECT result set, it limits the rows returned to those that contain a distinct (non-duplicate) value.

7. ORDER BY clause — applied to the SELECT result set, it sorts the rows returned in collation order by the specified field(s).

This semantic order shows that a table alias (which is defined in the FROM clause) can be recognized by all clauses, but a column alias (which is defined in the SELECT select-items) can only be recognized by the ORDER BY clause.

To use a column alias in other SELECT clauses you can use a subquery, as shown in the following example:

SELECT Interns FROM 
      (SELECT Name AS Interns FROM Sample.Employee WHERE Age<21) 
WHERE Interns %STARTSWITH 'A'

In this example, Name and Age are columns (data fields) in the Sample.Person table, and Interns is a column alias for Name.

Selecting Fields

When you issue a SELECT, Caché SQL attempts to match each specified select-item field name to a property defined in the class corresponding to the specified table. Each class property has both a property name and a SqlFieldName. If you defined the table using SQL, the field name specified in the CREATE TABLE command is the SqlFieldName, and Caché generated the property name from the SqlFieldName.

Field names, class property names, and SqlFieldName names have different naming conventions:

• Field names in a SELECT statement are not case-sensitive. SqlFieldName names and property names are case-sensitive.

• Field names in a SELECT statement and SqlFieldName names can contain certain non-alphanumeric characters following identifier naming conventions. Property names can only contain alphanumeric characters. When generating a property name, Caché strips out non-alphanumeric characters. Caché may have to append a character to create a unique property name.

The translation between these three names for a field determine several aspects of query behavior. You can specify a select-item field name using any combination of letter case and Caché SQL will identify the appropriate corresponding property. The data column header name in the result set display is the SqlFieldName, not the field name specified in the select-item. This is why the letter case of the data column header may differ from the select-item field name.

You can specify a column alias for a select-item field. A column alias can be in any mix of letter case, and can contain non-alphanumeric characters, following identifier naming conventions. A column alias can be referenced using any combination of letter case (for example, in the ORDER BY clause) and Caché SQL resolves to the letter case specified in the select-item field. Caché always attempts to match to the list of column aliases before attempting to match to the list of properties corresponding to defined fields. If you have defined a column alias, the data column header name in the result set display is the column alias in the specified letter case, not the SqlFieldName.

When a SELECT query completes successfully, Caché SQL generates a result set class for that query. The result set class contains a property corresponding to each selected field. If a SELECT query contains duplicate field names, the system generates unique property names for each instance of the field in the query by appending a character. For this reason, you cannot include more than 36 instances of the same field in a query.

The generated result set class for a query also contains properties for column aliases. To avoid the performance cost of letter case resolution, you should use the same letter case when referencing a column alias as the letter case used when specifying the column alias in the SELECT statement.

In addition to user-specified column aliases, Caché SQL also automatically generates up to three aliases for each field name, aliases which correspond to common letter case variants of the field name. These generated aliases are invisible to the user. They are provided for performance reasons, because accessing a property through an alias is faster than resolving letter case through letter case translation. For example, if SELECT specifies FAMILYNAME and the corresponding property is familyname, Caché SQL resolves letter case using a generated alias (FAMILYNAME AS familyname). However, if SELECT specifies fAmILyNaMe and the corresponding property is familyname, Caché SQL must resolves letter case using the slower letter case translation process.

A select-item item can also be an expression, an aggregate function, a subquery, a user-defined function, as asterisk, or some other value. For further details on select-item items other than field names, refer to The select-item section of the SELECT command reference page.

The JOIN Operation

A JOIN provides a way to link data in one table with data in another table and are frequently used in defining reports and queries. Within SQL, a JOIN is an operation that combines data from two tables to produce a third, subject to a restrictive condition. Every row of the resulting table must satisfy the restrictive condition.

Caché SQL supports five types of joins (some with multiple syntactic forms): CROSS JOIN, INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN. Outer joins support the ON clause with a full range of conditional expression predicates and logical operators. There is partial support for NATURAL outer joins and outer joins with a USING clause. For definitions of these join types and further details, refer to the JOIN page in the Caché SQL Reference.

If a query contains a join, all of the field references within that query must have an appended table alias. Because Caché does not include the table alias in the data column header name, you may wish to provide column aliases for select-item fields to clarify which table is the source of the data.

The following example uses a join operation to match the “fake” (randomly-assigned) zip codes in Sample.Person with the real zip codes and city names in Sample.USZipCode. A WHERE clause is provided because USZipCode does not include all possible 5-digit zip codes:

SELECT P.Home_City,P.Home_Zip AS FakeZip,Z.ZipCode,Z.City AS ZipCity,Z.State
FROM Sample.Person AS P LEFT OUTER JOIN Sample.USZipCode AS Z 
ON P.Home_Zip=Z.ZipCode
WHERE Z.ZipCode IS NOT NULL
ORDER BY P.Home_City

Queries Selecting Large Numbers of Fields

A query cannot select more than 1,000 select-item fields.

A query selecting more than 150 select-item fields may have the following performance consideration. Caché automatically generates result set column aliases. These generated aliases are provided for field names without user-defined aliases to enable rapid resolution of letter case variations. Letter case resolution using an alias is significantly faster than letter case resolution by letter case translation. However, the number of generated result set column aliases is limited to 500. Because commonly Caché generates three of these aliases (for the three most common letter case variations) for each field, the system generates aliases for roughly the first 150 specified fields in the query. Therefore, a query referencing less than 150 fields commonly has better result set performance than a query referencing significantly more fields. This performance issue can be avoided by specifying an exact column alias for each field select-item in a very large query (for example, SELECT FamilyName AS FamilyName) and then making sure that you use the same letter case when referencing the result set item by column alias.

CREATE QUERY and CALL

You can define a query using CREATE QUERY, and then execute it by name using CALL. In the following example, the first is an SQL program that defines the query AgeQuery, the second is Dynamic SQL that executes the query:

CREATE QUERY Sample.AgeQuery(IN topnum INT DEFAULT 10,IN minage INT 20)
   PROCEDURE
   BEGIN
   SELECT TOP :topnum Name,Age FROM Sample.Person
   WHERE Age > :minage 
   ORDER BY Age ;
   END

  ZNSPACE "Samples"
  SET mycall = "CALL Sample.AgeQuery(11,65)"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(mycall)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()

DROP QUERY Sample.AgeQuery

Class Queries

You can define a query in a class. The class may be a %Persistent class, but does not have to be. This class query can reference data defined in the same class, or in another class in the same namespace. The tables, fields, and other data entities referred to in a class query must exist when the class that contains the query is compiled.

The following class definition example defines a class query:

Class Sample.QClass Extends %Persistent [DdlAllowed]
  {
  Query MyQ(Myval As %String) As %SQLQuery (CONTAINID=1,ROWSPEC="Name,Home_State") [SqlProc]
     {
     SELECT Name,Home_State FROM Sample.Person 
     WHERE Home_State = :Myval  ORDER BY Name
     }

  }

The following example executes the MyQ query defined in the Sample.QClass in the previous example:

  SET Myval="NY"
  SET stmt=##class(%SQL.Statement).%New()
  SET status = stmt.%PrepareClassQuery("Sample.QClass","MyQ")
    IF status'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(status) QUIT}
  SET rset = stmt.%Execute(Myval)
  DO rset.%Display()
  WRITE !,"End of data"

The following Dynamic SQL example uses %SQL.StatementOpens in a new tab to execute the ByName query defined in the Sample.Person class, passing a string to limit the names returned to those that start with that string value:

  SET statemt=##class(%SQL.Statement).%New()
  SET cqStatus=statemt.%PrepareClassQuery("Sample.Person","ByName")
    IF cqStatus'=1 {WRITE "%PrepareClassQuery failed:" DO $System.Status.DisplayError(cqStatus) QUIT}
  SET rs=statemt.%Execute("L")
  DO rs.%Display()

For further details, refer to “Defining and Using Class Queries” in Using Caché Objects.

For information on query names automatically assigned to executed queries, refer to the Cached Queries chapter of Caché SQL Optimization Guide.

Collection Indexing and Querying Collections through SQL

Collections may be referenced from the SQL WHERE clause with a FOR clause. For example:

 FOR SOME %ELEMENT(collectionRef) [AS label] (predicate)

The FOR SOME %ELEMENT clause can be used for list collections and arrays that specify STORAGEDEFAULT="list". The predicate may contain one reference to the pseudo-columns %KEY, %VALUE, or both. A few examples should help to clarify how the FOR SOME %ELEMENT clause may be used. The following returns the name and the list of FavoriteColors for each person whose FavoriteColors include 'Red'.

SELECT Name,FavoriteColors FROM Sample.Person
    WHERE FOR SOME %ELEMENT(FavoriteColors) (%Value = 'Red')

Any SQL predicate may appear after the %Value (or %Key), so for example the following is also legal syntax:

SELECT Name,FavoriteColors FROM Sample.Person
    WHERE FOR SOME %ELEMENT(Sample.Person.FavoriteColors)
        (%Value IN ('Red', 'Blue', 'Green'))

A list collection is considered a special case of an array collection that has sequential numeric keys 1, 2, and so on. Array collections may have arbitrary non-null keys:

 FOR SOME (children) (%Key = 'betty' AND %Value > 5)

In addition to the built-in list and array collection types, generalized collections may be created by providing a BuildValueArray() class method for any property. The BuildValueArray() class method transforms the value of a property into a local array, where each subscript of the array is a %KEY and the value is the corresponding %VALUE.

In addition to simple selections on the %KEY or %VALUE, it is also possible to logically connect two collections, as in the following example:

   FOR SOME %ELEMENT(flavors) AS f
      (f.%VALUE IN ('Chocolate', 'Vanilla') AND
       FOR SOME %ELEMENT(toppings) AS t
           (t.%VALUE = 'Butterscotch' AND
            f.%KEY = t.%KEY))

This example has two collections: flavors and toppings, that are positionally related through their key. The query qualifies a row that has chocolate or vanilla specified as an element of flavors, and that also has butterscotch listed as the corresponding topping, where the correspondence is established through the %KEY.

Usage Notes and Restrictions

• FOR SOME %ELEMENT may only appear in the WHERE clause.

• %CONTAINS may only appear in the WHERE clause.

• %KEY and/or %VALUE may only appear in a FOR predicate.

• Any particular %KEY or %VALUE may be referenced only once.

• %KEY and %VALUE may not appear in an outer join.

• %KEY and %VALUE may not appear in a value expression (only in a predicate).

• Streams longer than the maximum length of a string require the use of the %CONTAINSTERM predicate rather than the %CONTAINS predicate. For information on the maximum length of a string, see the section “Support for Long String Operations” in the chapter “Server Configuration Options” in the Caché Programming Orientation Guide.

• Streams longer than the maximum length of a string only support the use of %SIMILARITY on indexed fields. For information on the maximum length of a string, see the section “Support for Long String Operations” in the chapter “Server Configuration Options” in the Caché Programming Orientation Guide.

Full Text Indexing and Text Retrieval through SQL

The %Library.TextOpens in a new tab class and the %TextOpens in a new tab package has been provided to index text and to search textual data with SQL. To use the feature on an existing %StringOpens in a new tab property, change %StringOpens in a new tab to %TextOpens in a new tab and set the LANGUAGECLASS parameter. For English text, the declaration would be as follows:

 Property myNotes As %Text ( LANGUAGECLASS="%Text.English", MAXLEN=1000 );
    Index myFullTextIndex On myNotes(KEYS);

LANGUAGECLASS specifies the name of a helper class that provides the necessary interface to SQL and to the indexer so that efficient text indexing and text search may be carried out. Specifying a MAXLEN value (in bytes) is required for %TextOpens in a new tab properties.

The available text-aware predicates are %CONTAINS, %CONTAINSTERM, and %SIMILARITY.

Once the %TextOpens in a new tab property has been declared and optionally indexed, you can issue full text queries using the SQL %CONTAINS predicate, as follows:

SELECT plaintiff, legalBrief FROM transcript
    WHERE plaintiff = 'John Doe' AND
          legalBrief %CONTAINS ('neighbor', 'tree', 'roof')

The query above returns all transcripts where the plaintiff is 'John Doe' and where the terms 'neighbor' AND 'tree' AND 'roof' are in its legalBrief.

Caché includes language-specific parsers in the %TextOpens in a new tab package for English, Spanish, French, Italian, German, Japanese, Portuguese. While easy to use, the language specific subclasses can be configured to perform many sophisticated operations, such as word stemming to map various forms of the same word into a common root (block, blocks, blocking, and so on), or to support multi-word phrases (n-grams), or to filter out noise words, or to perform automatic text classification (for example, for junk-mail filtering), as well as other features.

Multi-word strings may be specified to %CONTAINS, even if the type class is not configured to support n-grams of the full length of the query. For example, the following predicate may be specified even if the %Text class is configured to store only individual words:

SELECT author FROM famousQuotations WHERE
  quotetext %CONTAINS('eggs in one basket')

The query above would return all authors where the documents contain exactly the specified phrases, even if the text class represents the document only with single words (NGRAMLEN=1). When the pattern is longer than the n-gram length as in the case above, SQL filters the rows with the "[" (contains) operator. Because the "[" operator is case-sensitive, the %CONTAINS predicate is also case-sensitive on patterns longer than NGRAMLEN. An implication is that NGRAMLEN can also affect which rows get returned, as in the following case:

 mission %CONTAINS('Fortune 5')

If NGRAMLEN >= 2, then only the rows containing 'Fortune 5' are returned.

If NGRAMLEN < 2, then rows containing 'Fortune 5', 'Fortune 50', 'Fortune 500', and so on may be returned, since all of these patterns satisfy the "[" test.

See the class documentation of the %Text.TextOpens in a new tab class and its language-specific subclasses (such as %Text.EnglishOpens in a new tab) for more information about the capabilities of the Text interface.