Relational Interface Support

New syntax allows you to move table columns logically or physically to keep columns at desired ordinal positions. You may also change the ordinal position of a new column after adding it.

For both enhancements, see ALTER TABLE in SQL Engine Reference.

Bitwise Operators

Bitwise operators allow you to manipulate the bits of one or more operands. The following bitwise operators are supported:


Operator	Meaning
&	bitwise AND
~	bitwise NOT
\|	bitwise OR
^	bitwise exclusive OR

See Bitwise Operators in SQL Engine Reference.

CASE Expression

A CASE expression returns a value. CASE expression has two formats:

Simple When/Then. This format compares a value expression to a set of value expressions to determine a result. The value expressions are evaluated in their order listed. If a value expression evaluates to TRUE, CASE returns the value expression for the THEN clause.

Searched When/Then. This format evaluates a set of Boolean expressions to determine a result. The Boolean expressions are evaluated in their order listed. If a Boolean expression evaluates to TRUE, CASE returns the expression for the THEN clause.

See CASE (expression) in SQL Engine Reference.

DEFAULT Keyword

You may use the DEFAULT keyword in the following contexts:

Column definition of CREATE TABLE statement

Column definition of ALTER TABLE statement

VALUES clause of INSERT statement

VALUES clause of UPDATE statement

The default value (literal or expression) that you specify in a CREATE TABLE or ALTER TABLE statement must meet the following criteria:

be consistent with the data type of the column

conform to any other constraint imposed on the column, such as range, length, and so forth.

In INSERT and UPDATE statements, you do not have to specify values for columns that have a DEFAULT value defined. In such a case, Pervasive.SQL computes the DEFAULT expression and uses the result value as the value of the column.

See DEFAULT in SQL Engine Reference.

EXECUTE Statement

The EXECUTE statement has two uses:

To invoke a user-defined procedure or a system stored procedure. You may use EXECUTE in place of the CALL statement

To execute a character string, or an expression that returns a character string, within a stored procedure.

See EXECUTE in SQL Engine Reference.

FROM Clause in UPDATE and DELETE Statements

The UPDATE and DELETE statements, when used at the session level, now support a FROM clause containing a table reference. The FROM clause does not apply to the following:

UPDATE or DELETE statements used in a stored procedure

the positioned UPDATE statement

the positioned DELETE statement.

See DELETE and UPDATE , both in SQL Engine Reference.

Global Variables

The following global variable has been added:

@@SPID Variable

@@SPID

This variable (server process identifier) returns the identifier integer value of the system thread for the Pervasive.SQL connection.

See @@SPID in SQL Engine Reference.

Grouped View Support

A grouped view is one that contains any of the following in the SELECT list:

DISTINCT

GROUP BY

Scalar Functions

Scalar subquery

TOP

UNION

Grouped views may be used in a subquery provided the subquery is an expression. A subquery connected with the operators IN, EXISTS, ALL, or ANY is not considered an expression.

See CREATE VIEW in SQL Engine Reference.

Linked Duplicate Keys for CREATE TABLE

The CREATE TABLE statement provides a new keyword, LINKDUP, that allows you to specify the number of pointers to reserve for the addition of linked duplicates index keys. (Multiple records may carry the same duplicated value for index keys. The two methods to keep track of the records with duplicate key values are called linked duplicates and repeating duplicates.)

See the following topics:

LINKDUP in SQL Engine Reference

Methods for Handling Duplicate Keys in Advanced Operations Guide

ODBC 3.5 Support

The Pervasive relational interface fully conforms to the ODBC v3.51 specifications for Core, Level 1, and Level 2 interface conformance levels.

See ODBC Conformance in SQL Engine Reference.

Promotion of Numeric Data Types

The following topics pertain to the promotion of numeric data types:

Operator Precedence

Data Type Precedence

Precision and Scale of Decimal Data Types

Operator Precedence

An expression may have multiple operators. Operator precedence determines the sequence in which the operations are performed. An operator on a higher level is evaluated before an operator on a lower level.

See Operator Precedence in SQL Engine Reference.

Data Type Precedence

Data type precedence determines which data type results when two expressions of different data types are combined by an operator. The data type with the lower precedence is converted to the data type with the higher precedence.

See Data Type Precedence in SQL Engine Reference.

Precision and Scale of Decimal Data Types

Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. The number 909.777 has a precision of 6 and a scale of 3, for instance.

See Precision and Scale of Decimal Data Types in SQL Engine Reference.

Scalar Functions

The following scalar functions have been added:

CAST

COALESCE

ISNULL

CAST

The CAST function can convert data to any Pervasive.SQL relational data type. The CAST function does not support converting binary zeros in a string. For example, the following is invalid: CAST(c1 AS BINARY(10)), where c1 is a character column that contains binary zeros.

You cannot CAST user-defined data types. If both the input and the output are character strings, the output from CAST has the same collation as the input string.

See Conversion Functions in SQL Engine Reference.

COALESCE

This function takes two or more arguments and returns the first nonnull argument, starting from the left in the expression list.

See Logical Functions in SQL Engine Reference

ISNULL

This function takes two arguments (exp and value) and replaces NULL with the value specified for the value argument. Exp is the expression to check for NULL. Value is the value returned if exp is NULL. Exp is returned if it is not NULL. The data type of value must be compatible with the data type of exp.

See Logical Functions in SQL Engine Reference

SELECT Statement

The SELECT statement now supports the following:

CASE as expression

COALESCE as expression

NULL as expression

Subquery as expression

UNION in subquery

Column names in ORDER BY with UNION

See SELECT in SQL Engine Reference.

SET ANSI_PADDING

The SET ANSI_PADDING statement allows the relational interface to handle CHAR data types padded with NULLs (binary zeros). CHAR is defined as a character data type of fixed length.

See SET ANSI_PADDING in SQL Engine Reference.

Subquery Expressions

A subquery is a SELECT statement with one or more SELECT statements within it. The maximum number of nested subqueries allowed within the topmost SELECT statement is 16.

The following types of subqueries are supported:

comparison

quantified

exists

correlated

expression

See Subqueries in SQL Engine Reference.

System Stored Procedures

System stored procedures help you accomplish those administrative and informative tasks that are not covered by the Data Definition Language. The system stored procedures have a "psp_" prefix.

An error results if you execute a system stored procedure in the context of a database (for example, A) and try to obtain information from a secured database (for example, B). Information from a secured database cannot be obtained by another database.

See System Stored Procedures in SQL Engine Reference.

User Defined Functions

The CREATE FUNCTION statement creates a scalar user-defined function (UDF) in the database. You can invoke user-defined functions from a query.

See CREATE FUNCTION in SQL Engine Reference.