SQL Series...20

Value Expressions

An expression may be simple or complex. The expression can contain literal values, column names, parameters, host variables, subqueries, logical con-nectives, and arithmetic operators. Regardless of its complexity, an expres-sion must reduce to a single value.

For this reason, SQL expressions are commonly known as value expressions. Combining multiple value expressions into a single expression is possible, as long as the component value expressions reduce to values of compatible data types.

SQL has five kinds of value expressions:

String value expressions

Numeric value expressions

Datetime value expressions

Interval value expressions

Conditional value expressions

String value expressions

The simplest string value expression is a single string value specification. Other possibilities include a column reference, a set function, a scalar subquery, a CASE expression, a CAST expression, or a complex string value expression. Only one operator is possible in a string value expression: the concatenation opera-tor. You may concatenate any of the expressions I mention in the preceding bulleted list with another expression to create a more complex string value expression. A pair of vertical lines (||) represents the concatenation opera-tor. The following table shows some examples of string value expressions.

Expression	Produces
‘Peanut ‘ || ‘brittle’	‘Peanut brittle’
‘Jelly’ || ‘ ‘ || ‘beans’	‘Jelly beans’
FIRST_NAME || ‘ ‘ || LAST_NAME	‘Joe Smith’
B’1100111’ || B’01010011’	B’110011101010011’
‘’ || ‘Asparagus’	‘Asparagus’
‘Asparagus’ || ‘’	‘Asparagus’
‘As’ || ‘’ || ‘par’ || ‘’ || ‘agus’	‘Asparagus’

As the table shows, if you concatenate a string to a zero-length string, the result is the same as the original string.

Numeric value expressions

In numeric value expressions, you can apply the addition, subtraction, multi-plication, and division operators to numeric-type data. The expression must reduce to a numeric value. The components of a numeric value expression may be of different data types as long as all are numeric. The data type of the result depends on the data types of the components from which you derive the result. The SQL:2003 standard doesn’t rigidly specify the type that results from any specific combination of source expression components because of differences among hardware platforms. Check the documentation for your specific platform when mixing numeric data types.

Here are some examples of numeric value expressions:

-27

49 + 83

5 * (12 - 3)

PROTEIN + FAT + CARBOHYDRATE FEET/5280

COST * :multiplierA

Datetime value expressions

Datetime value expressions perform operations on data that deal with dates and times. These value expressions can contain components that are of the types DATE, TIME, TIMESTAMP, or INTERVAL. The result of a datetime value expression is always a datetime type (DATE, TIME, or TIMESTAMP). The follow-ing expression, for example, gives the date one week from today:

CURRENT_DATE + INTERVAL ‘7’ DAY

Times are maintained in Universal Time Coordinated (UTC) — known in Great Britain as Greenwich Mean Time — but you can specify an offset to make the time correct for any particular time zone. For your system’s local time zone, you can use the simple syntax given in the following example:

TIME ‘22:55:00’ AT LOCAL

Alternatively, you can specify this value the long way:

 

TIME ‘22:55:00’ AT TIME ZONE INTERVAL ‘-08.00’ HOUR TO MINUTE

This expression defines the local time as the time zone for Portland, Oregon, which is eight hours earlier than that of Greenwich, England.

Interval value expressions

If you subtract one datetime from another, you get an interval. Adding one datetime to another makes no sense, so SQL doesn’t permit you to do so. If you add two intervals together or subtract one interval from another inter-val, the result is an interval. You can also either multiply or divide an interval by a numeric constant.

SQL has two types of intervals: year-month and day-time. To avoid ambigui-ties, you must specify which to use in an interval expression. The following expression, for example, gives the interval in years and months until you reach retirement age:

(BIRTHDAY_65 - CURRENT_DATE) YEAR TO MONTH

The following example gives an interval of 40 days:

INTERVAL ‘17’ DAY + INTERVAL ‘23’ DAY

The example that follows approximates the total number of months that a mother of five has been pregnant (assuming that she’s not currently expect-ing number six!):

INTERVAL ‘9’ MONTH * 5

Intervals can be negative as well as positive and may consist of any value expression or combination of value expressions that evaluates to an interval.

Conditional value expressions

The value of a conditional value expression depends on a condition. The con-ditional value expressions CASE, NULLIF, and COALESCE are significantly more complex than the other kinds of value expressions. In fact, these three conditional value expressions are so complex that I don’t have enough room to talk about them here.