SQL Select With Functions

Syntax

SELECT select_columns FROM table_name [WHERE expression]
select_columns:   * for all columns or comma separated list of column names
table_name:  name of table to select records from
expression:   column | constant | parameter | function | not expression | (expression) |
expression operator expression | in (expression [, expression...])
operator:   or | and | = | != | < | > | <= | >=
function:   function_name(expression [, expression...])

The following table lists functions supported by SonicBase.

Function NameDescription
absReturns the absolute value of the first parameter
avgReturns the average value of all the parameters
bit_andReturns bitwise AND of the first two parameters
bit_notReturns bitwise NOT of the first parameter
bit_orReturns bitwise OR of the first two parameters
bit_shift_leftReturns the first parameter shifted left by the number of bits specified by the second paramter
bit_shift_rightReturns the first parameter shifted right by the number of bits specified by the second paramter
bit_xorReturns bitwise XOR of the first two parameters
ceilingReturns the smallest integer value not less than the first paramter
char_lengthReturns the number of characters in the first parameter
coalesceReturns the first non-null parameter
concatReturns the string created by appending the second paramter to the first paramter
cosReturns the cosine of the first paramter
cotReturns the cotangent of the first paramter
customAllows you to plug in a custom function into the where clause
date_addReturns the date created by adding the milliseconds in the second parameter to the timestamp first parameter
dayReturns the day of the month of the timestamp first paramter
day_of_weekReturns the day of the week of the timestamp first parameter
day_of_yearReturns the day of the month of the timestamp first paramter
floorReturns the largest integer value not greater than the first paramter
hexReturns the hexidecimal representation of the string first parameter
hourReturns the hour of the day of the timestamp first parameter
index_ofReturns the offset in the string first parameter of the string second parameter
logReturns the natural logarithm of the first parameter
log10Returns the base-10 logarithm of the first parameter
lowerReturns the lower case representation of the string first parameter
maxReturns the maximum value of all the parameters
max_timestampReturns the maximum timestamp of all the parameters
minReturns the minimum value of all the parameters
min_timestampReturns the minimum timestamp of all the paramters
minuteReturns the minute of the hour of the timestamp first parameter
modReturns the modulo value of the first two parameters
monthReturns the month of the year of the timestamp first parameter
nowReturns the current date and time as a timestamp
piReturns the value of pi
powerRerturns the value of the first parameter raised to the power of the second parameter
radiansReturns the first parameter converted to redians
replaceReturns the string first parameter with all occurrances of the second parameter replaced by the third parameter
roundReturns the first parameter rounded to the nearest integer
secondReturns the second of the minute of the timestamp first parameter
sinReturns the sine of the first parameter
sqrtReturns the square root of the first parameter
strReturns the string representation of the first parameter
tanReturns the tangent of the first parameter
trimReturns a string with the leading and trailing spaces removed from the first parameter
upperReturns the string first parameter converted to upper case
week_of_monthReturns the week of the month of the timestamp first parameter
week_of_yearReturns the week of the year of the timestamp first parameter
yearReturns the year of the timestamp first parameter

Custom Functions

You may add the function named "custom" to insert a custom function into the where clause. Your function muat reside in a class with a default constructor and it must have a signature like the following:

public Object <method_name>(Object[] parms);

A call to the function looks like the following:

custom(<class_name>, <method_name>, parm[, parm...])

Example Call to Custom Function

select * from persons where custom('com.sonicbase.database.CustomFunctions', 'plus', id, id2) < 3

Example Implementation of Custom Function



public class CustomFunctions {

public Object plus(Object[] o) { if (o[0] == null) { if (o[1] != null) { return o[1]; } return null; } long lhs = Long.valueOf(o[0]); if (o[1] == null) { return lhs; } long rhs = Long.valueOf(o[1]); return lhs + rhs; } }