Chapter 2

SQL Elements

This chapter describes the following SQL elements:

• Tokens

• Names

• Data types

• Constants

• System keywords

• Database sequence objects

• Collations

• Functions

• Expressions

• Predicates

• Search conditions

• Bind variables

Tokens

A token is a single character string in a SQL statement. Tokens include, but are not limited to, identifiers, constants, bind variable names, and keywords.

SQLBase restricts the length of tokens to 8 kilobytes. Some kinds of tokens, such as identifiers, actually have much more restricted lengths, as described below.

Names

A name is called an identifier in SQL. User names, table names, column names, and index names are examples of identifiers.

An identifier can be an ordinary identifier or a delimited identifier.

• An ordinary identifier begins with a letter or one of the special characters (_, #, @ or $). The rest of the name can include letters, numeric digits, and special characters. An exception is a database identifier, which cannot begin with a numeric digit and cannot contain special characters. In addition, ordinary identifiers cannot contain whitespace characters such as blanks, tabs, or carriage returns. The detailed rules for the first character of an identifier are on the ICU web site, as are the rules for subsequent characters of an identifier.

• A delimited identifier can contain any character including special characters such as blanks and periods. Also, a delimited identifier can start with a digit. A delimited identifier is case-sensitive.

Delimited identifiers must be enclosed in double quotes:

"7.g identifier"

SQL reserved words can be used as identifiers if they are delimited, but this is not recommended.

If a delimited identifier contains double quotes, then two consecutive double quotes ("") are used to represent one double quote (").

The maximum length of an identifier is 36 characters. Versions of SQLBase prior to 8.0 had a maximum length of 18 for these identifiers.

There are two exceptions to the length of identifiers. Database names are limited to 16 characters, and audit names are limited to 8 characters.

Database server names are also limited to 8 characters, but server name is not a SQL element.

Names, Case-Sensitivity, and Quotes

When names are declared without using delimiting double quotes, SQLBase creates the object with the name in uppercase. Thereafter you can refer to the object using any case (lower, mixed, upper) and SQLBase will translate your reference to uppercase. For example, you can create a table using the following command:

create table example ( first_col CHAR(6), second_col CHAR(8) );

When the table is created, its actual name is EXAMPLE (uppercase), and the column names within it are also uppercase. You can then run a query like this:

select * from Example

select * from example

select * from EXAMPLE

All of these queries will use table EXAMPLE.

However, if you delimit an object’s name with double quotes when you define it, like so:

create table "example" ( "first_col" CHAR(6), "second_col" CHAR(8) );

...then the actual table name in the database will be lowercase example. Now what happens when you run this query?

select * from example;

If you try to use the table name without enclosing it in quotes, SQLBase will return an error, because SQLBase will presume that the name is uppercase, and it will be unable to find a table named EXAMPLE.

select * from "example";

This version of the query will succeed.

If case matters, use double quotes

Remember, if an actual object name contains one or more lowercase characters, you must delimit that object name with double quotes when declaring it, and when using it in a query or command.

Remember that if the name itself contains a double quote, you must represent that character by typing two consecutive double quotes.

Note: Most database object names (identifiers) follow the SQLBase convention of being presumed to be uppercase unless they are delimited by double quotes. The only exceptions are authorization IDs (user names), passwords, stored procedures, stored commands, and database names, which are always interpreted as uppercase.. For such identifiers you should not use double quotes as delimiters.

Examples of names

Examples of names are:

CHECKS

AMOUNT_OF_$

:CHKNUM

$500

"NAME & NO."

#CUSTOMER

:3

Types of names

The following objects have names:

• Authorization ID

• Bind Variables

• Columns

• Commands

• Correlations

• Databases

• External functions

• Indexes

• Passwords

• Stored Procedures

• Synonyms

• Tables

• Triggers

• Views

Authorization ID (user name)

This is a short identifier that designates a user. Authorization ID is also called user name in this manual. The system keyword USER contains the user name.

An authorization ID is an implicit part of all database object names. To name a database object explicitly, add the authorization ID and a period to the beginning of the identifier. For example, the table name CUST created by user JOE has the explicit name JOE.CUST. The implicit name CUST is used most often.

Examples of authorization IDs are JOE and USER1.

Column name

This is a qualified or unqualified long identifier that names a column of a table or view.

The qualified form is preceded by a table name, a view name, or correlation name and a period (.). That preceding name might be further preceded by an authorization name like a user ID.

Examples of column names are EMPNO, EMPLOYEES.EMPNO, and SYSADM.EMPLOYEES.EMPNO.

Correlation name

This is a long identifier that designates a table or view within a command.

Examples of correlation names are X and TEMP.

Database name

This is a short identifier that designates a database.

Database names can only contain alphanumeric characters (A-Z, a-z, 0-9), and must start with a letter.

Do not specify an extension for a database name. For example, DEMO.XYZ is invalid. SQLBase automatically assigns a database name extension of .DBS. SQLBase will store a database called DEMO in a file named DEMO.DBS.

Examples of database names are DEMO and COMPANY.

External function name

This is an unqualified ordinary long identifier (maximum 64 characters) that names an external function. An example is MyFunc().

A function name must start with an alpha upper or lowercase letter. It cannot be the same as procedure, or a name used in any of the SQLBase aggregate functions.

If the name is declared without being delimited by double quotes, then calls to that external function must be made in UPPERCASE only. For this reason, and because external DLLs sometimes have case-sensitivity, it is recommended that both the declaration of an external function and the call to an external function use delimiting double quotes.

Index name

This is a qualified or unqualified long identifier that names an index.

The qualified form is preceded by an authorization ID and a period.

An unqualified index name is implicitly qualified by the authorization ID of the user who gave the command.

Examples are EMPX and JOE.EMPX.

Password

This is a short identifier that is a password for an authorization ID. Examples are PWD1 and X2381.

Procedure name

This is a qualified or unqualified long ordinary identifier that names a procedure. An example is JOE.PROC.

A procedure name can be different from the name under which it is stored. However, a procedure name cannot be the same name as an external function name.

Bind variable name

Bind variable names in a SQL command must always be ordinary identifiers or digits preceded by a colon (:).

Command name

This is a long identifier that designates a user-defined command. An example is QUERY1.

Synonym name

This is a long identifier that designates a table or view. A synonym can be used wherever a table name or view name can be used to refer to a table or view.

An example of a synonym is EASY.

Table name

This is a qualified or unqualified long identifier that names a table.

An unqualified table name is implicitly qualified by the authorization ID of the user who created the table.

The qualified form is preceded by an authorization ID and a period.

Examples are EMP and JOE.EMP.

Note: Maximum username length is 8 characters and tablename is 36 characters. In a quoted, fully-qualified tablename of the form "username.tablename" the total number of characters must not exceed 47, including period and quotes.

Trigger name

This is a qualified or unqualified long ordinary identifier that names a trigger. An example is JOB_UPDT.

View name

This is a qualified or unqualified long identifier that designates a view.

An unqualified view name is implicitly qualified by the authorization ID of the user who gave the command.

The qualified form is preceded by an authorization ID and a period.

Examples of view names are MYEMP and DEPT10.MYEMP.

Summary of naming requirements

The following table lists the naming requirements for any type of name.

Type of Identifier

Maximum Length

Qualifiable?

Authorization ID

36

No

Bind Variable

36

N/A

Column

36

Yes

Command

36

Yes

Correlation

36

No

Database

16

No

External function

64

No

Index

36

Yes

Password

36

No

Procedure

36

Yes

Synonym

36

No

Table

36

Yes

Trigger

36

Yes

View

36

Yes

Data types

The general data types that SQLBase uses to store data are:

• Character

• Binary

• Numeric

• Date and time

The data type determines the following information:

• The value and length of the data as stored in the database.

• The display format when the data is displayed.

The data type for a column is specified in the CREATE TABLE command.

Null values

A null value indicates the absence of data. Any data type can contain a null value. A null value has no storage.

Null is not equivalent to zero or to blank; it is the same as unknown. A value of null is not greater than, less than, or equivalent to any other value, including another value of null. To retrieve a field on a null match, the NULL predicate must be used.

NULL is equal to NULL when you insert two of them into a uniquely constrained column.

Empty strings have a null value.

Read the section Search conditions to understand more about how nulls are treated.

Character data types

A character string is a sequence of letters, digits, or special characters. All character data is stored in SQLBase as variable-length strings. Strings may contain ANSI characters or national characters (Unicode) - there are separate data types for each character type. SQLBase implicity converts between ANSI and Unicode as necessary without the need to call explicit functions.

SQLBase allows several alternative keywords to declare the same data type.

An empty string has a null value.

All character data types can store binary data. However, GUPTA recommends that you store binary data in one of the binary data types listed below, to take advantage of enhancements in the SQLBase API.

Character data is stored as case-sensitive. To search for case-insensitive data, you can issue a SELECT statement with the @UPPER or @LOWER functions. For example, the following query returns only upper-case SMITHS:

SELECT LNAME FROM EMP

WHERE @UPPER(LNAME) = 'SMITH';

CHAR (or VARCHAR)

A length must be specified for this data type. The length determines the maximum length of the string. The length cannot exceed 1000 characters. SQLBase documentation occasionally refers to these as ANSI characters, as contrasted with national characters (Unicode), which are described in a section below.

You can use CHAR columns in comparison operations with other characters or numbers and, and also in most functions and expressions. Wild-card search operators can be used in the LIKE predicate for character-only comparisons.

This data type is defined in the system catalog as CHAR and VARCHAR.

Examples:

CHAR (11)

VARCHAR(25)

CHAR(10)

LONG VARCHAR (or LONG or LONG CHARACTER or CLOB)

This data type stores strings of any length. The difference between a CHAR (VARCHAR) and a LONG (LONG VARCHAR) data type is that a LONG type can store strings longer than 1000 characters, and is not specified with a length attribute.

In earlier versions of SQLBase both text and binary data were stored in LONG VARCHAR. However, GUPTA strongly recommends that you avoid using LONG VARCHAR to store binary data. Use BINARY or LONG VARBINARY instead.

LONG VARCHAR columns can be stored, retrieved, or modified, but cannot be used in a comparison operation in a WHERE clause. LONG VARCHAR columns cannot be used in indexes, expressions, or in most functions.

You can use LONG VARCHAR as a Large Object (LOB) equivalent - hence the synonym CLOB.

A LONG datatype is stored as a linked list of pages. Since it is variable length, no space is pre-allocated. This means that if no data is entered, no pages are allocated, and if data is entered, only enough pages to hold the long are allocated. However, there is a minimum allocated space of one page for non-null values. Space is allocated by page.

Maximum length for this data type is 4GB.

Example:

LONG VARCHAR

NCHAR (or NVARCHAR, UCHAR, or UVARCHAR)

The NCHAR data type behaves identically to CHAR, except that it stores national characters (Unicode) rather than ANSI characters. This datatype allows a maximum of 1000 characters.

LONG NVARCHAR (or NCLOB)

LONG NVARCHAR data type behaves identically to LONG VARCHAR, except that it stores national characters (Unicode) rather than ANSI characters. The only limit on the length of the data is the 4GB limit that applies to all long datatypes.

Note: Long datatypes, both character and binary, cannot be used in any way that involves comparisons. That includes operators such as = and >, predicates such as GROUP BY and ORDER BY, and indexes.

Binary data types

Although it is possible to store binary data in character data types such as CLOB or NCLOB, GUPTA recommends that you store binary data in one of the binary data types listed here, to take advantage of enhancements in the SQLBase API.

BINARY (or VARBINARY)

This data type stores up to 1000 bytes of binary data. A length must be specified for this data type.

LONG VARBINARY (or BLOB)

This data type stores binary data up to 4GB in size. Length is not specified for this data type.

Numeric data types

SQLBase allows these numeric data types:

Exact Data Types

Approximate Data Types

DECIMAL (or DEC)
INTEGER (or INT) SMALLINT

DOUBLE PRECISION
NUMBER
FLOAT
REAL

SQLBase uses its own internal representation of numbers described in the SQL/API Reference Manual. Data is cast on input and output to conform to the restrictions of the data type.

Precision and scale are maintained internally by SQLBase:

• Precision refers to the total number of allowable digits

• Scale refers to the number of digits to the right of the decimal point.

Numbers with up to 15 decimal digits of precision can be stored in the exact data types. Numbers in the range of 1.0e-99 to 1.0e+99 can be stored in the approximate data types.

SQLBase supports integer arithmetic. For example:

INTEGER / INTEGER = INTEGER

Number columns can be used in any comparison operations with other numbers and can occur in all functions and expressions.

NUMBER

NUMBER is a superset of all the other numeric data types and supports the widest range of precision and scale (up to the maximum 22 digits allowed by SQLBase numeric types).

Example:

NUMBER

Use NUMBER in either of the following situations:

• You do not need to control precision or whole numbers.

• You do need SQLBase to automatically give you the largest precision available.

DECIMAL (or DEC)

This data type is associated with a particular scale and precision. Scale is the number of fractional digits and precision the total number of digits. If precision and scale are not specified, SQLBase uses a default precision and scale of 5,0.

Use the DECIMAL data type when you need to control precision and scale, such as in currency.

The position of the decimal point is determined by the precision and the scale of the number. The scale, which is the number of digits in the fractional part of the number, cannot be negative or greater than the precision. The maximum precision is 15 digits.

This data type can store a maximum of 15 digits. The valid range is:

-999999999999999 to +999999999999999

Another way to express the range is to say that the value can be -n to +n, where the absolute value of n is the largest number that can be represented with the applicable precision and scale.

The DEC notation is compatible with DB2.

Following are some DECIMAL examples:

DECIMAL (8,2)
DECIMAL (5,0) (same as INTEGER precision)
DECIMAL
DEC

SQLBase truncates input values to the precision of the column definition. For example:

• Entering 29.994 in a DECIMAL(10,2) stores 29.99.

• Entering 29.995 in a DECIMAL(10,2) also stores 29.99.

SQLBase truncates decimals as DB2 does with 2 exceptions:

• Floating point numbers that are used as bind variables.

• For positive numbers that contain more than 21 digits and negative numbers that contain than 19 digits, SQLBase rounds up the last digit.

Calculating precision for addition/subtraction

For two numbers A and B with precision and scale of (p1,s1) and (p2,s2) respectively, the following rules calculate the precision and scale for subtraction and division.

Precision:

Precision of result (A+B) or (A-B)

=

The minimum value of either the maximum precision of SQLBase (15) or the following equation:

max(p1-s1, p2-s2) + max(s1, s2) +1

Scale:

Scale of result (A+B) or (A-B)

=

The maximum value of the two scales s1 and s2.

Calculating precision for division

For division, the following rules calculate the precision and scale of the result.

Precision:

Precision of
result

=

Maximum precision
of SQLBase (15)

Scale:

Scale of result

=

Maximum precision

-

Precision of first input number

+

Scale of first input number

-

Scale of second input number

For example, if you have the following two columns:

D1 DECIMAL (10,2)

D2 DECIMAL (10,2)

and you divide D1 by D2, you get the following precision and scale:

precision = 15
scale = 15 - 10 + 2 - 2 = 5

Some functions change the maximum precision. For example, SUM changes the maximum precision to 15. Therefore, this equation:

SUM(D1)/SUM(D2)

results in the following precision and scale:

precision = 15
scale = 15 - 15 + 2 - 2 = 0

Calculating for multiplication

For two numbers A and B with precision and scale of (p1,s1) and (p2,s2) respectively, the following rules calculate the precision and scale.

Precision:

Precision of product (A*B)

=

The minimum value of either the maximum precision of SQLBase (15) or the sum of the precisions (p1 + p2)

Scale:

Scale of product (A*B)

=

The minimum value of either the maximum precision of SQLBase (15) or the sum of the scales (s1 + s2)

For example, if you have the following two columns:

D1 DECIMAL (10,2)
D2 DECIMAL (10,2)

and you multiply D1 by D2, then you get the following precision and scale:

precision = min(15, 20) = 15

scale = min (15, 4) = 4

Some functions change the maximum precision. For example, the SUM function uses the following rule:

precision = min(15, max(p1-s1, p2-s2) + max(s1, s2) + 1)

scale = max(s1,s2)

So, for the following sum:

SUM(D1)*SUM(D2)

you get the following precision and scale:

precision = min(15, max (8, 8) + max (2,2)+ 1)= min (15, 11)=11
scale = max(2,2) = 2

Currency

SQLBase does not have a specific CURRENCY data type, so you can use DECIMAL instead. A suggested setting is DECIMAL (15,2).

INTEGER (or INT)

This data type has no fractional digits. Digits to the right of the decimal point are truncated.

An INTEGER can have up to 10 digits of precision:

-2147483648 to +2147483647

The INT notation is compatible with DB2.

Examples:

INTEGER

INT

SMALLINT

This data type has no fractional digits. Digits to the right of the decimal point are truncated. Use this number type when you need whole numbers.

A SMALLINT can have up to 5 digits of precision:

-32768 to +32767

SQLBase does not store a SMALLINT value relative to the size of a 16- or 32-bit integer, but approximates it with the same number of digits. C programmers should check for overflow.

Example:

SMALLINT

DOUBLE PRECISION

This data type specifies a column containing double-precision floating point numbers.

Example:

DOUBLE PRECISION

FLOAT

This data type stores numbers of any precision and scale.

A FLOAT column can also specify a precision:

FLOAT (precision)

If the specified precision is between 1 to 21 inclusive, the format is single-precision floating point. If the precision is between 22 and 53 inclusive, the format is double-precision floating point.

Note: Although, SQLBase allows you specify a precision up to 53, the actual maximum supported precision is 22.

If the precision is omitted, double-precision is assumed.

Examples:

FLOAT

FLOAT (20)

FLOAT (53)

REAL

This data type specifies a column containing single-precision floating point numbers.

Example:

REAL

Date/Time data types

SQLBase supports these data types for date and time data:

• DATETIME (or TIMESTAMP)

• DATE

• TIME

You can use date columns in comparison operations with other dates. You can also use dates in some functions and expressions. The supported range of dates is 01-jan-0000 through 31-dec-9999.

Internally, SQLBase stores all date and time data in its own floating point format. The internal floating point value is available through an application program API call.

This format interprets a date or time as a number with the form:

DAY.TIME

DAY is a whole number that represents the number of days since December 30, 1899. December 30, 1899 is 0, December 31, 1899 is 1, and so forth.

TIME is the fractional part of the number. Zero represents 12:00 AM.

March 1, 1900 12:00:00 PM is represented by the floating point value 61.5 and March 1, 1900 12:00:00 AM is 61.0.

Anywhere a date/time string can be used in a SQL command, a corresponding floating point number can also be used.

SQLTalk and SQLBase provide extensive support for date/time values. Read the section Date/Time values for more information.

DATETIME (or TIMESTAMP)

This data type is used for columns which contain data that represents both the date and time portions of the internal number.

You can input DATETIME data using any of the allowable date and time formats listed for the DATE and TIME data types.

When a part of an input date/time string is omitted, SQLBase supplies the default of 0, which converts to December 30, 1899 (date part) 12:00:00 AM (time part).

TIMESTAMP can be used instead of DATETIME for DB2 compatibility.

Examples:

DATETIME

TIMESTAMP

The time portion of DATETIME has resolution to the second and microsecond. The time portion of TIMESTAMP has resolution several decimal places past microsecond; the exact precision depends on the operating system.

Note: Because of the difference in resolution between DATETIME and TIMESTAMP, it is possible to have very slightly different values in two columns that were initially assigned the same value. For this reason, you should use caution when running equality tests or date arithmetic between two columns when one is DATETIME and one is TIMESTAMP.

DATE

This data type stores a date value. The time portion of the internal number is 0. On output, only the date portion of the internal number is retrieved.

Example:

DATE

TIME

This data type stores a time value. The date portion of the internal number is 0. On output, only the time portion of the internal number is retrieved.

Example:

TIME

TIME has resolution to the second.

Data type conversions

This section describes how SQLBase converts data types.

Data type conversions in assignments

SQLBase is flexible in the data types it accepts for assignment operations:

Source Data Type

Target Data Type

Comment

Character

Numeric

Source value must form a valid numeric value (only digits and standard numeric editing characters).

Numeric

Character

Single quotes are not needed.

Date/Time

Numeric

 

Numeric

Date/Time

 

Date/Time

Character

Single quotes are not needed.

Character

Date/Time

Source value must form a valid date/time value.

Data type conversions in functions

Usually, functions accept any data type as an argument if the value conforms to the operation that function performs. SQLBase will automatically convert the value to the required data type.

For example, in functions that perform arithmetic operations, arguments can be character data types if the value forms a valid numeric value (only digits and standard numeric editing characters).

For date/time functions, an argument can be a character or numeric data type if the value forms a valid date/time value.

Implicit conversions for character data types

As described earlier, national characters (Unicode) are stored in some character data types and ANSI characters are stored in others. Some SQLBase functions only work with ANSI, some only with Unicode, but most functions work with both. Whenever an assignment occurs that assigns an ANSI value to a national character column, or vice versa, SQLBase handles the conversion automatically.

Constants

A constant (also called a literal) specifies a single value. Constants are classified as:

• String constants.

• Numeric constants.

• Date and time constants.

String constants

A string is a sequence of characters. A string constant must be enclosed in single quotes (apostrophes) when used in a SQL command.

To include a single quote in a string constant, use two adjacent single quotes.

Using prefix N

Special considerations apply to the prefix N when used in a string constant. For example, consider the special character represented by hex digits 4A30, as used in the fragments of a SQLTalk script below.

select distinct @hexdump('  ') from systables;

yields E3818A

select distinct @hexdump(N'  ') from systables;

yields 4A30

Why is that? It’s because SQLTalk uses the UTF8 character set. That special character requires three bytes to encode in UTF8. In national character format, which uses UTF16 encoding, only two bytes are required to store the character. By using the prefix N before the constant, we tell SQLBase to treat it as a national character string.

Numeric constants

A numeric constant refers to a single numeric value.

A numeric constant is specified with digits. The value can include a leading plus or minus sign and a decimal point.

A numeric constant can be entered in E notation.

Date/Time constants

Date and time values can be used as constants. Read the section Date/Time values for more information.

Examples of constants

Constant Type

Example

Explanation

Character String

'CHICAGO'

Character string must be enclosed in single quotes.

 

'DON''T'

To include a quote character as part of a character string, use two consecutive single quotes.

 

''

Two consecutive single quotes with no intervening character represents a null value.

 

'1492'

If digits are enclosed in quotes, it is assumed to be a character string and not a number.

 

N’Η’

String of national characters (Unicode)

Numeric

2580

Digits not enclosed in quotes are assumed to be numeric values.

 

1249.57

Numeric constant with decimal point.

 

-1249

Leading plus or minus signs may be used on numerics.

 

4.00E+7

E-notation can be used to express numeric values.

Date/time

10-27-94

Date/time constants do not need to be quoted.

 

27-Oct-1994

 

System keywords

Certain keywords have values that can be used in some commands in place of column names or constants. These special keywords are:

NULL

The absence of a value. NULL can be used as a constant in a select list or in a search condition. For example:

SELECT LNAME FROM EMP
WHERE DEPTNO IS NULL;

ROWID

The internal address of a row. ROWID can be used instead of a column name in a select list or in a search condition.

SELECT ROWID FROM EMP
WHERE HIREDATE > 01-JAN-1994;

USER

The authorization ID of the current user. USER can be specified instead of a constant in a select list or in a search condition.

CREATE VIEW MYTABLES AS
SELECT * FROM SYSADM.SYSTABLES
WHERE CREATOR = USER;

SYSDBTRANSID

The current transaction ID of the SQL command. SYSDBTRANSID can be specified instead of a constant or column name. Read the following section (Using SYSDBTRANSID keyword) for more details.

SQLBase also provides these keywords:

• date/time keywords, such as:

SYSDATETIME

SYSDATE

SYSTIME

SYSTIMEZONE

Read the section Date/Time values for more information.

• database sequence object keywords:

CURRVAL
NEXTVAL

Read the section Database sequence objects for more information

Using SYSDBTRANSID keyword

SYSDBTRANSID is an unsigned 4-byte numeric value representing the current transaction ID under which the SQL command was executed. Like other system keywords, you can specify SYSDBTRANSID in a SQL expression in place of a constant or column name. The current transaction ID, which is the value returned by SYSDBTRANSID, remains the same throughout the life of the transaction.

For example, assume you want to "capture" and store the transaction ID associated with the following UPDATE statement:

UPDATE EMPLOYEES SET SALARY = 100000 WHERE NAME = ‘JOHN’;

The following INSERT statement inserts the UPDATE’s transaction ID into a table called MYHISTORYTABLE:

INSERT INTO MYHISTORYTABLE (transid,time,changed_by,employee_name,new_salary)
SELECT SYSDBTRANSID, SYSTIME, USER, NAME, SALARY FROM EMPLOYEES
WHERE NAME = ‘JOHN’;

Although SYSDBTRANSID never decreases, you may not necessarily see sequential transaction IDs for sequential transactions. For instance, if you get a transaction ID of 20004 for one transaction, you may get an ID of 20010 for the next transaction, instead of 20005. This depends on the nature of the transaction; often times SQLBase has to do several internal transactions for each user transaction. The internal transactions also get their own transaction IDs. All IDs are unique.

Database sequence objects

SYSDBSequence is the name of the Database Sequence Object provided in SQLBase. A Database Sequence Object is an object inherently built into the SQLBase database that can be accessed by any database user for generating sequential numeric values. You can use sequences for automatically generating primary key values. When used as a primary key in a table, the generated sequence numbers also provide a useful way of ordering the rows in the entry sequence order.

Using SYSDBSequence

SYSDBSequence is a permanent persistent object in SQLBase. It is created when a SQLBase database is created and remains as part of the database until the database is dropped. It is persistent through reorganization of databases and can be migrated using the LOAD and UNLOAD database commands.

Initial value of the SYSDBSequence is 0 at the time of database creation and increases by 1 with no practical upper limit.

To access SYSDBSequence object values in SQL statements, use these pseudo columns:

• NEXTVAL: Obtains the next available sequence number

• CURRVAL: Obtains the sequence number last retrieved.

These pseudo columns let you obtain the current and incremented next value of the SYSDBSequence object as you would for regular table columns in some DML statements. Since the sequence number are generated independent of tables, they can be used across multiple tables or in general DML statement.

Although SYSDBSequence number never decrease, you may not necessarily see sequential numbers for sequential transactions. For instance, if you get a sequence number of 1000 for one transaction, you may get a number of 1005 for the next sequence, instead of 1001. This occurs since NEXTVAL is used by all transactions. Sequence numbers are always unique and ascending but not necessarily sequential.

You must qualify CURRVAL and NEXTVAL with the database sequence name SYSDBSequence. For example:

SYSDBSequence.CURRVAL

or

SYSDBSequence.NEXTVAL

You can use SYSDBSequence by accessing its value with CURRVAL and NEXTVAL pseudo columns in these places:

• SELECT list of a SELECT statement

• VALUES clause of an INSERT statement

• SET clause of an UPDATE statement

The following semantic rules apply for the usage of sequence numbers. Note that all of the following semantics rules apply for the single execution of a SQL statement.

• First reference to NEXTVAL returns the sequence’s initial value. Subsequent references to NEXTVAL increment the sequence value by 1 and returns the new value.

• Any reference to CURRVAL always returns the sequence’s current value. Before you use CURRVAL for the sequence in your transaction session, you must first increment the sequence with NEXTVAL otherwise an "un-initialized currval" error will be returned.

• Once a NEXTVAL is generated, it can be accessed in the same transaction session till the next NEXTVAL is requested from that transaction session.

• One transaction session can never see the sequence number generated by another transaction session. Once a sequence number is generated by one transaction, that transaction can continue to access that value by using the CURRVAL, regardless of whether the sequence is incremented by another transaction.

• You can only increment the SYSDBSequence once in a single SQL statement.

• If a statement contains more than one reference to NEXTVAL for SYSDBSequence, SQLBase increments the sequence value once and returns the same value for all occurrences of NEXTVAL in that statement.

• If a statement contains references to both CURRVAL and NEXTVAL, SQLBase increments the sequence once and returns the same value for both CURRVAL and NEXTVAL, regardless of their order within the statement.

• Two transactions can concurrently increment the sequence; the sequence number each transaction sees may have gaps because sequence numbers can be generated by the other transactions.

Examples

This example increments the SYSDBSequence and uses its value for a new employee inserted into the employee table:

INSERT INTO emp

VALUES (SYSDBSequence.nextval, ‘John’, SYSDATE);

The following example adds a new order with the next order number to the master order table and then adds suborders with this number to the detail order table.

INSERT INTO master_order(orderno, customer, orderdate)

VALUES (SYSDBSequence.nextval, ‘John’, SYSDATE);

 

INSERT INTO detail_order(orderno, part, quantity)

VALUES (SYSDBSequence.currval, ‘HUBCAP’, 1);

 

INSERT INTO detail_order(orderno, part, quantity)

VALUES (SYSDBSequence.currval, ‘SPARKPLUG’, 4);

 

INSERT INTO detail_order(orderno, part, quantity)

VALUES (SYSDBSequence.currval, ‘MUFFLER’, 1);

Expressions

An expression is:

• An item that yields a single value.

• A combination of items and operators that yield a single value. Operators are described in Operators.

An item can be any of the following:

• A column name.

• A constant.

• A bind variable.

• The result of a function.

• A system keyword.

• Another expression.

The form of an expression is:

If you do not use arithmetic operators, the result of an expression is the specified value of the term. For example, the result of 1+1 is 2; the result of the expression AMT (where AMT is a column name) is the value of the column.

Null values in expressions

If any item in an expression contains a null value, then the result of evaluating the expression is null (unknown or false).

String concatenation operator ( || )

This operator (||) concatenates two or more strings:

string || string

The result is a single string.

For example, if the column PLACE contains the value "PALO ALTO", then the following example returns the string "was born in PALO ALTO".

' was born in '|| PLACE

The following example prefixes everyone’s name with "Mr.":

SELECT 'Mr. '||LNAME FROM EMP;

Precedence

The following precedence rules are used in evaluating arithmetic expressions:

• Expressions in parentheses are evaluated first.

• The unary operators (+ and -) are applied before multiplication and division.

• Multiplication and division are applied before addition and subtraction.

• Operators at the same precedence level are applied from left to right.

Examples of expressions

The following table lists some sample expressions:

AMOUNT * TAX

Column arithmetic.

(CHECKS.AMOUNT * 10) - PAST_DUE

Nested arithmetic with columns.

HIREDATE + 90

Column and constant arithmetic.

SAL + MAX(BONUS)

Function with column arithmetic.

SAL + :1

Bind variable with column arithmetic.

SYSDATETIME + 4

Date/time system keyword arithmetic.

Search conditions

A search condition in a WHERE clause qualifies the scope of a query by specifying the particular conditions that must be met. The WHERE clause can be used in these SQL commands:

• SELECT

• DELETE

• UPDATE

A search condition contains one or more predicates connected by the logical (Boolean) operators OR, AND, and NOT.

The types of predicates that can be used in a search condition are discussed in section Predicates.

The specified logical operators are applied to each predicate and the results combined according to the following rules:

• Boolean expressions within parentheses are evaluated first.

• When the order of evaluation is not specified by parentheses, then NOT is applied before AND.

• AND is applied before OR.

• Operators at the same precedence level are applied from left to right.

A search condition specifies a condition that is true, false, or unknown about a given row or group. NOT (true) means false, NOT (false) means true and NOT (unknown) is unknown (false). AND and OR are shown in the following truth table.

Assume P and Q are predicates. The first two columns show the conditions of the individual predicates P and Q. The next two columns show the condition when P and Q are evaluated together with the AND operator and the OR operator. If an item in an expression in a search condition is null, then the search condition is evaluated as unknown (false).

P

Q

P and Q

P or Q

True

True

True

True

True

False

False

True

True

Unknown

Unknown

True

False

True

False

True

False

False

False

False

False

Unknown

False

Unknown

Unknown

True

Unknown

True

Unknown

False

False

Unknown

Unknown

Unknown

Unknown

Unknown

Using indexes with the OR predicate

SQLBase will use indexes with the OR predicate in the following situations:

• WHERE column IN (literal constants)

• WHERE column IN (literal constants) AND (boolean expression)

• WHERE column operator constant1 OR column operator constant2..

• WHERE (column operator constant1 OR column operator constant2..) AND (boolean expression)

Nulls and search conditions

If a search condition specifies a column that might contain a null value for one or more rows, be aware that such a row is not retrieved, because a null value is neither less than, equal to, nor greater than the value specified in the condition. The value of a null is unknown (false).

To select values from rows that contain null values, use the NULL predicate (explained later in this chapter):

WHERE column name IS NULL

SQLBase does not distinguish between a NULL and zero length string on input. Consider the following command that inserts a zero-length string:

INSERT INTO X VALUES (‘’);

The following command returns not only the null rows, but also the row with the zero-length string:

SELECT X FROM X WHERE X IS NULL;

Examples of search conditions

This returns rows for employees who are in department 2500.

SELECT * FROM EMP WHERE DEPTNO = 2500;

This returns rows for employees who are in department 2500 and were hired Feb. 1, 1994, or returns rows for employees who are programmers.

SELECT * FROM EMP WHERE (DEPTNO = 2500
AND HIREDATE = ‘01-FEB-1994’) OR JOB = 'Programmer';

The following WHERE clauses are equivalent.

SELECT * FROM EMP WHERE NOT
(JOB = 'Programmer' OR HIREDATE = '01-FEB-1994');

SELECT * FROM EMP WHERE
JOB != 'Programmer' AND HIREDATE ! = '01-FEB-1994';

Predicates

A predicate in a WHERE or HAVING clause specifies a search condition that is true, false, or unknown with respect to a given row or group of rows in a table.

Predicates use operators, expressions, and constants to specify the condition to be evaluated.

These types of predicates are described in this section:

• Relational

• BETWEEN

• NULL

• EXISTS

• LIKE

• IN

Relational predicate

There are two types of relational predicates:

• Comparison relational predicate

• Quantified relational predicate

Comparison Relational Predicate

A comparison relational predicate compares a value to another value based on standard relational operators. The basic form of a comparison predicate is two expressions connected by a relational operator:

A > B

col1 != col2

The following are examples of comparison predicates:

SELECT * FROM EMP WHERE EMPNO = ‘50642’;

SELECT * FROM EMP WHERE HIREDATE <= '1-Jan-1994';

Note: If you omit the keyword ALL or ANY (or SOME which can be used in place of ANY), the comparison relational predicate must return one row, or this error message is displayed: "Subselect resulted in multiple rows."

For example, if you have a table GRADES that contains a column RANK with the values 1, 2, and 3, the following statement is not allowed since no rows are returned:

X >= (SELECT RANK FROM GRADES WHERE RANK >= 4)

Quantified relational predicate

A quantified relational predicate compares the first expression value to a collection of values which result from a subselect command.

A SELECT command that is used in a predicate is called a subselect or subquery. A subselect is a SELECT command that appears in a WHERE clause of a SQL command.

You can use the NOT operator in place of the symbol (!). For example, NOT (a=b) is the same as a!=b.

You cannot use an ORDER BY clause in a subselect. Also, you cannot use a LONG VARCHAR column in a subselect.

ANY/SOME. You can use the ANY keyword as a test with one of the comparison operators. SQLBase also allows the SOME keyword as a alternate for ANY; they are interchangeable.

The ANY test compares a single test value to a column of data values produced by the subquery. SQLBase compares the test value to each value in the column individually. If any of the comparisons is TRUE, the entire ANY test is TRUE.

The following table lists the rules describing results of the ANY test when the test value is compared to the column of subquery results:

Comparison Test Value

ANY search value

TRUE for at least one of the data values in the column

TRUE

FALSE for every data value in the column

FALSE

Not TRUE for any data value in the column, but is NULL for one or more of the data values.

NULL

Subquery produces empty column of query results.

FALSE

Be careful when using the ANY keyword, since it involves an entire set of comparisons, not just one. Consider the following syntax:

WHERE X < ANY (SELECT Y)

It’s easy to read this line as "where X is less than any select Y". However, you should read the line as "where, for some Y, X is less than Y".

ALL. Like the ANY keyword, the ALL keyword is a quantified relational test used with the comparison operators. It compares a single test value to each data value in a column, one at a time. If all of the individual comparisons are TRUE, the entire ALL test is TRUE.

Examples of subqueries. Here are some examples of subselects and subqueries.

SALARY is not equal to the average salary:

SELECT * FROM EMPSAL WHERE SALARY != (SELECT
AVG(SALARY) FROM EMPSAL);

SELECT * FROM EMPSAL WHERE SALARY <> (SELECT
AVG(SALARY) FROM EMPSAL);

SALARY is greater than the average salary:

SELECT * FROM EMPSAL WHERE
SALARY > (SELECT AVG(SALARY) FROM EMPSAL);

SALARY is less than the average salary:

SELECT * FROM EMPSAL WHERE
SALARY < (SELECT AVG(SALARY) FROM EMPSAL);

SALARY is greater than or equal to any salary:

SELECT * FROM EMPSAL WHERE
SALARY >= ANY(SELECT SALARY FROM EMPSAL);

BETWEEN predicate

The BETWEEN predicate compares a value with a range of values. The BETWEEN predicate is inclusive.

The following line shows a BETWEEN example:

SELECT * FROM EMPSAL WHERE
SALARY BETWEEN 30000 AND 60000;

NULL predicate

The NULL predicate tests for null values.

The following line shows a NULL example:

SELECT * FROM EMP WHERE DEPTNO IS NULL;

EXISTS predicate

The EXISTS predicate tests for the existence of certain rows in a table.

This example retrieves all the rows from the EMP table if a salary matches the value stored in bind variable :1.

SELECT * FROM EMP WHERE EXISTS (SELECT * FROM EMPSAL
WHERE SALARY= :1)
\
70000
/

LIKE predicate

The LIKE predicate searches for strings that match a specified pattern. The LIKE predicate can only be used with CHAR or VARCHAR data types.

The underscore (_) and the percent (%) are the pattern-matching characters:

_

Matches any single character.

%

Matches zero or more characters.

The backslash (\) is the escape character for percent (%), underscore (_), and itself.

The following examples show examples of LIKE predicates.

True for any name with the string 'son' anywhere in it.

SELECT * FROM EMP WHERE LNAME LIKE '%son%';

True for any 2-character job code beginning with 'M'.

SELECT * FROM EMP WHERE JOB LIKE 'M_';

Returns all rows where the value in the JOB column is 'A24%'.

SELECT * FROM EMP WHERE JOB LIKE 'A24\%';

Returns all rows where the value in the JOB column begins with 'A24%'

SELECT * FROM EMP WHERE JOB LIKE 'A24\%%';

IN predicate

The IN predicate compares a value to a collection of values. The collection of values can be either listed in the command or the result of a subselect.

If there is only one item in the list of values, parentheses are not required.

The maximum number of comparisons allowed in the collection varies depending on the operating system upon which the server is running and on the type of query (ordinary select, recursive stored procedure, etc.). Tests have shown maximums ranging from slightly more than 900 comparisons to almost 3000 comparisons, but these tests were not comprehensive.

If you are building a query with a very large number of comparisons, consider using the EXISTS predicate instead.

The following examples show IN predicates.

SELECT * FROM EMP
WHERE DEPTNO IN (2500,2600,2700);

SELECT * FROM EMP
WHERE EMPNO NOT IN (SELECT EMPNO FROM EMPSAL WHERE
SALARY< 40000);

SELECT * FROM EMP
WHERE @LEFT (LNAME, 1) IN ('J', 'M', 'D');

SELECT * FROM EMP
WHERE LNAME NOT IN (:1,:2,’Jones’)
\
Johnson, Smith
/

Collations

A collation is a set of rules that describe how characters are compared. Comparisons happen in the database server in many ways, such as whenever you ask to build an index, do a GROUP BY, or use a relational operator such as <, >, or =.

Collations are implemented by using the keyword COLLATE in an individual query, in DDL (data definition language), or by using the keyword COLLATION in the configuration file.

In versions of SQLBase prior to 10.0, only one collation existed: BINARY. Characters were compared based on their binary values. In 10.0, with its Unicode compatibility, a large number of collations became available. BINARY is still one of the possible choices, and it offers the fastest performance. Other collations must call extra logic, consuming extra resources. The available collations can be found in system table SYSCOLLATIONS.

Collation is used in any SQL query that involves comparison of strings. Changing the collation will change your result sets. If you are using a collation where Η and C are not considered identical, and you switch to a collation where Η and C are considered identical, the results of some clauses (such as GROUP BY) will change.

In addition to comparisons caused by predicates, indexes, and relational operators, collation also affects the results of some SQLBase functions: MIN, MAX, COUNT, @DECODE, and @SCAN, since these functions involve comparisons.

There are four collation "labels": explicit, implicit, coercible, and none. This is also the order of precedence used by SQLBase when it determines what collation to use in a query.

Explicit collation happens when the COLLATE keyword is part of a specific query. For example:

SELECT CHINESE_COLUMN, STANDARD_COLUMN FROM MY_TABLE WHERE

STANDARD_COLUMN COLLATE LATIN_GENERAL_CS_AS > 'G'

 

Implicit collation is assigned at the time an object is created. Three examples:

CREATE DATABASE MY_DB COLLATE English_CI_AI

CREATE TABLE MY_TABLE ( column declarations ) COLLATE English_CI_AI

CREATE TABLE MY_TABLE ( CHINESE_COLUMN NCHAR(1000) COLLATE GB2312, STANDARD_COLUMN CHAR(20) ) COLLATE English_CI_AI

In the last example, one coluumn has a different collation than the table as a whole. In these examples, even though collation was assigned using explicit clauses at creation time, the collations are considered implicit at the time a query is run.

Coercible collation, applied to literals and to built-in database functions that return strings, is based on configuration settings. The order of preference for coercible collation is shown in the next section..

A collation of none occurs when two strings with conflicting implicit collations are combined in an operation. An expression with a collation of none, when combined with any other string expression with another kind of collation, will yield a result with a collation of none.

Server collation can be assigned using the COLLATION keyword in the server portion of the configuration file (sql.ini). Client collation can be assigned using the COLLATION keyword in the client portion of the configuration file (sql.ini). Collation can be changed at runtime by client applications that use the SQLBase API function SQLSET with parameters SQLPCCOL, SQLPDCOL, and SQLPSCOL.

The last resort, if no other collation specifications are found, is BINARY, the collation that was always used in versions of SQLBase prior to 10.0.

Collation order of preference

To summarize how collation is applied when a query is run, the SQLBase engine will use the first collation specification that it finds from the following list:

1. Explicit use of the COLLATE keyword within an individual query.

2. Implicit collation assigned by a DDL statement that used the COLLATE keyword during the definition of a database object.

3. The client (session) collation, if any.

4. The database collation, if any.

5. The server collation, if any.

6. BINARY.

Changing an existing collation

Database objects that are sensitive to collations include indexes, views, and stored procedures. In some cases, when a collation is changed, these objects are invalidated or dropped.

Functions

A function returns a value that is derived by applying the function to its arguments.

SQLBase has many functions that manipulate strings, dates and numbers. Functions are classified as:

• Aggregate functions

• String functions

• Date and time functions

• Logical functions

• Special functions

• Math functions

• Finance functions

The functions are described in Chapter 4, SQL Function Reference.

Date/Time values

This section describes SQL date and time values, including SQLBase year 2000 (Y2K) support.

Entering date/time values

Although SQLBase stores dates and times in its own internal format, it accepts all conventional date and time input formats, including ISO, European, and Japanese Industrial Time.

Input for a date or time column is a string that contains date or time information. The input string has a date portion and/or a time portion, depending on whether the date/time is a DATE, a TIME or a DATETIME.

A forward slash (/), hyphen (-) or period (.) are used as the delimiter for the parts of a date, as shown in the diagram on the next page. You must be consistent within a single command. A colon (:) or a period (.) are both accepted as the delimiter for times. Case is ignored by SQLBase when entering months. Either a space or a hyphen can separate the date portion from the time portion.

Letter combinations used in the formats below have the following meanings.

yy or yyyy
(read the next section ,Year and century values, for details)

Year

mm (entered with numbers, for example, 01)

mon (spelled out, for example, jan)

Month

dd

Day

hh

Hours

mi

Minutes

ss

Seconds

999999

Microseconds

Year and century values

SQLBase accepts date/time values in either of the following string formats:

• 4-digit string yyyy, which represents a 2-digit century value and a 2-digit year; for example, 1996.

• 2-digit string yy, which represents a 2-digit year; for example, 96.

By default, SQLBase always stores 2-digit century values as the current century. To change the default setting, you can specify 1 (one) as the value for the SQLBase keyword centurydefaultmode in the server section of SQL.INI. When set to 1, SQLBase applies the algorithm reflected in the following table to determine whether the year is in the current, previous, or, next century.

When last 2-digits of current year are:

When 2-digit entry is 0-49

When 2-digit entry is 50-99

0-49

The input date is in the current century

The input date is in the previous century

50-99

The input date is in the next century

The input date is in the current century

Examples:

• Assume the current year is 1996:

If 05 is entered, the computed date is 2005
If 89 is entered, the computed date is 1989

• Assume current year is 2014:

If 05 is entered, the computed date is 2005
If 34 is entered, the computed date is 2034
If 97 is entered, the computed date is 1997

• Assume current year is 2065:

If 05 is entered, the computed date is 2105
If 70 is entered, the computed date is 2070

Note: Enabling the 2-digit century is a SQLBase feature and has no impact on connectivity routers. If you are using a Gupta developed application or a SQL/API application against a non-SQLBase database, read the database documentation for information on how it determines year/century values.

Date/time input formats

Valid input formats for date/time values are:

A time string can contain an AM or PM designation. The default is AM. SQLBase recognizes military time (24 hour clock) on input if the AM/PM parameter is omitted.

Some examples of date/time input strings are:

12-JAN-94

12/jan/1994 12:15

01-12-94 12

01/12/94 12:15:20

Date/time system keywords

Certain system keywords return a date/time values. These system keywords can be used in expressions to specify an interval of a specified type.

The keyword values for SYSDATETIME, SYSDATE, SYSTIME, and SYSTIMEZONE are set at the beginning of execution of a command.

The following table lists system keywords and their meaning. An asterisk (*) means that the keyword is DB2 compatible.

System Keyword

Meaning

SYSDATETIME
CURRENT TIMESTAMP *
CURRENT DATETIME *

Current date and time.

SYSDATE
CURRENT DATE *

Current date.

SYSTIME
CURRENT TIME *

Current time.

SYSTIMEZONE
CURRENT TIMEZONE *

Timezone interval in days. For example, SYSTIMEZONE=.25 means 6 hours.

MICROSECOND[S]

Time in microseconds.

SECOND[S]

Time in seconds.

MINUTE[S]

Time in minutes.

HOUR[S]

Time in hours.

DAY[S]

Time in days.

MONTH[S]

Time in months.

YEAR[S]

Time in years.

Resolution for time keywords

The table below show the resolution in seconds for the time keywords.

Time Keyword

Resolution

CURRENT TIME
CURRENT DATETIME

Seconds
(hh:mm:ss)

SYSDATETIME SYSTIME
CURRENT TIMESTAMP

Microseconds
(hh:mm:ss:999999)

SECOND[S]

Seconds
(ss)

MICROSECONDS

Microseconds
(ss:999999)

The following command shows an example of a date/time system keyword:

INSERT INTO CALLS (COL_DATE) VALUES (SYSDATETIME)

Time zones

The keyword SYSTIMEZONE returns the time zone for the system as a time interval in days. For example, if SYSTIMEZONE returns 0.25, the time interval is 6 hours.

The time interval is the difference between local time and Greenwich Mean Time:

TIMEZONE interval = LOCAL TIME - GMT

This interval is set with the timezone keyword in sql.ini. The default value is 0 (Greenwich Mean Time).

For instance, GMT is 5 hours later than EST (Eastern Standard Time). If the time was 5:00 A.M. EST, then

TIMEZONE interval = 5 - 10 = -5

TIMEZONE= -5

To get the current time in GMT, use the following expression:

(SYSTIME - SYSTIMEZONE)

Date/Time expressions

Addition or subtraction operators can be applied to dates. The results are as follows:

• Date + Number (of days) is DATETIME.

• Date - Number (of days) is DATETIME.

• Date - Date is a number (of days).

Note that if you add or subtract a non-date/time value to or from DATE, the result is a DATETIME. To make the result a DATE, use an expression like this:

Date + Number DAYS

where Number is a numeric value.

The system keywords that represent time intervals (such as MONTH or MICROSECOND) can be added to or subtracted from other date and time quantities to get new date and time quantities.

For example, the following expression yields a new DATETIME value.

SYSDATETIME + 3 MINUTES

If you do not specify the type of interval, the number is assumed to be DAYS. The following example adds one day to the current date.

SYSDATE + 1

You could also use the expression:

SYSDATE + 1 DAY

Only a constant can precede a date/time keyword.

Microseconds, seconds, minutes, hours, and days behave like numbers. MONTH and YEAR intervals however, are special cases since they do not have a fixed value in terms of the number of days in the month or year. February has 28 or 29 days, March has 30; a year can be 365 or 366 days.

Use the following rules for MONTH and YEAR intervals:

• MONTH and YEAR intervals can only be added to or subtracted from a DATE or a DATETIME quantity.

Valid: (SYSDATE + 3 DAYS) + 1 YEAR

Invalid: SYSDATE + (3 DAYS + 1 YEAR)

• When MONTHs are added, the month number (and if necessary the year number) is incremented. If the day represents a day beyond the last valid day for the month and year, it is adjusted to be a valid date.

• SQLBase ignores fractional parts of MONTHs and YEARs. For example, SQLBase would ignore the fraction part .5 of MONTHS in the following command:

SELECT DISTINCT SYSDATETIME, SYSDATETIME + 1.5
MONTHS FROM SYSTABLES

Examples of date/time expressions

The following table lists some sample date/time expressions and their results:

Date/Time Expression

Result

31-Jan-1993 + 1 MONTH

28-Feb-1993

20-Jan-1993 + 1 MONTH

20-Feb-1993

3 1-Jan-1993 + 1 MONTH - 1 MONTH

28-Jan-1993

Joins

A join pulls data from different tables and compares it by matching on a common row that is in all the tables.

You cannot perform an operation with the CURRENT OF clause on a result set that you formed with a join.

The following example demonstrates a join.

Example:

The primary key for a table is a value that has a match in another table. For example, the following CUSTOMER table contains these columns: name and address. Also, each customer has a unique identifying number.

CUSTNO

NAME

ADDRESS

    1

ABC INC.

13 A St.

    2

XYZ INC.

1 B St.

    3

A1 INC.

12 C St.

There is another table called ORDERS that contains the order number, order date, and sales rep for each order. The table also includes a key that contains the customer number. This is the same number that is in the CUSTOMER table.

CUSTNO

ORDERNO

ORDERDATE

SALES REP

1

3001

01-JUL-94

Jill

1

3002

03-JUL-94

Jill

1

3003

06-JUL-94

Tom

2

3004

06-JUL-94

Tom

3

3005

07-JUL-94

Jill

You can join customer information with order information without unnecessary data repetition.

The following SQL command uses these tables to find the name and order numbers of the sales made by Tom.

SELECT NAME, ORDERNO FROM
CUSTOMER, ORDERS WHERE
CUSTOMER.CUSTNO = ORDERS.CUSTNO
AND SALES REP = ‘Tom’;

This produces the following result:

NAME

ORDERNO

ABC Inc.

3003

XYZ Inc.

3004

Types of joins

SQLBase supports the following types of joins:

• Equijoins

• Outer joins

• Self joins

• Non-equijoins

Equijoin

The following query matches customer names and order numbers. Two tables are used: CUSTOMER and ORDERS.

SELECT NAME, ORDERNO FROM CUSTOMER, ORDERS
WHERE CUSTOMER.CUSTNO = ORDERS.CUSTNO;

Each result row contains the customer name and an order number. If customer number 1 made three orders, three rows would result. The single customer row containing the customer's name and number would be "joined" to each of the three order rows.

The ORDERS rows are related to the CUSTOMER using the key column, CUSTNO, which appears in both the CUSTOMER table and the ORDERS table.

This type of search condition, which specifies a relationship between two tables based on an equality, is called an equijoin.

The same query, using the SQL99 ANSI join syntax supported in SQLBase version 8.5 and later, would look like this:

SELECT NAME, ORDERNO FROM CUSTOMER INNER JOIN ORDERS ON
CUSTOMER.CUSTNO=ORDERS.CUSTNO

The keyword INNER in the query above is optional. If it is omitted and no other keyword is used (LEFT, RIGHT, or OUTER), then INNER is presumed.

Cartesian product

Specifying a join condition as a relational predicate in the search condition is necessary to avoid a Cartesian product. A Cartesian product is the set of all possible rows resulting from a join of more than one table. For example, suppose we specified the previous query as follows:

SELECT NAME, ORDERNO FROM CUSTOMER, ORDERS;

The result would be the product of the number of rows in the customer table and the number of rows in the orders table. If CUSTOMER had 100 rows, and ORDERS had 500 rows, the Cartesian product would be every possible combination, or 50,000 rows, which is probably not the desired result.

The correct way to get each customer and order listed (a set of 500 rows) is with an equijoin, as follows:

SELECT NAME, ORDERNO FROM CUSTOMER, ORDERS
WHERE CUSTOMER.CUSTNO = ORDERS.CUSTNO;

Outer join (native syntax)

In the previous example of the equijoin, the search condition specified a join on customers and orders. What happens if customer NEWACCOUNT has not yet made an order? The above query does not retrieve that customer.

An outer join produces a result that joins each row of one table with either a matching row or a null row of another table. The result includes all the rows of one table regardless of whether they have a match with any of the rows of the table to which they are being joined.

Outer join semantics. In the WHERE clause, add a plus sign (+) to the join column of the table that might not have rows to satisfy the join condition.

SQLBase supports an outer join on only one table per SELECT statement, and it must be a one-way outer join. You cannot add the plus sign (+) to both sides of the join condition. You can, however, specify an outer join on more than one column of the same table, like this example:

SELECT t1.col1, t2.col1, t1.col2, t2.col2
FROM t1, t2
WHERE t1.col1 = t2.col1 (+)
AND t1.col2 = t2.col2 (+);

The next example lists customer names and their order numbers, including customers who have made no orders.

SELECT CUSTOMER.CUSTNO, NAME FROM CUSTOMER, ORDERS
WHERE CUSTOMER.CUSTNO = ORDERS.CUSTNO(+);

When SQLBase sees the plus sign (+) after ORDERS.CUSTNO, it temporarily adds an extra row containing all null values to the ORDERS table. SQLBase then joins this null row to rows in the CUSTOMER table which do not have matching orders. Therefore, all customer numbers are retrieved.

SQLBase adheres to both the ANSI and industry standard implementation of an outer join. According to the ANSI standard, the correct semantics of an outer join must display all the rows of one table that meet the specified constraints on that table, regardless of the constraints on the other table.

Outer join (SQL99 ANSI syntax)

In SQLBase version 8.5, support was added for SQL99 syntax in join specifications. The native syntax discussed in the section just above is also supported in version 8.5. However, native syntax allows only one outer join per query. SQL99 syntax permits multiple outer joins per query. The remainder of this section contrasts SQL99 syntax with native syntax, using the same queries as the section above.

Note: CROSS JOIN and FULL OUTER JOIN are not supported in SQLBase version 8.5

An outer join produces a result that joins each row of one table with either a matching row or a null row of another table. The result includes all the rows of one table regardless of whether they have a match with any of the rows of the table to which they are being joined.

Outer join semantics. Between the names of the two tables (or views, or aliases) to be joined, use the keywords LEFT OUTER JOIN or RIGHT OUTER JOIN.

Here is the example from the previous section, rewritten in SQL99 syntax:

SELECT t1.col1, t2.col1, t1.col2, t2.col2
FROM t2 RIGHT OUTER JOIN t1
ON t1.col1 = t2.col1
AND t1.col2 = t2.col2 ;

The keyword RIGHT specifies that the table name on the right of the join ("t1") is the table which will have all its rows, matching or not, represented in the result set of the query.

The next example lists customer names and their order numbers, including customers who have made no orders.

SELECT CUSTOMER.CUSTNO, NAME FROM CUSTOMER LEFT OUTER JOIN
ORDERS ON CUSTOMER.CUSTNO = ORDERS.CUSTNO;

In this case the LEFT keyword causes SQLBase to temporarily add an extra row containing all null values to the ORDERS table (the one on the right of the join). SQLBase then joins this null row to rows in the CUSTOMER table (the one on the LEFT of the join) which do not have matching orders. Therefore, all customer numbers are retrieved.

The examples above used the ON keyword to indicate how matching should be performed between joined tables; in SQL99 syntax, search conditons for joins are no longer specified in the WHERE clause. Note that there are other ways to match joined tables in SQL99 syntax, designated by the keywords NATURAL and USING. See SELECT for more information about these keywords.

Oracle Outer Join

If you need to use the Oracle-style outer join result, you can specify the oracleouterjoin keyword in the relevant server section of your sql.ini file. For example, if you are using the SQLBase Server for Windows NT, specify oracleouterjoin in the [dbntsrv] section:

[dbntsrv]
oracleouterjoin=1

The following example shows how the two standards differ in output. These examples use the following tables and SELECT statement:

Table A (a int) Table B (b int)
--------------- ---------------

1 1

2 2

3 3

4

5

SELECT a, b

FROM A, B

WHERE A.a = B.b (+)

AND B.b IS NULL;

The ANSI standard gives the following result:

a b
--- ---

1

2

3

4

5

Using the ORACLE style outer join yields a different result:

a b
--- ---

4

5

Note that the oracleouterjoin keyword setting is ignored when using SQL99 ANSI join syntax. In such cases you will always get ANSI standard results.

Self join

A self join lets you join a table to itself, as though it was two separate tables. To do this, the self-join table is given a correlation name. The example below finds all dates on which more than one order was placed:

SELECT A.ORDERNO, A.ORDERDATE
FROM ORDERS A, ORDERS B
WHERE A.ORDERDATE = B.ORDERDATE
AND A.ORDERDATE <> B.ORDERNO;

The ORDERS table is treated as two tables, using the correlation names A and B. An order date is retrieved from correlation table A. Then this order date is used as a search condition for table B.

This same information can be retrieved using a subquery. Read the section Subqueries for more information.

Self-joins can be implemented in both native syntax and SQL99 syntax.

Non-equijoin

A non-equijoin joins tables to one another based on comparisons other than equality. Any of the relational operators can be used, (such as >, <, !=, BETWEEN, or LIKE).

The following example specifies a join using the BETWEEN operator.

SELECT NAME, ORDERNO, ORDERDATE
FROM CUSTOMER, ORDERS
WHERE CUSTOMER.CUSTNO = ORDERS.CUSTNO
AND ORDERDATE BETWEEN 01-JUL-94 AND 30-SEP-94;

Number of joins

SQLBase allows you to join 17 tables in a SELECT statement. However, think carefully before you join this many tables. With each table added in a JOIN, the time needed to process the statement increases. Having 17 tables in a join can slow the database performance considerably!

A carefully designed database model should rarely need to join 10 tables in a statement. Instead of using this many tables, reconsider your database design.

Subqueries

SQL is recursive. The input to one query can be the output of another query. A query can be nested within another SQL command to define the scope of a command. This nested query is called a subquery.

A subquery is a search condition that is a nested SELECT command (also called a subselect). The subquery specifies a result table from one or more tables or views, in the same manner as any other SELECT. Each result row of the subselect is used as a basis for qualifying a candidate result row in the outer select.

You cannot use an ORDER BY clause in a subquery. You cannot use the UNION keyword in a subquery. And you cannot use a LONG VARCHAR in a subquery.

Examples of subqueries

For example, find all the orders that were placed on the same day as the order from customer number 2.

SELECT ORDERNO, ORDERDATE FROM ORDERS
WHERE ORDERDATE = (SELECT ORDERDATE FROM ORDERS
WHERE CUSTNO = 2);

First, the order date of customer number 2 is retrieved and this value is used to complete the search condition of the main or outer query. In this example, the subquery was executed once to retrieve a single value used by the main query. In the following SELECT command, called a correlated subquery, the subquery is executed once for each candidate row in the main query.

For example, find all employees whose salary is larger than the average salary of other employees.

SELECT * FROM EMPSAL WHERE
SALARY (SELECT AVG (SALARY) FROM EMPSAL);

Bind variables

A bind variable refers to a data value associated with a SQL command. Bind variables associate (bind) a syntactic location in a SQL command with a data value that is to be used in that command.

Bind variables can be used wherever a data value is allowed:

• WHERE clause.

• VALUES clause in an INSERT command.

• SET clause of in UPDATE command.

Bind variable names start with a colon (:). The name can be:

• The name of a variable that is declared in a program (such as :ARG1).

• A number that refers to the relative position among the data items associated with the SQL command (such as :1, :2, :3).

Read the manual for the client application that you are using (such as the Gupta’s SQL/API, SQLTalk, or Team Developer) for the details on how to use bind variables.

 

 

SQLBase Language Reference