StelsCSV Driver supports the following SQL statements and syntax:

 

SELECT [ ALL | DISTINCT ]
{ selectExpression } [, ... ]
FROM table
List
[ WHERE whereExpression  ]
[ GROUP BY column [, ...] ] 
[ ORDER BY orderExpression [, ...] ]
[ HAVING selectExpression [, ...] ]
[ UNION [ALL] selectStatement ]


table
List:

table | tableList JOIN table ON joinExpression 

 

joinExpression:

column = column [ AND joinExpression ] 


selectExpression:
{ value | COUNT( * ) | {COUNT | MIN | MAX | SUM | AVG} ( { [ DISTINCT column | value } ) }  [ AS ] [ alias ]


whereExpression:

[ 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 ]

 

value

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 -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 | 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( ) )

 

 [HOME]   [TOP]