Querying the Database

A query is a statement which performs data retrieval and generates a result set. Query can consist of any of the following:

A SELECT statement selects one or more rows of data from one or more tables or views. A simple SELECT is shown in the following example:

SELECT Name,Age FROM Sample.Person ORDER BY Name

A SELECT statement includes the following elements (some of which are optional):

The SELECT list specifies one or more columns or expressions whose value are included within a query’s result set:

SELECT Name FROM Sample.Person

Multiple columns within the SELECT list are separated by commas:

SELECT Name,SSN FROM Sample.Person

You can provide columns in a SELECT list with an alias:

SELECT Name As TheName,SSN As TheSSN FROM Sample.Person

A SELECT list can include expressions as well as literal values:

SELECT 'Doctor ' || Name As TheName, Age * 2 As DoubleAge 
FROM Sample.Person

The FROM clause specifies one or more tables which provide the data for the query. In Caché SQL, the FROM clause is optional if no table data is referenced during the query.

The optional WHERE clause contains one or more conditions that are used to restrict the values returned by a query:

SELECT Name FROM Sample.Person
WHERE Name %STARTSWITH 'A'

The WHERE clause can contain multiple conditions connected by the AND and OR logical operators:

SELECT Name,Age FROM Sample.Person
WHERE (Name %STARTSWITH 'A') OR (Age > 50)

These conditions are referred to as predicates , which are logical tests to qualify or disqualify specific rows from the query selection. Most predicates can be inverted by preceding the predicate keyword with the NOT operator. For a list of supported predicates, refer to Overview of Predicates in the Caché SQL Reference.

Note:

In Caché SQL, expressions are evaluated in strict left-to-right order; there is no operator precedence. To avoid problems with complex expressions, use parentheses to explicitly specify the precedence of operators.

If a query includes division and there are any values in the database that could produce a divisor with a value of zero or a NULL value, you cannot rely on order of testing to avoid division by zero. Instead, use a case statement to suppress the risk.

The optional ORDER BY clause specifies one or more columns that should be used to sort the values returned by a query:

SELECT Name FROM Sample.Person ORDER BY Name

You can also specify whether ordering should be ascending (the default) or descending:

SELECT Name FROM Sample.Person ORDER BY Name DESC

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 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.

Caché supports what is called “free-text search,” which includes support for:

This feature enables SQL to support full text indexing, and also enables SQL to index and reference individual elements of a collection without projecting the collection property as a child table. While the underlying mechanisms that support collection indexing and full text indexing are closely related, text retrieval has many special properties, and therefore special classes and SQL features have been provided for text retrieval.

For details on the underlying classes that support these features, see the %Text.Text class.

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

 Property myDocument As %Text ( LANGUAGECLASS="%Text.English" );
    Index myFullTextIndex On myDocument(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. The default MAXLEN for %Text is 256 bytes.

Once the %Text property has been declared and optionally indexed, you can issue full text queries using the SQL %CONTAINS operator, 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 %Text 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
  document %CONTAINS('put all your eggs in one basket',
                     'but watch that 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 "[" operator. Because the "[" operator is case-sensitive, the %CONTAINS operator 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.Text class and its language-specific subclasses (such as %Text.English) for more information about the capabilities of the Text interface.

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.

You can use Dynamic SQL to return metadata about the query, such as the number of columns specified in the query, the name (or alias) of a column specified in the query, and the data type of a column specified in the query.

The following Caché ObjectScript Dynamic SQL example returns the column name and an integer code for the column's ODBC data type for all of the columns in Sample.Person:

  SET myquery="SELECT * FROM Sample.Person"
  SET rset = ##class(%SQL.Statement).%New()
  SET tStatus = rset.%Prepare(myquery)
    SET x=rset.%Metadata.columns.Count()
  WHILE x>0 {
    SET column=rset.%Metadata.columns.GetAt(x)
  WRITE !,x," ",column.colName," ",column.ODBCType
  SET x=x-1 }
  WRITE !,"end of columns"

In this example, columns are listed in reverse column order. Note that the FavoriteColors column, which contains list structured data, returns a data type of 12 (VARCHAR) because ODBC represents a Caché list data type value as a string of comma-separated values.

For further details, refer to the Dynamic SQL chapter of this manual, and the %SQL.Statement class in the InterSystems Class Reference.


	[Documentation] > [Development Guides] > [Using Caché SQL] > [Querying the Database]		Go to:		Search: