Search conditions and expressions

When using statements such as SELECT, INSERT, UPDATE, and DELETE, you can use search conditions to qualify the data returned, inserted, updated, or deleted. A search condition is a comparison of two expressions or some other type of predicate. AND and OR can be used to combine predicates in a search condition. Expressions are usually comprised of any of the following:

See the following for more information:

You can use the expression builder in SQL Assist to build expressions. See SQL Assist for more details.

For a complete listing of search conditions and expressions, see SQL Reference and the SQL Programming in the Information CenterLink to Information center

Predicates

A predicate specifies a condition that is true, false, or unknown about a given row or group. A basic predicate compares two values. For example:

SELECT * 
  FROM CORPDATA.EMPLOYEE
  WHERE SALARY + BONUS + COMM < 2000

Other types of predicates include BETWEEN, EXISTS, IN, LIKE, and NULL. You can also use the keyword NOT with any of these predicates. Subqueries are also useful types of predicates.

BETWEEN: BETWEEN finds a values in a range. For example, find all columns where the employee's salary is between 20000 and 40000:

SELECT * 
  FROM CORPDATA.EMPLOYEE
  WHERE SALARY BETWEEN 20000 AND 40000

EXISTS: EXISTS tests for the existence of certain rows in a subselect. The result is true only if the subselect returns at least one row. For example:

SELECT EMPNO, LASTNAME 
  FROM CORPDATA.EMPLOYEE
  WHERE EXISTS
    (SELECT *
      FROM CORPDATA.PROJECT
      WHERE PRSTDATE > '1982-01-01')

IN: IN compares a value with a set of values. For example, to find all employees in any of departments D01, B01, and C01:

SELECT * 
  FROM CORPDATA.EMPLOYEE
  WHERE WORKDEPT IN ('D01' , 'B01' , 'C01')

LIKE: LIKE searches for strings that have a certain pattern. Characters in the search string can be represented by a underscore sign (_) or a percent sign (%). An underscore represents a single character while percent sign represents a string of zero or more characters. For example, to find all of the employees with a first name that begins with the letter 'J':

SELECT * 
  FROM CORPDATA.EMPLOYEE
  WHERE EMPLOYEE.FIRSTNME LIKE 'J%'

NULL: NULL tests for null values. For example:

SELECT * 
  FROM CORPDATA.EMPLOYEE
  WHERE DEPTNO IS NOT NULL

Comparing a column to NULL is not allowed. For example, DEPTNO = NULL returns an error.

Use functions

A function is an operation denoted by a function name followed by one or more operands that are enclosed in parentheses. It represents a relationship between a set of input values and a set of result values. The input values to a function are called arguments. For example, a function can be passed two input arguments that have date and time data types and return a value with a timestamp data type as the result.

There are several different types of functions:

Built-in
User-defined

Additionally, functions can be broken down into column and scalar. A column function receives a set of values for each argument (such as the values of a column) and returns a single-value result for the set of input values. Column functions are sometimes called aggregating functions. A scalar function receives a single value for each argument and returns a single-value result. Built-in functions and user-defined functions can be either column or scalar. User-defined functions that are created for distinct types are always scalar functions.

To find a complete listing of built-in functions, see Function list. For information about creating user-defined functions, see CREATE FUNCTION.

A function is invoked by its function name, which is implicitly or explicitly qualified with a schema name, followed by parentheses that enclose the arguments to the function. Within the database, each function is uniquely identified by its function signature, which is its schema name, function name, the number of parameters, and the data types of the parameters. Thus, a schema can contain several functions that have the same name but each of which have a different number of parameters, or parameters with different data types. Or, a function with the same name, number of parameters, and types of parameters can exist in multiple schemas. When you invoke any function, the database manager must determine which function to execute. This process is called function resolution. For more information about Function Resolution, see Function resolution in the SQL Reference topic in the Information CenterLink to Information center.

Special registers

A special register is a storage area that is defined by the database manage and is used to store information that can be referenced in SQL statements. The special registers include:

CURRENT CLIENT_ACCTNG
Specifies a VARCHAR(255) value that contains the value of the accounting string from the client information specified for this connection.

CURRENT CLIENT_APPLNAME
Specifies a VARCHAR(255) value that contains the value of the application name from the client information specified for this connection.

CURRENT CLIENT_PROGRAMID
Specifies a VARCHAR(255) value that contains the value of the client program ID from the client information specified for this connection.

CURRENT CLIENT_USERID
Specifies a VARCHAR(255) value that contains the value of the client user ID from the client information specified for this connection.

CURRENT CLIENT_WRKSTNNAME
Specifies a VARCHAR(255) value that contains the value of the workstation name from the client information specified for this connection.

CURRENT DATE
Specifies a date that is based on a reading of the time-of day clock when the SQL statement is executed.

CURRENT DEBUG MODE
Specifies whether SQL or Java procedures should be created or altered so they can be debugged by the Unified Debugger.

CURRENT DECFLOAT ROUNDING MODE
Specifies the rounding mode that is used when DECFLOATs are manipulated in dynamically prepared SQL statements.

CURRENT DEGREE
Specifies the degree of I/O or Symmetric MultiProcessing (SMP) parallelism for the execution of queries, index creates, index rebuilds, index maintenance, and reorganizes.

CURRENT PATH
Specifies the SQL path used to resolve unqualified distinct type names, procedure names, and function names in dynamically prepared SQL statements.

CURRENT SCHEMA
Specifies the value of the current schema special register.

CURRENT SERVER
Specifies a VARCHAR(18) value that identifies the current server.

CURRENT TIME
Specifies a time that is based on a reading of the time-of-day clock when the SQL statement is executed.

CURRENT TIMESTAMP
Specifies a timestamp that is based on a reading of the time-of-day clock when the SQL statement is executed.

CURRENT TIMEZONE
Specifies the difference between Universal Time Coordinated (UTC) and local time at the server.

SESSION_USER
Specifies the run-time authorization ID at the current server. The data type of the special register is VARCHAR(128).

SYSTEM_USER
Specifies the authorization ID that connected to the current server. The data type of the special register is VARCHAR(128).

USER
Specifies the run-time authorization ID at the current server. The data type is VARCHAR(18).

Examples: Special register

Using the PROJECT table, set the project end date (PRENDATE) of the MA2111 project (PROJNO) to the current date.

UPDATE CORPDATA.PROJECT
  SET PRENDATE = CURRENT DATE
  WHERE PROJNO = 'MA2111'

Using the CL_SCHED table, select all the classes (CLASS_CODE) that start (STARTING) later today. Today's classes have a value of 3 in the DAY column.

SELECT CLASS_CODE
  FROM CORPDATA.CL_SCHED
  WHERE STARTING CURRENT TIME AND DAY = 3

For more information about Spoecial registers, see Special registers in the SQL Reference topic in the Information CenterLink to Information center.