Predicates

• ANY

  The ANY predicate evaluates TRUE when the comparison predicate that goes with it evaluates TRUE for any value from the subquery. The SOME predicate is functionally the same as ANY. In the example below, for any record to be retrieved from the SURVEYS table, the value in the DEPTH field need only be greater than any one of the values returned in the subquery’s DEPTH column.

  SELECT *

  FROM Surveys S

  WHERE (S.Depth > ANY

    (SELECT H.Depth

    FROM headers))

  While the subquery providing the comparison values for the outer query may return multiple records, it may only return values for one field.

• ALL

  The ALL predicate evaluates TRUE when the comparison that goes with it evaluates TRUE for all values from the subquery. In the example below, for any record to be retrieved from the SURVEYS table, the value in the DEPTH field needs to be greater than every value returned in the subquery’s DEPTH column.

  SELECT *

  FROM Surveys S

  WHERE (S.Depth > ALL

    (SELECT H.Depth

    FROM headers))

  While the subquery providing the comparison values for the outer query may return multiple records, it may only return values for one field.

• IS NULL

  Use IS NULL to determine whether any records contain NULL (empty) values for a specified field.

  <field_name> IS [NOT] NULL

  For example:

  SELECT *

  FROM headers

  WHERE (azim IS NULL)

  You can use IS NOT NULL to return records where the values for a specified field are not NULL.

• BETWEEN

  Use the BETWEEN comparison predicate to determine whether a value falls within a specified range.

  <value_1> [NOT] BETWEEN <value_2> AND <value_3>

  Use NOT to return the records that do not fall within a specified range. In the example below, the statement returns a TRUE value .

  Remember you can use the CAST function to convert incompatible data types. The values used in a BETWEEN comparison may be field values, literal values, or calculated values.

  SELECT PROJECT, SUBPROJ, BHID, XCOORD, YCOORD, ZCOORD

  FROM G_HEADER

  WHERE YCOORD BETWEEN 20000 AND 50000

  BETWEEN is useful when you want to filter records to retrieve contiguous values. To retrieve records with values that are non-contiguous use the IN predicate.

• EXISTS

  Use the EXISTS comparison predicate to filter a table based upon the existence of field values returned as a result of a subquery. EXISTS returns a true value if the subquery has at least one row in its result set, otherwise it returns false.

  The subquery is executed once for each record in the table being filtered. If the result of the subquery is true then the current record is included in the filtered result set.

  For example:

  SELECT C.hole, C.easting, C.northing, C.RL, C.length

  FROM collars C

  WHERE EXISTS

    (SELECT L.hole

    FROM Lithology L

    WHERE (C.hole = L.hole) AND (L.lith = "CLAY"

   ) )

  You can Use NOT EXISTS to reverse the result of an EXISTS comparison.

• IN

  Use the IN comparison predicate to determine whether a value exists in a set of values. The set of comparison values may be either:

  • static - a list of comma-separated literal values
  • dynamic - the result set from a subquery

  <some_value> [NOT] IN (value_set)

  In the following example, a comma-delimited list of literal comparison values is used:

  SELECT L.hole, L.from, L.to

  FROM lithology L

  WHERE (L.lith IN ("CLAY", "LIME"))

  The comparison set can also be the result set from a subquery. The subquery may return multiple rows, but must only return a single field for comparison.

  SELECT E.company, E.region

  FROM exploration E

  WHERE (E.region IN

    (SELECT R.region

    FROM regions R

    WHERE (R.region = "Laverton")))

  • Use NOT IN to return records that are not present in the result set of the subquery.
  • Remember you can use the CAST function to convert incompatible data types.
  • IN is useful when you want to filter records to retrieve non-contiguous values. To retrieve records with values that fall within a range of contiguous values use the BETWEEN predicate.

• LIKE

  Use the LIKE comparison predicate to filter a table based on the similarities between a field value and one or more comparison values. The comparison performed by the LIKE predicate is case-sensitive.

  You can use substitution characters to allow the comparison to be based on a partial value rather than a whole value.

  SELECT *

  FROM lithology

  WHERE (lith LIKE "b%")

  Use the wildcard substitution character ("%") in a comparison to represent an unknown number of characters. LIKE returns TRUE when the portion of the column value matches that portion of the comparison value before, after, or at either side of the wildcard character.

  Use the single-character substitution character ("_") to represent a single character. LIKE returns TRUE when the portions of the field value delimited by the wildcard character are matched to portions of the value of the field being compared.

  SELECT code

  FROM geology

  WHERE (code LIKE "cz_")

  Use NOT LIKE to reverse the result of a LIKE comparison.

  You can use an escape character when the wildcard characters "%" or "_" appear as data in the field being compared.

  The ESCAPE keyword designates an escape character. The character that follows the escape character is treated as a data character and not a wildcard. Other wildcard characters in the comparison value are unaffected.

  In the example below, the character "!" is designated as an escape character. The escape character is used in the comparison string to treat the character that comes after it as an underscore rather than a wildcard. This allows filtering based on drillhole IDs such as "WV020_ID", "WV021_ID", "WV022_ID" etc.

  SELECT *

  FROM Surveys

  WHERE (BHID LIKE "WV02%!_ID" ESCAPE "!")

  The LIKE predicate can be used only with CHAR or compatible data types. Remember you can use the CAST function to convert an incompatible data type.

Statements