StelsEngine SQL Syntax
StelsEngine
supports
the following SQL statements and syntax:
query:
SELECT
[ TOP n [ PERCENT ]] [
ALL | DISTINCT ] select_list
FROM
table_reference_list
[
WHERE condition_expression ]
[ GROUP BY column [ , ... ]
]
[ HAVING
condition_expression [ , ... ] ]
[
union_clause ]
[ ORDER BY order_expression [ , ... ] ]
select_list:
{ { expression [ [ AS ] column_alias ] | table_name.* } [ , ... ] } | *
table_reference_list:
{ table_reference | table_join }
table_reference:
{ table_name | subquery } [ [ AS ] table_alias ]
table_name: { string | "string" | !string! | `string` }
table_alias:
{ string | 'string' | "string"
| !string! | `string` }
subquery: ( query )
table_join : table_reference join_clause [ join_clause, ... ]
join_clause:
[ INNER | { { LEFT | RIGHT | FULL } OUTER } ] JOIN table_reference ON join_expression
join_expression:
column = column [ AND join_expression ]
union_clause:
UNION [ ALL ] query [ union_clause, ... ]
expression:
{ value | COUNT( * ) |
aggregate_function } [ [ AS ] column_alias ]
aggregate_function:
{ COUNT | MIN | MAX | SUM | AVG } ( [ DISTINCT ] column )
column_alias: { string | 'string' | "string" | !string! | `string` }
condition_expression:
[
NOT
]
condition [ { OR | AND }
condition_expression
]
condition:
value { = | < | <= | > | >= | <> } { value | subquery }
| value BETWEEN value AND value
| value
[ NOT ] LIKE pattern
|
value [ NOT ] IN ( value [, ...] )
| value [ NOT ] IN subquery
| value IS [ NOT
] NULL
orderExpression:
column [ ASC | DESC ]
constant
|
column
| function ( [ value ] [ ,... ] )
| [ - ] value [ + | - | * |
/ value ]
constant
column: [ table_alias. ] { string | "string" | !string! | `string` }
Notes:
The driver uses only the ANSI SQL'92 notation for joins, e.g.: SELECT * FROM prices ps JOIN regions regs ON ps.regionid = regs.id JOIN products prod ON prod.prodid = ps.prodid
The driver supports non-correlated subqueries only.
LIKE pattern can include the following valid SQL wildcard characters: '%' - any string of zero or more characters, '_' - any single characters
INSERT INTO
tableName [ ( column [,...] ) ]
{ VALUES ( value [,...] )
| selectStatement }
UPDATE tableName SET column =
value [, ...]
DELETE FROM tableName
[ WHERE whereExpression ]
CREATE HASH INDEX indexName ON tableName
( column [,...] )
functions:
Conversion
functions
Name |
Description |
Example |
to_string(arg : any type) : string type
|
converts
an argument to a string value |
to_string(120), to_string(1.587), to_string(float_column),
to_string(date_column) |
to_string(arg :
integer | long | float | double type,
format : string type) : string type
|
converts
an argument to a string value
with the
specified
number format. |
to_string(123467.8, '###,###.##'), to_string(123467.8, '000000.000') |
to_string(arg :
integer | long | float | double type, format : string type, isoLang : string type, isoCountry : string type) : string type
|
converts
an argument to a string value
with the specified number format,
ISO language
code and ISO country code |
to_string(123467.8, '###,###.##', 'en', 'US'), to_string(123467.8, '000000.000', 'de', 'DE') |
to_string(arg : datetime type,
format : string type) : string type |
formats
the given argument into a date/time string with the specified
format |
to_string(date_column,
'yyyy-MM-dd HH:mm:ss') |
to_string(arg : datetime type,
format : string type, isoLang : string type,
isoCountry: string type)
: string type |
formats
the given argument into a date/time string with the specified format,
ISO language code and ISO country code |
to_string(date_column,
'MMMMM yyyy', 'en', 'US') |
to_int(arg : any type) : integer
type |
converts
an argument into a integer value |
to_int(1.578),
to_int('457')
|
to_long(arg : any type) : long
type |
converts
an argument into a long value |
to_long(1.578), to_long('123456789012')
|
to_float(arg: any type) : float
type |
converts
an argument into an floating-point value |
to_float(345), to_float('234.57') |
to_double(arg : any type) : double
type |
converts
an argument into a double value |
to_double(1.578), to_double('1234567890.1234')
|
to_date(arg: string type, format : string type) :
datetime type |
converts
an argument into a date/time value with the specified format |
to_date('2003-12-25','yyyy-MM-dd') |
to_date(arg: string type, format : string type, isoLang : string type, isoCountry : string type) : datetime type |
converts
an argument into a date/time value with the specified format, ISO language
code
and ISO country code |
to_date('September 2003',
'MMMMM yyyy', 'en', 'US') |
to_boolean(arg: any type) : boolean type | converts an argument into a boolean value |
to_boolean('true') |
String
functions
Name |
Description |
Example |
index_of(arg : string type, searchstr :
string type) : integer
type |
Returns the index location of the first occurrence of the specified string. |
index_of(str_column, 'John') |
last_index_of(arg : string type,
searchstr : string type)
: integer type |
Returns the index location of the last occurrence of the specified string. |
last_index_of(str_column, 'John') |
length(arg : string type) : integer
type |
returns
the number of characters in the string. |
length(str_column +
'qwerty') |
lower(arg : string type) : string
type |
converts
all of the characters in the string to lower case |
lower('ABCDE') |
matches(arg : string type,
regex : string type) : string
type |
attempts to match the given argument against the specified regular expression. The function returns 1 if the argument is matched and returns 0 in otherwise. |
matches('A1',
'[a-zA-Z][0-9]') |
replace_string(arg : string type, replaceable : string type,
replacement : string type) : string type |
replaces
each substring of specified argument with the given
replacement |
replace_string(str_column,
',' , '.') |
sub_string(arg : string type, beginIndex : integer type) : string
type |
returns
a string that is a substring of this string |
substring('text', 2) |
sub_string(arg : string type, beginIndex : integer type, endIndex : integer type) : string
type |
returns
a string that is a substring of this string |
substring('text', 2, 3) |
trim(arg: string type) : string
type |
returns
a string, with leading and trailing whitespace
omitted. |
trim(str_column) |
upper(arg : string type) : string
type |
converts
all of the characters in the string to upper case |
upper('abcde') |
Numeric functions
Name |
Description |
Example |
round(arg : float type | double type) : integer
type |
returns
the argument, rounded to the nearest integer. |
round(2.3478) |
sgn(arg : integer type | long type | float type | double type) : appropriate type |
returns
the sign of the argument as
|
sgn
(53), sgn (-47.8) |
abs | sin | cos | tan (arg : integer type | long type | float type | double type) : double type |
mathematical
functions |
abs(-5), sin(1), cos(1.2), tan (3.141) |
Other functions
Name |
Description |
Example |
current_date() : datetime type |
returns
the current date/time. |
current_date() |
nvl(arg : any type) :
appropriate type |
substitutes
a non-value when a null value is encountered. |
nvl(int_column, 0) |