SQL Operations

This basic SQL operations supported include:

• String concatenation operations
• Assignment operations
• Arithmetic operations
• Ingresdate operations

String Concatenation Operations

To concatenate strings, use the + operator. For example:

'This ' + 'is ' + 'a ' + 'test.'

gives the value:

'This is a test.'

Use the concat function to also concatenate strings. For details, see String in the chapter "Introducing SQL Data Types."

Assignment Operations

An assignment operation places a value in a column or variable. Assignment operations occur during the execution of insert, update, fetch, create table as...select, and embedded select statements. Assignments can also occur within a database procedure.

When an assignment operation occurs, the data types of the assigned value and the receiving column or variable must either be the same or comparable.

• If the data types are not the same, comparable data types are converted.
• If the data types are not comparable, convert the assignment value into a type that is the same or comparable with the receiving column or variable.

For information about the type conversion functions, see Default Type Conversion.

Character String Assignment

All character types are comparable with one another and with integer and float types. Any character string can be assigned to any column or variable of character data type. A character string can also be assigned to a column or variable of integer or float type, as long as the content of the string is a valid numeric value. The result of the assignment depends on the types of the assignment string and the receiving column or variable.

String Truncation

If an attempt is made to insert a string value into a table column that is too short to contain the value, the string is truncated.

String truncation can occur as a result of the following statements:

• Copy
• Create table...as select
• Insert
• Update

-string_truncation Flag—Specify Error Handling for String Truncation

To specify error handling for string truncation, use the -string_truncation=option flag on the connect statement, specified when a session connects to a database.

The option can be one of the following:

• ignore

  (Default) Truncates and inserts the string. No error or warning is issued.

• fail

  Does not insert the string, issues an error, and aborts the statement.

This flag can also be specified on the command line for Ingres operating system commands that accept SQL option flags. For details about SQL option flags, see the sql command description in the Command Reference Guide.

Numeric Assignments

All numeric types are compatible with one another and with character types. Money is compatible with all of the numeric and string data types.

Numeric assignments follow these rules:

• The DBMS Server can truncate leading zeros, or all or part of the fractional part of a number if necessary. If truncation of the non-fractional part of a value (other than leading zeros) is necessary, an overflow error results. These errors are reported only if the -numeric_overflow flag is set to warn or fail. For information about the -numeric_overflow flag, see the sql command description in the Command Reference Guide.
• If the receiving column or variable specifies more digits to the right of the decimal point than is present in the assignment value, the assignment value is padded with trailing zeros.
• When a float, float4, decimal, or money value is assigned to an integer column or variable, the fractional part is truncated.
• When a decimal value with a scale greater than two is assigned to a money column or variable, the fractional value is rounded.

ANSI Date/Time

The ANSI date/time data types are compatible between themselves and string columns, and follow the rules of coercion described in Coercion Between Date/Time Data Types.

The date/time values of all date/time types can be assigned to string columns. The result is a display version of each value. String values can also be assigned to date/time columns, as long as the string values correspond to the acceptable input format for the particular date/time type.

Note: When a date/time value is retrieved from a database, it is always presented to the user in the appropriate display format in a string variable.

Ingresdate

The ingresdate data type is compatible with string data types if the value in the string is a valid representation of a date or time input format.

Absolute date or interval column values can be assigned to an ingresdate column. In addition, a string literal, a character string host variable, or a character string column value can be assigned to an ingresdate column if its value conforms to the valid input formats for Ingres dates or times.

When a date value is assigned to a character string, the DBMS Server converts the date to the display format. For more information about date display formats, see Date and Time Display.

Types of Logical Keys

There are two types of logical keys:

Table_key—This is comparable only with another table_key or a char that has a length of 8 bytes (char(8)).

Object_key—This is comparable only with another object_key or a char that has a length of 16 bytes (char(16)).

If a logical key column is declared as system_maintained, the DBMS Server assigns the values to that column. System_maintained logical key columns cannot be updated. If a logical key column is declared as not system_maintained, values must be assigned to the column.

In embedded SQL programs, if values are assigned using host variables, the host variables must be char(8)-comparable for table_key columns, and char(16)-comparable variables for object_key columns.

Values can be assigned to logical keys, not system_maintained, using a hex constant or a string literal. For information about the format of a hex constant, see String Literals.

Values assigned to table_keys must be 8 bytes long. Values assigned to object_keys must be 16 bytes long. The following example inserts the value 1 into a table_key column using a hex constant:

insert into test (tablekey) values (table_key(X'0000000000000001'));

The previous statement inserts 7 bytes containing 0, followed by 1 byte containing 1. The value is explicitly converted to a table key using the table_key conversion function.

The following example assigns the value 'abc' (padded to 8 characters for data type compatibility) to a logical key column:

insert into test (tablekey) values (table_key('abc'));

Null Value Assignment

A null can be assigned to a column of any data type if the column was defined as a nullable column. A null can also be assigned to a host language variable if there is an indicator variable associated with the host variable. For more information about indicator variables, see Indicator Variables in the chapter "Working with Embedded SQL."

To ensure that a null is not assigned to a column, use the Ifnull Function.

Arithmetic Operations

An arithmetic operation combines two or more numeric expressions using the arithmetic operators to form a resulting numeric expression. For details about arithmetic operators, see Arithmetic Operators.

Before performing any arithmetic operation, the participating expressions are converted to identical data types. After the arithmetic operation is performed, the resulting expression too has that storage format.

Default Type Conversion

When two numeric expressions are combined, the data types of the expressions are converted to be identical; this conversion determines data type of the result. The expression having the data type of lower precedence is converted to the data type of the higher. The order of precedence among the numeric data types is, in highest-to-lowest order:

• money
• float4
• float
• decimal
• integer8 (bigint)
• integer
• smallint
• integer1 (tinyint)

For example, in an operation that combines an integer and a floating point number, the integer is converted to a floating point number. If the operands are two integers of different sizes, the smaller is converted to the size of the larger. The conversions are done before the operation is performed.

The following table lists the data types that result from combining numeric data types in expressions:

For example, for the expression:

(job.lowsal + 1000) * 12

the first operator (+) combines a float4 expression (job.lowsal) with a smallint constant (1000). The result is float4. The second operator (*) combines the float4 expression with a smallint constant (12), resulting in a float4 expression.

To convert one data type to another, use data type conversion functions. For details, see Data Type Conversion in this chapter.

Arithmetic Operations on Decimal Data Types

In expressions that combine decimal values and return decimal results, the precision (total number of digits) and scale (number of digits to the right of the decimal point) of the result can be determined, as shown in the following table:

For example, in the following decimal addition operation:

1.234 + 567.89

the scale and precision of the result is calculated as follows:

Precision = 7

Calculated as 3 (largest number of fractional digits) + 3 (largest number of non-fractional digits) + 1 = 7

Scale = 3

The first operand has the largest number of digits to the right of the decimal point.

Result:

0569.124

If exponentiation is performed on a decimal value, the resulting data type is float.

Specify Error Handling for Arithmetic Errors

To specify error handling for numeric overflow, underflow and division by zero, use the connect statement numeric_overflow=option flag. Error-handling options are:

This flag can also be specified on the command line for Ingres operating system commands that accept SQL option flags. For details about SQL option flags, see the sql command description in the Command Reference Guide.

Date/Time Arithmetic

The following arithmetic is supported for date/time data types. Any combinations that do not conform result in errors E_AD5068_ANSIDATE_DATESUB and E_AD5069_ANSIDATE_DATEADD.

ANSI Date/Time Arithmetic

The following arithmetic operations can be coded for the ANSI date/time types. These operations allow the computation of intervals from date/time values and new date/time values from existing date/time values and intervals.

In all of these operations, the operands must be compatible. For example, you can subtract a date value from another date value or from a timestamp, but you cannot subtract a date value from a time value, or vice versa. Likewise, only intervals of the same type (YEAR TO MONTH or DAY TO SECOND) can be combined in an operation and a YEAR TO MONTH interval cannot be added to or subtracted from a TIME value.

ANSI Date/Time Comparisons

ANSI date/time values can be compared only to values of the same type. When the timezone setting of a time or timestamp value does not match that of its comparand, time or timestamp values WITH TIME ZONE or WITH LOCAL TIME ZONE are considered to be stored in GMT and can be compared directly. If one comparand is time or timestamp WITH TIME ZONE or WITH LOCAL TIME ZONE and the other comparand is WITHOUT TIME ZONE, the value of the WITHOUT TIME ZONE comparand is assumed to be in local time of the session default time zone. It is converted to GMT based on that assumption and the comparison proceeds.

Ingresdate Arithmetic

Ingresdate values can be added and subtracted, but cannot be multiplied or divided. The following lists the results of date arithmetic:

Addition:

Subtraction:

When adding intervals, each of the units is added.

For example:

date('6 days') + date('5 hours')

yields, 6 days 5 hours, while:

date('4 years 20 minutes') + date('6 months 80 minutes')

yields, 4 years 6 months 1 hour 40 minutes.

In the above example, 20 minutes and 80 minutes are added and the result is 1 hour 40 minutes. 20 minutes plus 80 minutes equals 100 minutes, but this result overflows the minute time unit because there are 60 minutes in an hour. Overflows are propagated upward except when intervals are added. In the above example, the result is 1 hour 40 minutes. However, days are not propagated to months. For example, if 25 days is added to 23 days, the result is 48 days.

When intervals or absolute dates are subtracted, the result is returned in appropriate time units. For example, if the following subtraction is performed:

date('2 days') - date('4 hours')

the result is 1 day 20 hours.

Date constants can be converted into numbers of days relative to an absolute date. For example, to convert today's date to the number of days since January 1, 1900:

num_days = int4(interval('days', 'today' - date('1/1/00')))

To convert the interval back to a date:

(date('1/1/00') + concat(char(num_days), ' days'))

where num_days is the number of days added to the date constant.

Adding a month to a date always yields the same date in the next month. For example:

date('1-feb-98') + '1 month'

yields March 1.

If the result month has fewer days, the resulting date is the last day of the next month. For instance, adding a month to May 31 yields June 30, instead of June 31, which does not exist. Similar rules hold for subtracting a month and for adding and subtracting years.

Dates that are stored without time values can acquire time values as a result of date arithmetic. For example, the following SQL statements create a table with one date column and store today's date (with no time) in the column:

create table dtest (dcolumn date);
insert into dtest (dcolumn) values (date('today'));

If the contents of the date column is selected using the following query:

select dcolumn from dtest;

a date with no time is returned. For example:

09-aug-2001

If date arithmetic is used to adjust the value of the date column, the values in the column acquire a time. For example:

update dtest set dcolumn=dcolumn-date('1 hour');
select dcolumn from dtest;

returns the value:

08-aug-1998 23:00:00

Comparing Ingresdates

In comparisons, a blank (default) ingresdate is less than any interval ingresdate. All interval ingresdates are less than all absolute ingresdates. Intervals are converted to comparable units before they are compared. For instance, before comparing ingresdate('5 hours') and ingresdate('200 minutes'), both the hours and minutes are converted to milliseconds internally before comparing the values. Ingresdates are stored in Greenwich Mean Time (GMT). For this reason, 5:00 PM Pacific Standard Time is equal to 8:00 PM Eastern Standard Time.

Note: Support of blank date values and the ability to compare absolute and interval values are available only with the ingresdate type and not with the ANSI date/time data types.