7.18.1 Specification format and rules for search conditions

(1) Specification format

search-condition ::= {[NOT] {(search-condition)|predicate|logical-value-specification}
              |search-condition OR {(search-condition)|predicate|logical-value-specification}
              |search-condition AND {(search-condition)|predicate|logical-value-specification}}
 
  logical-value-specification ::= {TRUE|FALSE}

To Page Top

(2) Explanation of specification format

NOT:

If NOT is specified, values that do not satisfy the search conditions become the target of retrieval. For example, if you specify NOT "USERID"='U00358', USERIDs other than U00358 are retrieved.

search-condition:

To specify multiple search conditions, connect the search conditions with AND or OR. A mixture of ANDs and ORs can be specified. The meanings of AND and OR are as follows:

• search-condition-1 AND search-condition-2

  Rows that satisfy both search-condition-1 and search-condition-2 will be subject to retrieval.

• search-condition-1 OR search-condition-2

  Rows that satisfy either search-condition-1 or search-condition-2 will be subject to retrieval.

predicate:

For details about predicates, see 7.19 Predicates.

logical-value-specification:

TRUE: If TRUE is specified for logical-value-specification, the result of logical value specification is true.

FALSE: If FALSE is specified for logical-value-specification, the result of logical value specification is false.

The following are examples of specifying search conditions.

Examples:

C1, C2, and C3 are column names.

• Specification examples using comparison predicates

  "C1">=100
  "C1"=?
  "C2"=CURRENT_DATE
  SUBSTR("C3",2,3)='150'

• Specification examples using the IN predicate, BETWEEN predicate, LIKE predicate, and NULL predicate

  "C1" IN (10,20)
  "C1" BETWEEN 100 AND 200
  "C3" LIKE 'M%'
  "C3" IS NULL

• Examples specifying multiple search conditions

  "C1"=100 AND "C2">=DATE'2011-09-06'
  "C1" IN (10,20) AND "C2">=DATE'2011-09-06'
  "C1"=10 OR "C1"=20
  "C2">=DATE'2011-09-04' AND ("C1"=10 OR "C2"=20)

The order of evaluation of search conditions is: items inside parentheses, NOT, AND, OR.

To Page Top

(3) Rules

1. A maximum of 255 logical operations can be specified in an SQL search condition.

2. The following figure shows the results of performing each logical operation.

   Figure 7‒3: Results of performing logical operations

To Page Top

(4) Examples

Search conditions are illustrated in the following examples.

Example 1

From the sales history table (SALESLIST), this example retrieves the customer ID (USERID), product code (PUR-CODE), and date of purchase (PUR-DATE) for customers who purchased product code P001 or P003 on or after September 4, 2011.

SELECT "USERID","PUR-CODE","PUR-DATE"
    FROM "SALESLIST"
        WHERE "PUR-DATE">=DATE'2011-09-04'
        AND ("PUR-CODE"='P001' OR "PUR-CODE"='P003')

The underlined portion indicates the search conditions specified in the WHERE clause.

Example 2

Using the data in the sales history table (SALESLIST), this example determines the sum and average of the quantities purchased on or after September 3, 2011 by product code (PUR-CODE).

Furthermore, retrieve only the product codes for which the sum of the quantities purchased is 20 or fewer.

SELECT "PUR-CODE",SUM("PUR-NUM"),AVG("PUR-NUM")
    FROM "SALESLIST"
        WHERE "PUR-DATE">=DATE'2011-09-03'
        GROUP BY "PUR-CODE"
        HAVING SUM("PUR-NUM")<=20

The underlined portion indicates the search condition specified in the HAVING clause.

Example 3

Insert a row from the products table (PRODUCTLIST) into the new products table (PRODUCTLIST_NEW). When inserting the row, change the product prices (PRICE) as follows:

• If the product code (PCODE) is P001: reduce the price by 10%

• If the product code is P002: reduce the price by 20%

• Otherwise: reduce the price by 30%

INSERT INTO "PRODUCTLIST_NEW"("PCODE","PRICE")
    SELECT "PCODE",CASE WHEN "PCODE"='P001' THEN "PRICE"*0.9
                        WHEN "PCODE"='P002' THEN "PRICE"*0.8
                        ELSE "PRICE"*0.7
                   END
        FROM "PRODUCTLIST"

The underlined portions indicate the search conditions specified in the CASE expression.

To Page Top