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
USING keyword
WITH REPLACE keyword
alter-option SQL grammar element definition
alter-option-list SQL grammar element definition
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 V8
AUTOINCREMENT data type
AVG aggregate function
B
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
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
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
Codes
data type
col-constraint SQL grammar element definition
collation-name SQL grammar element definition
Column
attributes
system table (X$Attrib)
definitions
changing
creating
maximum
in a database
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 SQL grammar element definition
column-definition SQL grammar element definition
column-name SQL grammar element definition
Columns
number allowed in a trigger
Comma
as decimal separator
data display
data entry
COMMIT
grammar supported
commit-statement SQL grammar element definition
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 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 GROUP
grammar supported
CREATE INDEX
grammar supported
limitations
CREATE PROCEDURE
grammar supported
CREATE TABLE
file compression
grammar supported
IN DICTIONARY keyword
page size
USING keyword
WITH REPLACE keyword
CREATE TRIGGER
grammar supported
CREATE VIEW
grammar supported
Creating
groups
stored procedures
tables with legacy null support
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
LOGICAL
LSTRING
MONEY
NUMERIC
NUMERICSA
NUMERICSTS
REAL
STRING
supported
that cannot be indexed
TIME
TIMESTAMP
UNSIGNED BINARY
WSTRING
WZSTRING
ZSTRING
data-type SQL grammar element definition
data-type-name SQL grammar element definition
Database
maximum
number of columns
name
valid characters
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
DBQ
entry in odbc.ini
length of
DCOMPRESS
DDFs
changing data definitions without affecting data files
DECIMAL data type
precision of SUM() function result
Decimal separator
comma
display only
comma as 1 2
DECLARE
grammar supported
DECLARE CURSOR
grammar supported
DEFAULT
example of use in CREATE TABLE 1 2
in CREATE TABLE
in UPDATE 1 2
Default values, see Values, default
default-value SQL grammar element definition
Definitions
index
Btrieve indexes and 1 2 3
DEGREES
Delete
rule
cascade
restrict
DELETE
grammar supported 1 2
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 INDEX
grammar supported
DROP PROCEDURE
grammar supported
DROP TABLE
grammar supported
IN DICTIONARY keyword
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
EXISTS
grammar supported
EXP
Expression
in stored procedures
subquery on left side of
expression SQL grammar element definition
expression-or-subquery SQL grammar element definition
Extended data types
EXTRACT
F
FETCH
grammar supported
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
categories
logical
conversion
date
inserting value
numeric
string
system
time
inserting current
inserting value
G
Global variables
@@IDENTITY
@@ROWCOUNT
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
Grouped views
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
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
existing used when possible 1 2 3
system tables
X$Field
X$Index
Infinity
representation
ins-upd-del SQL grammar element definition
INSERT
default values and
grammar supported
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
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-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 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
data entry
LOCATE
Locking
table
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
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 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
Numeric functions supported
NUMERICSA data type
NUMERICSTS data type
O
OCTET_LENGTH
ODBC
API
conformance
exceptions to conformance
security
ODBC API
functions
supported
Odbc.ini
DBQ entry length
OEM to ANSI
character translation
connection string
ON DELETE
ON UPDATE
ONLY
grammar supported
Operators
greater than
less than
Optimizations
index usage
restriction
SQL Relational Database Engine
subquery on left side of expression
ORDER BY
SET ROWCOUNT and
TOP and
order-by-expression SQL grammar element definition
outer-join-definition SQL grammar element definition
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 List
number of arguments for stored procedures 1 2
parameter SQL grammar element definition
parameter-name SQL grammar element definition
parameter-type SQL grammar element definition
parameter-type-name SQL grammar element definition
Password
for Master user
table
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 SQL grammar element definition
PRIMARY KEY
cannot ALTER column
grammar supported
PRINT
grammar supported
proc-expr SQL grammar element definition
proc-search-condition SQL grammar element definition
proc-stmt SQL grammar element definition
PROC.DDF
procedure-name SQL grammar element definition
Procedures
creating
PUBLIC
grammar supported
public-or-user-group-name SQL grammar element definition
Q
QUARTER
Query
setting timeout via ODBC 1 2
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 SQL grammar element definition
Referential integrity
delete rules 1 2
update rules
X$Relate system table
referential-actions SQL grammar element definition
referential-delete-action SQL grammar element definition
referential-update-action SQL grammar element definition
Regional settings
comma as decimal separator
data display
data entry
RELATE.DDF
Relational index definitions
Btrieve indexes and 1 2 3
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
ABSOLUTE
ACCELERATED
ACTION
ADD
AFTER
ALL
ALTER
AND
ANY
AS
ASC
ATOMIC
AUTHORIZATION
AVG
BEFORE
BEGIN/BEGINS
BETWEEN
BINARYNULL
BIND
BLANK
BY
CALL
CASCADE
CASE
CHAIN
CHARACTER
CHECK
CLOSE
COBOL
COLLATE
COLUMN
COMMIT
COMMITTED
CONDITION
CONSTRAINT
CONTINUE
CONVERT
COUNT
CREATE
CREATETAB
CROSS
CS
CURDATE
CURRENT
CURSOR
CURTIME
D
DATABASE
DCOMPRESS
DECIMALSEPARATORCOMMA
DECLARE
DEFAULT
DELETE
DESC
DIAGNOSTICS
DICTIONARY
DISTINCT
DO
DROP
EACH
ELSE
ELSEIF
END
ESCAPE
EX
EXCLUSIVE
EXEC
EXECUTE
EXISTS
EXTERNAL
FETCH
FIRST
FN
FOR
FOREIGN
FROM
FULL
GRANT
GROUP
HANDLER
HAVING
HOUR
IDENTITY
IF
IN
INCLUDE
INDEX
INNER
INOUT
INSERT
INTERNAL
INTO
IS
ISOLATION
JOIN
KEY
LANGUAGE
LAST
LEAVE
LEFT
LEGACYOWNERNAME
LENGTH
LEVEL
LIKE
LOGICAL
LOGIN
LOOP
LOWER
MAX
MILLISECOND
MIN
MINUTE
MODIFIABLE
MODIFY
MODULE
MONEY
MONTH
NEW
NEXT
NO
NORMAL
NOT
NOTFOUND
NOW
NULL
OF
OFF
OLD
ON
ONLY
OPEN
OPENMODE
OPTION
OR
ORDER
OUT
OUTER
OWNER
OWNERACCESS
PAGESIZE
PREALLOCATE
PREPARE
PRIMARY
PRINT
PRIOR
PRIVILEGES
PROCEDURE
PUBLIC
RANGE
READ
READONLY
REFERENCES
REFERENCING
RELATIVE
RELEASE
REPEAT
REPEATABLE
REPLACE
REPLICATION
RESIGNAL
RESTRICT
RETURN
RETURNS
REVOKE
RIGHT
ROLLBACK
ROW
ROWCOUNT
SAVEPOINT
SCHEMA
SCROLL
SECURITY
SEG
SELECT
SERIALIZABLE
SET
SIGNAL
SIZE
SOME
SQLERROR
SQLEXCEPTIONS
SQLSTATE
SQLWARNINGS
SSP_EXPR
SSP_PRED
START
STATEMENT
STRINGNULL
SUM
SVBEGIN
SVEND
T
TABLE
THEN
THRESHOLD
TO
TOP
TRANSACTION
TRIGGER
TRIGGERSTAMPMISC
TRUENULLCREATE
TS
TYPE
UNCOMMITTED
UNDO
UNION
UNIQUE
UNTIL
UPDATABLE
UPDATE
UPPER
USER
USING
VALUE, VALUES
VERIFY
VIEW
WHEN
WHERE
WHILE
WITH
WORK
WRITE
YEAR
ZSTRING
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
categories
conversion
date
numeric
string
system
time
CEILING
CHAR
CHAR_LENGTH
CHARACTER_LENGTH
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
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 SQL grammar element definition
scale SQL grammar element definition
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 SQL grammar element definition
select-list SQL grammar element definition
Separator
comma as decimal
data display
data entry
SET DECIMALSEPARATORCOMMA
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 SQL grammar element definition
SIGN
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
SQL
description and purpose
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
variables
limits
parameters
SQL Data Manager
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
commit-statement
comparison-operator
constraint-name
correlation-name
cursor-name
data-type
data-type-name
db-name
default-value
expression
expression-or-subquery
fetch-orientation
group-name
index-definition
index-name
index-segment-definition
ins-upd-del
insert-values
join-definition
label-name
literal
order-by-expression
outer-join-definition
outer-join-type
parameter
parameter-name
parameter-type
parameter-type-name
password
positional-argument
precision
predicate
proc-expr
proc-search-condition
proc-stmt
procedure-name
public-or-user-group-name
query-specification 1 2
referencing-alias
referential-actions
referential-delete-action
referential-update-action
release-statement
result
rollback-statement
savepoint-name
scalar-function
scale
search-condition
select-item
select-list
set-function
sqlstate-value
table-constraint
table-constraint-definition
table-element
table-expression
table-name
table-privilege
table-reference
transaction-statement
trigger-name
user-group-name
user-name
user-password
value
value-list
values-clause
variable-name
view-name
SQL Grammar Elements
SQL Relational Database Engine
optimizations
performance
restriction optimization
temporary files, location
SQL_QUERY_TIMEOUT
SQLSetConnectOption
SQLSetStmtOption
SQLAllocConnect
SQLAllocEnv
SQLAllocStmt
SQLBindCol
SQLBindParameter
SQLBrowseConnect
SQLCancel
SQLColAttributes
SQLColumnPrivileges
SQLColumns
SQLConnect
SQLDataSources
SQLDescribeCol
SQLDescribeParam
SQLDisconnect
SQLDriverConnect
SQLDrivers
SQLError
SQLExecDirect
SQLExecute
SQLExtendedFetch
SQLFetch
SQLForeignKeys
SQLFreeConnect
SQLFreeEnv
SQLFreeStmt 1 2
SQLGetConnectOption 1 2
SQLGetCursorName
SQLGetData
with LONGVARBINARY
with LONGVARCHAR
SQLGetFunctions
SQLGetInfo 1 2
SQLGetStmtOption 1 2
SQLGetTypeInfo 1 2
SQLMoreResults 1 2 3
SQLNativeSql
SQLNumParams
SQLNumResultCols
SQLParamData
SQLPrepare
SQLPrimaryKeys
SQLProcedureColumns
SQLProcedures
SQLPutData
SQLRowCount
SQLSetConnectOption 1 2
SQLSetCursorName
SQLSetPos
SQLSetStmtOption 1 2
SQLSpecialColumns 1 2
SQLSTATE
grammar supported
sqlstate-value SQL grammar element definition
SQLStatistics
SQLTablePrivileges
SQLTables
SQLTransact
SQRT
START TRANSACTION
grammar supported
Statement delimiter, changing
Statements
maximum per connection
Static cursor
type changed by TOP
Status Codes
59
Stored procedures
calling
creating
data type limitations
dropping 1 2
executing
expressions
limits
max. number of columns 1 2
number of arguments in a parameter list 1 2
total size 1 2
predicates
system table (X$Proc)
use of variables as select items
using
String
functions supported
literal values
Maximum size of quoted string in SQL statement
STRING data type
Structured Query Language. See SQL
STUFF
Subquery
correlated 1 2
eliminating duplicate rows with DISTINCT
grammar supported
improved performance of
non-correlated
SUBSTRING
SUM aggregate function
and precision of DECIMAL type
Supported data types
Supported ODBC APIs
in Pervasive.SQL 2000
System
clock
locale setting
SQL functions supported
time
System tables
updating
X$Attrib
X$Depend
