www.openlinksw.com
docs.openlinksw.com

Book Home

Contents
Preface

SQL Reference

Datatypes
Date Literals Casting Time & Date Manipulation Declaring Collations of Expressions
User Defined Types
XML Column Type
Identifier Case & Quoting
Wide Character Identifiers
Qualified Names
Literals, Brace Escapes
CREATE TABLE Statement
DROP TABLE Statement
CREATE INDEX Statement
DROP INDEX Statement
ALTER TABLE Statement
CREATE VIEW Statement
CREATE XML SCHEMA Statement
DROP XML SCHEMA Statement
Sequence Objects
INSERT Statement
UPDATE Statement
SELECT Statement
COMMIT WORK, ROLLBACK WORK Statement
CHECKPOINT, SHUTDOWN Statement
Stored Procedures as Views & Derived Tables
GRANT, REVOKE Statement
SET Statement
Anytime Queries
Best Effort Union
Standard and User-Defined Aggregate Functions
Virtuoso SQL Optimization
SQL Inverse Functions
SQL Grammar
Bitmap Indices
Transitivity in SQL
Fast Phrase Match Processor
Geometry Data Types and Spatial Index Support
SQL Bulk Load, ELT, File Tables and Zero Load Operations

9.1. Datatypes

CHARACTER:

CHARACTER

VARCHAR

VARCHAR'('INTNUM')'

NVARCHAR

NVARCHAR'('INTNUM')'

CHAR'('INTNUM')'

NUMERIC:

NUMERIC

NUMERIC'('INTNUM')'

NUMERIC'('INTNUM','INTNUM')'

DECIMAL:

DECIMAL

DECIMAL'('INTNUM')'

DECIMAL'('INTNUM','INTNUM')'

INTEGER:

INT

INTEGER

SMALLINT

FLOAT:

FLOAT

FLOAT'('INTNUM')'

REAL:

REAL

DOUBLE PRECISION

LONG VARCHAR:

BLOB data

VARBINARY [( precision )]:

Binary data

LONG VARBINARY:

Binary BLOB data

TIMESTAMP:

TIMESTAMP

DATETIME

TIME

DATE

<UDT>:

User Defined Type with varbinary-like size properties.

LONG <UDT>:

User Defined Type with LONG varbinary-like size properties.

LONG XML:

LONG XML BLOB-like data type.

Note:

User Defined Types can be created from native or external types, composites or classes from any hosted language such as Java or .Net. Any User Defined Type can be used to define a column in a CREATE TABLE statement.

9.1.1. Date Literals

Virtuoso does not support date literals or the DATE reserved keyword. Literal dates should be enclosed in a conversion function such as stringdate(), as in this example:

select * from demo.demo.orders o
  where o.orderdate between stringdate('1994-01-01') And stringdate('1997-12-31')

Alternatively type casts can be used to explicitly instruct Virtuoso to assume a string as a date, see below.


9.1.2. Casting

Blob types can be cast to varchars. This will produce a string of up to 16 MB in length and an error for longer blobs.

Nothing can be cast to a blob type. Blobs only come from selecting blob columns and are created by assigning values to such columns.

Converting non-integer values to integers rounds towards 0.

Any data type can be converted to binary with the VARBINARY target data type. The result may or may not be meaningful. Specifically datetime can be converted to binary and back.

cast_expression :
		CAST '(' scalar_exp AS data_type [COLLATE collation_name ] ')'

collation_name :
	identifier
	| owner '.' identifier
	| qualifier '.' owner '.' identifier

The CAST expression converts the type of the scalar_exp into the data_type, if possible. If the conversion is not meaningful, as from a float to a date, an error is signalled.

CAST is the recommended way of converting between data types, including any conversion between strings, different number types and datetime types.

Examples:
select cast ('2000-1-3' as date);

select cast (cast ('2000-1-3' as date) as varchar);
	= 2000-01-03 00-00-00 000000

9.1.3. Time & Date Manipulation

The SQL92 standard functions for time and date queries are available. These are mapped to Virtuoso internal functions as follows:

The results of the above functions can also be obtained using the now() function and casting to the appropriate target type.

Dates and times should be input or compared as literals in a standard format. The following table describes the proper methods available:

Table: 9.1.3.1. Time & date syntax
Datatype ODBC Syntax SQL92 Casting
Date {d 'yyyy-mm-dd'} cast('yyyy-mm-dd' as date)
Time {t 'hh:mm:ss'} cast('hh:mm:ss' as time)
Datetime/timestamp {ts 'yyyy-mm-dd hh:mm:ss[.f...]'} cast('yyyy-mm-dd hh:mm:ss[.f...]' as datetime)

Example: comparing dates
select datecol from table
  where datecol > cast('1900-01-01' as date)
    and datecol < cast(now() as date);

now() is cast for explicit compatibility although not required.


9.1.4. Declaring Collations of Expressions

A collation can be declared with CAST for string expressions. Suppose insensitive_ascii were a collation name specifying case insensitive comparison of ASCII strings, the predicate:

CAST 'foo' as varchar collate insensitive_ascii) = CAST 'FOO as varchar collate insensitive_ascii)

would be true.

This will alter the semantic of string comparison and can be used to define a collation where the collation would otherwise be default, for instance when comparing results of expressions or constants. This can also alter the default collation implied by the collation declaration in column options in CREATE TABLE.