Difference between revisions of "Builtin functions and Stored Procedures"
(→String Fnctions) 
DrewJensen (Talk  contribs) (→Modifiers) 

Line 417:  Line 417:  
    
}  }  
+  
+  == Modifiers ==  
+  
+  You may want to modify the results returned by a query to either limit the number of records returned or you may want to only returns records with unique values. This is accomplished by using the SELECT statement modifiers LIMIT, TOP, or DISTINCT.  
+  
+  In the case of LIMIT and TOP, Base does not allow their use without turning 'Escape Processing' off first. This is done in the query design window by  
+  1) Turn of the GUI designer  
+  2) Select the 'Run SQL Direct' toolbar button.  
+  
+  === Limit / Top ===  
+  
+  === Distinct ===  
Revision as of 17:28, 18 December 2007
Contents
Overview
The HSQLdb database engine included with Base supports the following functions and stored procedures for use within queries.
The Query component in Base allows for SQL commands that return a result set. i.e. SELECT and CALL
The component supports two views:
 GUI Query Designer (Designer View)
 Text editor (SQL View)
Queries created and executed in Designer and SQL View support the use of named replaceable parameters in these statements, in most cases. Use of this feature is controlled by the option 'Escape Processing'.
Escape Processing is always ENABLED when using Designer View.
Escape Processing is ENABLED by default in SQL View, but may be DISABLED by selecting the 'Run SQL directly' tool button.
The SQL window in Base (opened with the menu commands TOOLS > SQL) allows any valid SQL statement to be executed, but it does not return any data results to the user. Statements executed in this window serve two main purposes.
 Data Manipulation Language ( DML ) statements. i.e. UPDATE, DELETE, INSERT.
 Data Definition Language ( DDL ) statements. i.e. CREATE TABLE, ALTER TABLE, etc.
Multiple statements may be executed as a batch by ending each statement with a semicolon.
All functions listed below are available for use in the SQL window.
Each table below is formatted as follows:
 Function Name  this is the actual function, along with required or optional parameters
 Designer  Yes in this column means it may be used in Designer View. No, the function must be used in SQL View.
 Esc Proc  Yes in this column means it may be used in SQL View, with escape processing ENABLED. No, the function is only available in SQL View with escape processing DISABLED.
 Comments  A brief description of what the function does, the parameters expected and the type of output generated.
Numerical Functions
Function Name  Desinger  Esc Proc  Comments 

ABS(d)  YES  YES  Returns the absolute value of a number. ABS( 1 ) = ABS( 1 ) 
ACOS(d)  YES  YES  Returns the arc cosine of an angle, in the range of 0.0 through pi. ACOS( .5 ) = 1.05 
ASIN(d)  YES  YES  Returns the arc sine of an angle, in the range of pi/2 through pi/2. ASIN( .5 ) = 0.52 
ATAN(d)  YES  YES  Returns the arc tangent of an angle, in the range of pi/2 through pi/2. ATAN( .5 ) = 0.46 
ATAN2(a,b)  YES  YES  Returns the tangent of a/b. ATAN2( 1, 2 ) = 0.46 
BITAND(a,b)  YES  YES  Returns a & b. BITAND( 0, 1 ) = 0 
BITOR(a,b)  YES  YES  Returns a  b BITOR( 0,1 ) = 1 
BITXOR(a,b)  YES  YES  Returns the bitwise logical xor of the given integer values. BITXOR( 0, 1 ) = 1 
CEILING(d)  YES  YES  Rreturns the smallest integer that is not less than d CEILING( 8.7 ) = 9 
COS(d)  YES  YES  Returns the trigonometric cosine of an angle. COS( .5 ) = 0.88 
COT(d)  YES  YES  Returns the cotangent of an angle. COT( .5 ) = 1.83 
DEGREES(d)  YES  YES  Converts radians to degrees. DEGREES( 1 ) = 57.29577951 
EXP(d)  YES  YES  Returns the exponential number e (i.e., 2.718...) raised to the power of a double value. EXP( 2 ) = 7.389056 
FLOOR(d)  YES  YES  Returns the largest double value that is not greater than the d. FLOOR( 8.7 ) = 9 
LOG(d)  NO  NO  Returns the natural logarithm (base e) of d. d must be greater than 0.0. LOG( 1.5 ) = 0.41 LOG( 1 ) = 1.#NAN SEE ISSUE#69054 
LOG10(d)  YES  YES  Returns the logarithm (base 10) of d. d must be greater than 0.0. LOG10( 1.5 ) = 0.18 LOG10( 1 ) = 1.#NAN 
MOD(a,b)  YES  YES  Returns a modulo b MOD( 7, 2 ) = 1 
PI()  YES  YES  The double value that is closer than any other to pi, the ratio of the circumference of a circle to its diameter. PI() = 3.14159265 
POWER(a,b)  YES  YES  Returns of value of the first argument, a, raised to the power of the second argument, b. POWER( 2, 2 ) = 4 
RADIANS(d)  YES  YES  Converts degrees to radians RADIANS( 1 ) = 0.01745329 
RAND()  YES  YES  Returns a random number x >= 0.0 and < 1.0 RAND() MIGHT EQUAL 0.34 
ROUND(a,b)  YES  YES  Rounds a to b digits after the decimal point. ROUND( 1.0333, 2 ) = 1.0300 
ROUNDMAGIC(d)  YES*  YES*  solves certain rounding problems, specifically when numbers approach 0.0 such as 3.113.10.001 ROUNDMAGIC( 3.113.10.001 ) = 0.01 * Currently when used with escape processing enabled the return value is changed to a string. The workaround for this is to cast the result back to a dboule. CAST( ROUNDMAGIC( D ) AS DOUBLE ) See Issue#81031 
SIGN( d )  YES  YES  Returns 1 if d is smaller than 0, 0 if d==0 and 1 if d is bigger than 0 SIGN( 0.001 ) = 1 
SIN(d)  YES  YES  Returns the trigonometric sine of an angle. d is an angle in radians SIN( PI() / 12 ) = 0.25881905 
SQRT( d )  YES  YES  Returns the square root of d. <br. SQRT( 9 ) = 3 
TAN( A )  YES  YES  Returns the trigonometric tangent of an angle, A. A  and angle in in radians. TAN( 0.01745329 * 20 ) = 0.36397018 
TRUNCATE(a,b)  YES  YES  Truncates a to b digits after the decimal point. TRUNCATE( 1.0333, 3 ) = 1.0330 
String Functions
Function Name  Desinger  Esc Proc  Comments 

ASCII(s)  YES  YES  Returns the Unicode code value of the leftmost character ofs as an int . This is the same as the ASCII value if the string contains only ASCII characters. ASCII( 'ONE' ) = 79 
BIT_LENGTH(str)  YES  YES  Returns the length of the string in bits BIT_LENGTH ( 'ONE' ) = 48 Note Each character is assumed to be 16 bits long. 
CHAR(c)  YES  YES  Returns the character string corresponding to the given ASCII (or Unicode) value C. Note: In some SQL CLI implementations, a null is returned if the range is outside 0..255.In HSQLDB, the corresponding Unicode character is returned unchecked. CHAR( 79 ) = O 
CHAR_LENGTH(str)  YES  YES  Returns the length of the string in characters CHAR_LENGTH('ONE') = 3 
CONCAT(str1,str2)  YES  YES  Returns str1 + str2 CONCAT( 'ONE', 'HUNDRED' ) = ONEHUNDRED 
DIFFERENCE(s1,s2)  YES  YES  Returns the difference between the sound of s1 and s2. If either String is NULL, zero is returned. DIFFERENCE( 'SMITH', 'SMYTH' ) = 0 ; DIFFERENCE( 'WILD', 'CHILD' ) = 1 ; DIFFERENCE( 'TUPLE', 'SUPPLE' ) = 1 ; DIFFERENCE( 'BRAKE', 'BIKE' ) = 2 
HEXTORAW(s1)  YES  YES  Returns translated string ; The given String must consist of a sequence of 4 digit hexidecimal character substrings. If its length is not evenly divisible by 4, null is returned. If any of its 4 character subsequences cannot be parsed as a 4 digit, base 16 value, then a NumberFormatException is thrown. '00FF', '00ff00ff' is acceptable ; '0x00FF', 'FF' is NOT and will return null HEXTORAW( '0041' ) = A ; HEXTORAW( 'FF41' ) = a ; HEXTORAW( '00410061' ) = Aa 
INSERT(s,start,len,s2)  YES  YES  Returns a character sequence which is the result of writing the first length number of characters from the second given String over the first string. The start position in the first string where the characters are overwritten is given by start .
Note: In order of precedence, boundry conditions are handled as follows:

LCASE(s)  YES  YES  Converts s to lower case LCASE( 'ONE' ) = one 
LEFT(s,count)  YES  YES  Returns the leftmost count of characters of s Note: boundry conditions are handled in the following order of precedence:

LENGTH(s)  YES  YES  Returns the number of characters in s LENGTH( 'ONE' ) = 3 
LOCATE(search,s,[start])  YES  YES  Returns the first index (1=left, 0=not found) where search is found in s, starting at start LOCATE( 'ME', 'FIND ME IF YOU CAN', 1 ) = 6 ; LOCATE( 'ME', 'FIND ME IF YOU CAN', 7 ) = 0 
LTRIM(s)  YES  YES  Removes all leading blanks in s LTRIM( ' ONE ' ) = "ONE " 
LOWER(s)  YES  YES  Converts s to lower case LOWER( 'ONE' ) = one 
OCTET_LENGTH(str)  YES  YES  Returns the length of the string in bytes (twice the number of characters) OCTET_LENGTH( 'ONE' ) = 6 
RAWTOHEX(s1)  YES  YES  Returns translated string, where s1 is any character string and returns the string representation of the characters as hex values RAWTOHEX( 'A' ) = 0041 ; RAWTOHEX( 'Aa' ) = 00410061 
POSITION (<string expression> IN <string expression 1>)  YES  YES  If the first string is a substring of the second one, returns the position of the substring, counting from one; otherwise 0 POSITION ( 'THIS' IN 'THIS AND THAT' ) = 1 
REPEAT(s,count)  YES  YES  Returns s repeated count times REPEAT( 'X', 4 ) = XXXX 
REPLACE(s,replace,s2)  YES  YES  Replaces all occurrences of replace in s with s2 REPLACE( 'WHAT XXXX BROWN COW.', 'XXXX', 'NOW' ) = WHAT NOW BROWN COW. 
RIGHT(s,count)  YES  YES  Returns the rightmost count of characters of s

RTRIM(s)  YES  YES  Removes all trailing spaces RTRIM( ' ONE ' ) = " ONE" 
SOUNDEX(s)  YES  YES  Returns a four character code representing the sound of s. NonASCCI characters in the input String are ignored.SOUNDEX( 'SMITH' ) = S530 ; SOUNDEX( 'SMYTH' ) = S530 
SPACE(count)  YES  YES  Returns a string consisting of count spaces SPACE( 4 ) = " " 
SUBSTR(s,start[,len]) SUBSTRING(s,start[,len]) 
YES  YES  Returns the substring starting at start (1=left) with length len Note: The rules for boundary conditions on s, start and length are,in order of precedence: 1.) if s is null, return null 2.) If length is less than 1, return null. 3.) If start is 0, it is treated as 1. 4.) If start is positive, count from the beginning of s to find the first character postion. 5.) If start is negative, count backwards from the end of s to find the first character. 6.) If, after applying 2.) or 3.), the start position lies outside s, then return null 7.) if length is ommited or is greated than the number of characters from the start position to the end of s, return the remaineder of s, starting with the start position. SUBSTR( 'HERE I AM', 6, 1 ) = I ; SUBSTR( 'HERE I AM', 6 ) = I AM 
SUBSTRING( s FROM start [FOR len] )  YES  YES  Alternate syntax where s may be a string expression SUBSTRING ( 'HERE I AM' FROM 6 FOR 1 ) = I ; SUBSTRING ( 'HERE I AM' FROM 6 ) = I AM 
TRIM( [[ LEADING  TRAILING  BOTH ] [TRIMSTR]] FROM s )  YES*  YES*  Returns the character sequence s , with the leading, trailing or both the leading and trailing occurences of the first character of the character sequence trimstr removed.If trimstr is not supplied SPACE is used.TRIM( BOTH FROM ' ONE ' ) = "ONE" ; TRIM ( BOTH 'O' FROM 'OONEOO' ) = NE * Note with escape processing DISABLED the command may be abbreviated to TRIM ( FROM ' ONE ' ) AS "OUTPUT" = "ONE"  An alias must be used in this case also 
UCASE(s)  YES  YES  Converts s to upper case UCASE( 'one' ) = ONE 
UPPER(s)  YES  YES  Converts s to upper case UPPER( 'one' ) = ONE 
Date and Time Functions
Function Name  Desinger  Esc Proc  Comments  

CURDATE()  YES  YES  Returns the current date. This is the current system date on your PC. CURDATE() = 09/01/07 NOTE  The parenthesize are required.  
CURTIME()  YES  YES  Returns the current time. This is the current system time on your PC. CURTIME() = 10:16:04 PM NOTE  The parenthesize are required.  
DATEDIFF(string, datetime1, datetime2)  YES  YES  Returns the count of units of time elapsed from datetime1 to datetime2. The string indicates the unit of time and can have the following values 'ms'='millisecond', 'ss'='second','mi'='minute','hh'='hour', 'dd'='day', 'mm'='month', 'yy' = 'year'. Both the long and short form of the strings can be used. DATEDIFF('dd', '20070801', '20070901' ) = 31  
DAYNAME(date)  YES  YES  Returns the name of the day of the week. DAYNAME( '20070901' ) = Saturday  
DAYOFMONTH(date)  YES  YES  Returns the day of the month (131) DAYOFMONTH( '20070901' ) = 1  
DAYOFWEEK(date)  YES  YES  Returns the day of the week (1 means Sunday) DAYOFWEEK( '20070901' ) = 7  
DAYOFYEAR(date)  YES  YES  Returns the day of the year (1366) DAYOFYEAR( '20070901' ) = 244  
HOUR(time)  YES  YES  Return the hour (023) HOUR( '21:16:04' )  
MINUTE(time)  YES  YES  Returns the minute (059) MINUTE( '21:16:04' ) = 16  
MONTH(date)  YES  YES  Returns the month (112) MONTH( '20070901' ) = 9  
MONTHNAME(date)  YES  YES  Returns the name of the month, MONTHNAME( '20070901' ) = September  
NOW()  YES  YES  Returns the current date and time as a timestamp) use of CURRENT_TIMESTAMP instead is suggested NOW() = 09/01/07 10:34 PM  
QUARTER(date)  YES  YES  Returns the quarter (14), with the new year starting in January QUARTER( '20070901' ) = 3  
SECOND(time)  YES  YES  Returns the second (059) SECOND( CURRENT_TIME ) MAY EQUAL 6  
WEEK(date)  YES  YES  Returns the week of this year (153) WEEK( '20070901' ) = 35  
YEAR(date)  YES  YES  Returns the year YEAR( '20070901' ) = 2007  
CURRENT_DATE  YES  YES  Returns the current date CURRENT_DATE = 09/01/07  
CURRENT_TIME  YES  YES  Returns the current time CURRENT_TIME = 10:44:28 PM  
CURRENT_TIMESTAMP  YES  YES  Returns the current timestamp CURRENT_TIMESTAMP = 09/01/07 10:34 PM  
TO_CHAR( datetime, format String )  YES  YES  Returns a string from a date or datetime, based on the format mask Following is a list of valid format mask character sequences,

System Functions
Function Name  Desinger  Esc Proc  Comments 

DATABASE()  YES  YES  Returns the fully qualified file name of the Base file DATABASE() = C:\Documents and Settings\HP_Owner\My Documents\Forum\ChucrchLibrary\new_library.odb 
USER()  NO  NO  Returns the name of the user connected to the Base database NOTE  This command requires use of the parentheses. USER() = SA 
CURRENT_USER  YES  YES  Returns the name of the user connected to the Base database. This is actually a macro that calls the function USER(). NOTE  Use of parentheses is not allowed with this macro. CURRENT_USER = SA 
IDENTITY()  YES  YES  Returns the last identity value that was inserted by this connection. 
Aggregate Functions
Aggregate functions are used to perform some computation against group of values. Each field in a table can be thought of as one group of values. Use of the GROUP BY clause in a select statement allows the creation of multiple value groups.
Function Name  Desinger  Esc Proc  Comments 

COUNT( Any  * )  YES  YES  Returns the count of not null entries in a column or when used with * the count of records. 
MIN(d)  YES  YES  Returns the smallest value in a group. 
MAX(d)  YES  YES  Returns the largest value in a group. 
SUM(d)  YES  YES  Adds all values in a group together. 
AVG(d)  YES  YES  Returns the average for the group of values. 
SOME(b)  NO  NO  Returns TRUE if any value in a group = TRUE 
EVERY(b)  YES  YES  Returns TRUE only if all values in a group = TRUE. 
Statistical Functions
Function Name  Desinger  Esc Proc  Comments 

VAR_POP  Returns the biased variance of a group of numbers.  
VAR_SAMP  Returns the sample variance of a group of numbers.  
STDDEV_POP  
STDDEV_SAMP 
Modifiers
You may want to modify the results returned by a query to either limit the number of records returned or you may want to only returns records with unique values. This is accomplished by using the SELECT statement modifiers LIMIT, TOP, or DISTINCT.
In the case of LIMIT and TOP, Base does not allow their use without turning 'Escape Processing' off first. This is done in the query design window by 1) Turn of the GUI designer 2) Select the 'Run SQL Direct' toolbar button.
Limit / Top
Distinct
Author: Base default functions Drew 17:47, 11 September 2007 (CEST)
Please do not change the logical content of this site without
acknowledge of the author or the OOo QA Project Lead/CoLeads.