SQL Engine Reference - Index
A -
B -
C -
D -
E -
F -
G -
H -
I -
J -
L -
M -
N -
O -
P -
Q -
R -
S -
T -
U -
V -
W -
X -
Y -
Z -
Symbols
A
ABS
ACOS
ADD
grammar supported
Aggregate functions
AVG
COUNT
MAX
MIN
SUM
DECIMAL precision and
ALL
grammar supported
ALTER TABLE
grammar supported
IN DICTIONARY keyword
use with COBOL
USING keyword
WITH REPLACE keyword
alter-option (grammar element defined)
alter-option SQL grammar element definition
alter-option-list (grammar element defined)
alter-option-list SQL grammar element definition
AND bitwise operator
ANDed predicate
maximum in SQL statement
ANSI
character translation
ANY
grammar supported
API functions
ODBC supported
Approximate-numeric-literal
grammar supported
Arguments
number in a parameter list for stored procedures 1 2
Arithmetic
floating point comparison
AS
grammar supported
as-or-semicolon SQL grammar element definition
ASCII
ASIN
ATAN
ATAN2
ATTRIB.DDF
attributes ignored by Pervasive.SQL
AUTOINCREMENT data type
AVG aggregate function
B
before-or-after (grammar element defined)
before-or-after SQL grammar element definition
BEGIN
grammar supported
Between-predicate
grammar supported
BFLOAT data type
BINARY data type
0-padded if odd digits
default value not supported
notes about
SELECT and
Binary Large Objects
BIT_LENGTH
Bitwise operators
See Operators
BLOB data type
default value not supported
Bound databases
IN DICTIONARY not permitted 1 2
Btrieve owner names See Owner names
C
CALL
calling a stored procedure
grammar supported
call-arguments SQL grammar element definition
CASCADE
grammar supported
ON DELETE
CASE
grammar supported
CASE (grammar element defined)
case_value_expression (grammar element defined)
CAST
CB001
CB002
CB003
CB004
CB005
CB006
CB007
CB008
CB009
CB010
CB012
CB013
CB014
CB016
CB017
CB018
CB019
CB022
CB023
CB024
CB025
CB028
CB029
CB050
CB051
CB052
CB057
CB099
CB100
CB101
CB102
CB103
CB105
CB106
CB108
CB109
CB110
CEILING
Changing
System Tables
CHAR
CHAR data type
notes about
CHAR_LENGTH
Character
escape for wildcard characters
translation
OEM to ANSI
CHARACTER_LENGTH
CLOB data type
default value not supported
CLOSE
grammar supported
Closing
open tables
COALESCE
grammar supported
COALESCE ( (grammar element defined)
COBOL
DBCobolNumeric setting
Schema Executor
example usage
executing sample XML file
log messages
sign value for positive NUMERIC data types
SQL access for applications
SQL enhanced access
components
Schema Executor
using
use of ALTER TABLE statement
use of CREATE INDEX
use of CREATE INDEX IN DICTIONARY
use of CREATE TRIGGER
use of DELETE
use of DROP TABLE
use of INSERT INTO
use of UPDATE
Codes
data type
col-constraint (grammar element defined)
col-constraint SQL grammar element definition
collation-name (grammar element defined)
collation-name SQL grammar element definition
Column
attributes
system table (X$Attrib)
definitions
changing
creating
maximum
name length
number
number allowed in a trigger
number in a select list
number in a table
size of
system table (X$Field)
column-constraint (grammar element defined)
column-constraint SQL grammar element definition
column-definition (grammar element defined)
column-definition SQL grammar element definition
column-name (grammar element defined)
column-name SQL grammar element definition
Columns
number allowed in a trigger
Comma
as decimal separator
data display 1 2
data entry
COMMIT
grammar supported
commit-statement SQL grammar element definition
comparison-operator (grammar element defined)
comparison-operator SQL grammar element definition
Compressing data files
with SQL
CONCAT
Concurrency
Connection
maximum number of connections
Connection strings
DSN
TRANSLATIONDLL
Constant values, expressing
date
string
time
timestamp
constraint-name (grammar element defined)
constraint-name SQL grammar element definition
CONVERT 1 2
Converting
data to other types
Correlated subquery 1 2
Correlation-name
grammar supported
correlation-name SQL grammar element definition
COS
COT
COUNT aggregate function
COUNT( ), COUNT(*) functions
differences 1 2
CREATE FUNCTION
grammar supported
CREATE GROUP
grammar supported
CREATE INDEX
grammar supported
limitations
use with COBOL
CREATE INDEX IN DICTIONARY
use with COBOL
CREATE PROCEDURE
grammar supported
CREATE TABLE
file compression
grammar supported
IN DICTIONARY keyword
page size
USING keyword
WITH REPLACE keyword
CREATE TRIGGER
grammar supported
use with COBOL
CREATE VIEW
grammar supported
Creating
groups
stored procedures
tables with legacy null support
user-defined functions
users and groups
views
CURDATE
CURRENCY data type
Current Date
adding to an INSERT statement 1 2
Current Time
adding to an INSERT statement 1 2
CURRENT_DATE
CURRENT_TIME
CURRENT_TIME()
CURRENT_TIMESTAMP
Cursor
how to loop through
limits
type changed by TOP
cursor-name SQL grammar element definition
CURTIME
CURTIME()
D
Data
long, inserting or updating
Data control statements
Data definition statements
Data dictionaries
list of system tables
Data files
binary compatible cross-platform 1 2
replacing existing 1 2
system table (X$File)
Data manipulation
statements
Data Source Names
connection string keywords
clients
engine
Data types
AUTOINCREMENT
BFLOAT
BLOB
CLOB
codes
conversion
converting to other types
CURRENCY
DATE
DECIMAL
extended
FLOAT
INTEGER
lengths and ranges
limitations in stored procedures
limitations in triggers
limitations in user-defined functions
LOGICAL
LSTRING
MONEY
NUMERIC
NUMERICSA
NUMERICSTS
REAL
STRING
supported
that cannot be indexed
TIME
TIMESTAMP
UNSIGNED BINARY
WSTRING
WZSTRING
ZSTRING
data-type (grammar element defined)
data-type SQL grammar element definition
data-type-name SQL grammar element definition
DATABASE
Databases
joining multiple
Date
adding in INSERT statements
format supported 1 2
functions supported
inserting current 1 2
inserting or updating column values 1 2
literal format 1 2
literal values
TIMESTAMP data type
why not to use
Date arithmetic
grammar supported 1 2
DATE data type
Date-literal
grammar supported
Daylight savings, see Time
DAYNAME
DAYOFMONTH
DAYOFYEAR
db-name SQL grammar element definition
DBCobolNumeric setting
DCOMPRESS
DDFs
changing data definitions without affecting data files
DECIMAL data type
precision of SUM() function result
Decimal separator
comma
display only 1 2
comma as 1 2 3
DECLARE
grammar supported
DECLARE CURSOR
grammar supported
DEFAULT
example of use in CREATE TABLE 1 2
in UPDATE 1 2
Default values, see Values, default
default-value-expression (grammar element defined)
Definitions
index
Btrieve indexes and 1 2 3 4
DEGREES
Delete
rule
cascade
restrict
DELETE
grammar supported 1 2
use with COBOL
Deleting
view
Delimited identifier
in SQL Statements
Delimiter
SQL statement in PCC
DEPEND.DDF
Disabling security
Disjointed index, definition of
DISTINCT
grammar supported
in subquery
Double quote
matching data that contains a
DROP FUNCTION
grammar supported
DROP INDEX
grammar supported
DROP PROCEDURE
grammar supported
DROP TABLE
grammar supported
IN DICTIONARY keyword
use with COBOL
DROP TRIGGER
grammar supported
DROP VIEW
grammar supported
DSN
connection strings
Dynamic cursors
SET ROWCOUNT and
type changed by TOP
E
Enabling
security
END
grammar supported
Escape character
Exact-numeric-literal
grammar supported
Exclusive OR bitwise operator
EXISTS
grammar supported
EXP
Expression
in stored procedures
subquery on left side of
expression (grammar element defined)
expression-or-subquery (grammar element defined)
expression-or-subquery SQL grammar element definition
Expressions as Default Values for Date-Time Columns
Supported Data Types and Constraints
Usage Context
Extended data types
EXTRACT
F
FETCH
grammar supported
fetch-orientation (grammar element defined)
fetch-orientation SQL grammar element definition
Field.ddf
FIELD.DDF
File
compression, specifying with SQL
File.ddf
FILE.DDF
Files
location of temporary
FLOAT data type
comparing values
FLOOR
FOREIGN KEY
cannot ALTER column
grammar supported
Format
literal values
date
string
time
timestamp
Formats, literal
date 1 2
string
time 1 2
timestamp 1 2
Functions
aggregate
AVG
COUNT
MAX
MIN
SUM 1 2
conversion
date
inserting value
logical
numeric
string
system
time
inserting current
inserting value
G
Global variables
@@IDENTITY
@@ROWCOUNT
@@SPID
Grammar element definitions
alter-option
alter-option-list
before-or-after
CASE
case_value_expression
COALESCE (
col-constraint
collation-name
column-constraint
column-definition
column-name
comparison-operator
constraint-name
data-type
default-value-expression
expression 1 2
expression-or-subquery
fetch-orientation
ins-upd-del
join-definition
join-type
literal
order-by-expression
outer-join-definition
outer-join-type
parameter
parameter-type
parameter-type-name
predicate
proc-expr
proc-search-condition
proc-stmt
procedure-name
query-specification
referencing-alias
referential-actions
referential-delete-action
referential-update-action
scalar-function
search-condition
select-item
select-list 1 2
set-function
sqlstate-value
subquery-expression
table-constraint 1 2
table-constraint-definition
table-element
table-name
table-reference 1 2
top-clause
transaction-statement
trigger-name
value
values-clause
view-name
Grammar Element Definitions
GRANT
grammar supported
Granting
rights
Greater than (>) operator
Group
creating a
GROUP BY
grammar supported
with a HAVING clause
group-name SQL grammar element definition
H
HAVING
in a GROUP BY expression
HOUR
How
to loop through a cursor
I
IDENTITY global variable. See @@IDENTITY
IF
grammar supported
nesting limit
IF (with SELECT)
grammar supported
IFNULL
IN
grammar supported
IN DICTIONARY keyword 1 2 3 4
not permitted on bound databases 1 2
In-predicate
grammar supported
Index
creating
data types that cannot be indexed
definitions, SQL
Btrieve indexes and 1 2 3 4
disjointed, definition of
dropping named
maximum indexed nullable columns
name
maximum length of
optimizations
index-definition SQL grammar element definition
index-name SQL grammar element definition
index-segment-definition SQL grammar element definition
Index.ddf
INDEX.DDF
Indexes
existing Btrieve and relational definitions 1 2 3 4
existing used when possible 1 2 3 4
system tables
X$Field
X$Index
Infinity
representation
ins-upd-del (grammar element defined)
ins-upd-del SQL grammar element definition
INSERT
default values and
grammar supported
INSERT INTO
use with COBOL
insert-values SQL grammar element definition
Inserting
current time, current date and timestamp 1 2 3
INTEGER data type
Invalid row-count in subquery
returned if SELECT within UPDATE returns multiple rows
ISNULL
J
Join
Cartesian
cross-database
LEFT OUTER
maximum tables in a
multi-database
RIGHT OUTER
TOP clause in a
two-way LEFT OUTER JOIN
JOIN
grammar supported
join type (grammar element defined)
join-definition (grammar element defined)
join-definition SQL grammar element definition
L
label-name SQL grammar element definition
LCASE
LEAVE
grammar supported
LEFT
LEFT OUTER JOIN
and vendor strings
syntax
Length
maximum for column name
of path name in USING 1 2
LENGTH
Less than (<) operator
LIKE
syntax and usage
LIKE predicates
on LONGVARBINARY
on LONGVARCHAR
Limitations
columns, number of
of LONGVARBINARY
of LONGVARBINARY in INSERT statements
of LONGVARCHAR
of LONGVARCHAR in INSERT statements
of LONGVARCHAR in UNION statements
Limits
cursors
maximum ANDed predicates in SQL statement
maximum size of quoted string in SQL statement
of Pervasive ODBC Engine interface
SQL variables and parameters
stored procedures
arguments in a parameter list 1 2
procedure name
size 1 2
variable name
triggers
number of columns 1 2
when using long data
literal (grammar element defined)
literal SQL grammar element definition
Literal values
date
string
entering data longer than max
time
timestamp
Locale
setting time zone
Locale-specific behavior
comma as decimal separator
data display 1 2
data entry
LOCATE
LOG
LOG10
LOGICAL data type
Logical functions supported
Long data
inserting or updating
limits when using
LONGVARBINARY
limitation
limitations in INSERT statements
notes about
using SQLGetData
LONGVARCHAR
limitations
limitations in INSERT statements
limitations in UNION statements
notes about
using SQLGetData
LOOP
grammar supported
LSTRING data type
LTRIM
M
Master user
MAX aggregate function
Maximum
column name length
length of view name
Metadata
Schema Executor
MIN aggregate function
MINUTE
MOD
Modifying
data
statements for
MONEY data type
MONTH
MONTHNAME
N
Named databases
and file names 1 2
Names
maximum length
Nesting limits
IF
Non-correlated subquery
NOT
grammar supported
NOT bitwise operator
NOT NULL
default values and
NOW
Null
support
creating tables with legacy
setting for table creation
NULL
attempted insert into NOT NULL column with default value
cannot make column nullable
inserted by UPDATE if subquery returns no rows
Nullable columns
default value for
maximum number of indexed
NULLIF
NUMERIC data type
sign value for positive data type
Numeric functions supported
NUMERICSA data type
NUMERICSTS data type
O
Occurs DDF
Occurs.DDF
OCTET_LENGTH
ODBC
API
conformance
exceptions to conformance
Conformance
Attribute
Descriptor Field
Interface
security
ODBC API
functions
supported
OEM to ANSI
character translation
connection string
ON DELETE
ON UPDATE
ONLY
grammar supported
Operators
bitwise
bitwise AND (&)
bitwise Exclusive OR (^)
bitwise NOT (~)
bitwise OR (|)
truth table for bitwise operations
greater than
less than
Optimizations
index usage
restriction
SQL Relational Database Engine
subquery on left side of expression
OR bitwise operator
ORDER BY
SET ROWCOUNT and
TOP and
order-by-expression (grammar element defined)
order-by-expression SQL grammar element definition
outer-join-definition (grammar element defined)
outer-join-definition SQL grammar element definition
outer-join-type (grammar element defined)
outer-join-type SQL grammar element definition
Owner names
in secured databases
specifying in SQL
P
Padding
in BINARY columns
in CHAR columns
in LONGVARBINARY columns
in LONGVARCHAR columns
in VARCHAR columns
Page
sizes
specifying with SQL
PAGESIZE
parameter (grammar element defined)
Parameter List
number of arguments for stored procedures 1 2
parameter SQL grammar element definition
parameter-name SQL grammar element definition
parameter-type (grammar element defined)
parameter-type SQL grammar element definition
parameter-type-name (grammar element defined)
parameter-type-name SQL grammar element definition
Password
for Master user
password SQL grammar element definition
Path name
length in USING 1 2
Pattern matching
with LIKE
Performance
SQL Relational Database Engine
temporary tables
Permissions. See Rights
Pervasive Control Center
OEM characters and
Pervasive ODBC Engine interface
data types supported
limits of
SQL conformance
Pervasive.SQL 7
status code 59
PI
POSITION
positional-argument SQL grammar element definition
POWER
Precedence
TOP vs. SET ROWCOUNT 1 2
Precision
SUM result on DECIMAL data
precision SQL grammar element definition
Predicate
in stored procedures
predicate (grammar element defined)
predicate SQL grammar element definition
PRIMARY KEY
cannot ALTER column
grammar supported
PRINT
grammar supported
proc-expr (grammar element defined)
proc-expr SQL grammar element definition
proc-search-condition (grammar element defined)
proc-search-condition SQL grammar element definition
proc-stmt (grammar element defined)
proc-stmt SQL grammar element definition
PROC.DDF
procedure-name (grammar element defined)
procedure-name SQL grammar element definition
Procedures
creating
psp_column_attributes
psp_column_rights
psp_columns
psp_fkeys
psp_groups
psp_help_sp
psp_help_trigger
psp_help_udf
psp_help_view
psp_indexes
psp_pkeys
psp_stored_procedures
psp_table_rights
psp_tables
psp_triggers
psp_udfs
psp_users
psp_views
PUBLIC
grammar supported
public-or-user-group-name SQL grammar element definition
Push down filter
See table filter
Q
QUARTER
query-specification (grammar element defined)
query-specification SQL grammar element definition 1 2
Quotation mark, representing single
Quoted string in SQL statement
maximum size
Quotes
matching data that contains
R
RADIANS
RAND
Ranges
data types
REAL data type
Real Infinity
representation
referencing-alias (grammar element defined)
referencing-alias SQL grammar element definition
Referential integrity
delete rules 1 2
update rules
X$Relate system table
referential-actions (grammar element defined)
referential-actions SQL grammar element definition
referential-delete-action (grammar element defined)
referential-delete-action SQL grammar element definition
referential-update-action (grammar element defined)
referential-update-action SQL grammar element definition
Regional settings
comma as decimal separator
data display 1 2
data entry
RELATE.DDF
Relational index definitions
Btrieve indexes and 1 2 3 4
Relational security
RELEASE SAVEPOINT
grammar supported
release-statement SQL grammar element definition
REPLACE
Replacing data files 1 2
REPLICATE
Replication
SET TIME ZONE and
Reserved words
~See also Words to avoid
ABORT
ADD
AFTER
ALL
ALTER
AND
ANY
AS
ASC
ATOMIC
AVG
BEFORE
BEGIN/BEGINS
BETWEEN
BY
CALL
CASCADE
CASE
CAST
CHECk
CLOSE
COALESCE
COLLATE
COLUMN
COMMIT
COMMITTED
CONSTRAINT
CONVERT
COUNT
CREATE
CREATETAB
CROSS
CS
CURDATE
CURRENT
CURSOR
CURTIME
DCOMPRESS
DECIMALSEPARATORCOMMA
DECLARE
DEFAULT
DELETE
DENY
DESC
DIAGNOSTICS
DICTIONARY
DISTINCT
DO
DROP
EACH
ELSE
END
EX
EXEC
EXECUTE
EXISTS
FETCH
FN
FOR
FOREIGN
FROM
FULL
FUNCTION
GRANT
GROUP
HANDLER
HAVING
IF
IN
INDEX
INNER
INOUT
INSERT
INTERNAL
INTO
IS
ISOLATION
JOIN
KEY
LEAVE
LEFT
LEGACYOWNERNAME
LEVEL
LIKE
LOGIN
LOOP
MAX
MIN
MODIFIABLE
MODIFY
NEW
NEXT
NO
NOT
NOW
NULL
OF
OFF
OLD
ON
ONLY
OPEN
OR
ORDER
OUT
OUTER
OWNER
PAGESIZE
PRIMARY
PRINT
PROCEDURE
PUBLIC
READ
REFERENCES
REFERENCING
RELEASE
REPEAT
REPEATABLE
REPLACE
RESTRICT
RETURN
RETURNS
REVOKE
RIGHT
ROLLBACK
ROW
ROWCOUNT
SAVEPOINT
SECURITY
SELECT
SERIALIZABLE
SET
SIGNAL
SIZE
SQLSTATE
SSP_EXPR
SSP_PRED
START
SUM
SVBEGIN
SVEND
T
TABLE
THEN
TO
TOP
TRANSACTION
TRIGGER
TRIGGERSTAMPMISC
TRUEBITCREATE
TRUENULLCREATE
TS
UNCOMMITTED
UNION
UNIQUE
UNTIL
UPDATE
USER
USING
VALUES
VIEW
WHEN
WHERE
WHILE
WITH
WORK
WRITE
RESTRICT
grammar supported
ON DELETE
result SQL grammar element definition
Results
returning partial set 1 2
Retrieving data
about
RETURNS
example of in CREATE PROCEDURE
REVOKE
grammar supported
Revoking rights
RI. See Referential integrity
RIGHT
Rights
granting
revoking
RIGHTS.DDF
ROLLBACK WORK
grammar supported
rollback-statement SQL grammar element definition
ROUND
ROWCOUNT (SET)
ROWCOUNT global variable. See @@ROWCOUNT
Rows
limiting number returned 1 2
limits
Rowset size
specifying
RTRIM
S
SAVEPOINT
grammar supported
savepoint-name SQL grammar element definition
Scalar functions
ABS
ACOS
ASCII
ASIN
ATAN
ATAN2
BIT_LENGTH
CAST
categories
conversion
date
numeric
string
system
time
CEILING
CHAR
CHAR_LENGTH
CHARACTER_LENGTH
COALESCE
CONCAT
CONVERT
COS
COT
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DATABASE
DAYNAME
DAYOFMONTH
DAYOFYEAR
DEGREES
EXP
EXTRACT
FLOOR
HOUR
IF
IFNULL
ignore fractional seconds in TIMESTAMP
ISNULL
LCASE or LOWER
LEFT
LENGTH
LOCATE
LOG
LOG10
LTRIM
MINUTE
MOD
MONTH
MONTHNAME
NOW
NULL
NULLIF
OCTET_LENGTH
PI
POSITION
POWER
QUARTER
RADIANS
RAND
REPLACE
REPLICATE
RIGHT
ROUND
RTRIM
SECOND
SIGN
SIN
SPACE
SQRT
STUFF
SUBSTRING
TAN
TIMESTAMPADD
TIMESTAMPDIFF
TRUNCATE
UCASE or UPPER
USER
WEEK
YEAR
scalar-function (grammar element defined)
scalar-function SQL grammar element definition
scale SQL grammar element definition
Schema Executor 1 2
example usage
executing sample XML file
log messages
table filters used with
SchemaExec
See Schema Executor
search-condition (grammar element defined)
search-condition SQL grammar element definition
SECOND
Secured databases
with Btrieve Owner Names
Security
enabling and disabling
owner names 1 2
password of Master user
relational
SET OWNER
setting
system tables
rights (X$Rights)
users (X$User)
SELECT
grammar supported
maximum number of columns
TOP clause
SELECT (with INTO)
grammar supported
select-item (grammar element defined)
select-item SQL grammar element definition
select-list (grammar element defined)
select-list SQL grammar element definition
Separator
comma as decimal
data display 1 2
data entry
SET DECIMALSEPARATORCOMMA 1 2
Set Function
grammar supported in SELECT statements
SET OWNER
SET PASSWORD
grammar supported
SET ROWCOUNT
SET SECURITY
grammar supported
SET TIME ZONE
SET TRUENULLCREATE
grammar supported
SET VARIABLE
grammar supported
set-function (grammar element defined)
set-function SQL grammar element definition
Shift
sign value for positive NUMERIC data types
SIGN
Sign value
for positive NUMERIC data types
SIGNAL
grammar supported
SIN
Single quote
matching data that contains a
Single quote, representing
Size
of data page, specifying
Sort order in keys
string
SPACE
Spacing
in BINARY columns
in CHAR columns
in LONGVARBINARY columns
in LONGVARCHAR columns
in VARCHAR columns
Special characters
SPID global variable. See @@SPID
SQL
description and purpose
enhanced access for COBOL
components
using
extensions to standards
list of statements
maximum length of statement
maximum size of quoted string in statement
reserved words
security
statement delimiter
changing in PCC
statements
data control
data definition
data manipulation 1 2
delimited identifiers
types of
system tables. See System tables
use of statements with COBOL data constructs 1 2 3 4 5 6 7 8 9
variables
limits
parameters
SQL Editor
OEM characters and
SQL grammar element definition
alter-option
alter-option-list
as-or-semicolon
before-or-after
call-arguments
col-constraint
collation-name
column-constraint
column-definition
column-name
