StelsEngine SQL Syntax

 

StelsEngine supports the following SQL statements and syntax:

 

SELECT

INSERT

UPDATE

DELETE 

 

CREATE TABLE

CREATE HASH INDEX

DROP TABLE

 

 


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 ]

 

value:

constant

| column 
| function ( [ value ] [ ,... ] )
| [ - ] value [ + | - | * | /  value ]

 

constant: { 'string' | integer | long | float | double | NULL }

 

column: [ table_alias. ] { string | "string" | !string! | `string` }


Notes:


INSERT INTO tableName [ ( column [,...] ) ]
{ VALUES ( value [,...] )  |  selectStatement }

 


UPDATE tableName SET column = value [, ...] 
[ WHERE whereExpression ]

 


DELETE FROM tableName

[ WHERE whereExpression ]

 


CREATE TABLE tableName ( column datatype [, ...] )

 


CREATE HASH INDEX indexName ON tableName

( column [,...] )

 


DROP TABLE tableName

 


 

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') = to_boolean(1) = to_boolean(1.0) = true

* The driver also supports the JDBC escape syntax: {d 'yyyy-mm-dd'}, {t 'hh:mm:ss'}, {ts 'yyyy-mm-dd hh:mm:ss'} for converting a string argument into a date/time value.

 

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 -1, 0, or 1, depending on whether argument is negative, zero, or positive:

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), nvl (str_column, 'unknown')

 

 [HOME]   [TOP]