StelsCSV
Driver supports
the following SQL statements and syntax:
SELECT
[
ALL | DISTINCT ]
{ selectExpression
} [, ... ]
FROM tableList
[
WHERE whereExpression ]
[ GROUP BY column [, ...]
]
[ ORDER BY orderExpression [, ...] ]
[ HAVING selectExpression [, ...] ]
[ UNION [ALL]
selectStatement ]
table | tableList JOIN table ON joinExpression
joinExpression:
column = column [ AND joinExpression ]
selectExpression:
{ value | COUNT( * ) | {COUNT | MIN | MAX |
SUM | AVG} ( { [ DISTINCT
column | value } ) } [ AS ] [ alias ]
[
NOT
]
condition [ { OR | AND } condition ]
condition:
{ value { = | < | <= | > | >= | <> | != | IS
[NOT] } value
| value BETWEEN value AND value
|
value [ NOT ] IN ( value [, ...] )
| value IS [ NOT
] NULL
orderExpression:
column [ ASC | DESC ]
term
|
column
| function ( [value] [,...] )
| [ - ] value [ + | - | * |
/ value ]
term:
{ 'string' | number | floatingpoint
number
column:
[ tableAlias.] { string | "string" }
tableName: { string | "string" } [ AS
] [ tableAlias ]
function:
Converting
functions
Name |
Description |
Example |
to_string
(
arg : integer type | float type| datetime 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 : datetime type,
format :string type ) : string type |
formats
the given argument into a date/time string with specified
format |
to_string
( date_column, ‘yyyy-MM-dd HH:mm:ss’
) |
to_string
( arg : datetime type,
format : string type, isoLocale : string type )
: string type |
formats
the given argument into a date/time string with specified format
and
ISO language code (the two lower-case two-letter code as defined by
ISO-639) |
to_string
( date_column, ‘MMMMM yyyy’, ‘en’ ) |
to_int
( arg : float type | string type ) : integer
type |
converts
an argument into a integer value |
to_int
( 1.578 ), to_int ( ‘457’ )
|
to_float
( arg: integer type | string type ) : float
type |
converts
an argument into an floating-point value |
to_float
( 345 ), to_float ( ‘234.57’
) |
to_date
( arg: string type, format : string type ) :
datetime type |
converts
an argument into a date/time value with specified format |
to_date( ’2003-12-25’, ‘yyyy-MM-dd’ ) |
to_date
( arg: string type, format : string type, isoLocale : string type ) : datetime type |
converts
an argument into a date/time value with specified format and ISO language
code |
to_date( ’September 2003’, ‘MMMMM yyyy’, ‘en’) |
String
functions
Name |
Description |
Example |
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’
) |
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’ ) |
Number functions
Name |
Description |
Example |
round
( arg : float type ) : integer
type |
returns
the argument, rounded to the nearest integer. |
round
( 2.3478 ) |
sgn
( arg : integer type | float type
) |
returns
the sign of the argument as |
sgn
( 53 ), sgn ( -47.8 ) |
abs
| sin | cos | tan (arg : integer type | float 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 : integer type | replaceWith : integer type ) : integer
type |
substitutes
a non-value when a null value is encountered. |
nvl(
int_column, 0 ) |
nvl
( arg : float type | replaceWith : float type ) : float
type |
substitutes
a non-value when a null value is encountered. |
nvl
( float_column, 0.0
) |
nvl
( arg : string type | replaceWith : string type ) : string
type |
substitutes
a non-value when a null value is encountered. |
nvl
( str_column, ‘unknown’
) |
nvl
( arg : datetime type
| replaceWith : datetime type ) : datetime
type |
substitutes
a non-value when a null value is encountered. |
nvl
( date_column, current_date( )
) |