Difference between revisions of "Built-in functions and Stored Procedures"
DrewJensen (Talk | contribs) m (→Numerical Functions) |
(→Modifiers) |
||
(38 intermediate revisions by 7 users not shown) | |||
Line 1: | Line 1: | ||
− | ==Overview== | + | == Overview == |
The HSQLdb database engine included with Base supports the following functions and stored procedures for use within queries. | 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 | + | The Query component in Base allows for SQL commands that return a result set, i.e. SELECT and CALL. |
− | i.e. SELECT and CALL | + | |
The component supports two views: | The component supports two views: | ||
− | *GUI Query Designer (Designer View) | + | * GUI Query Designer (Designer View) |
− | *Text editor (SQL 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'. | 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'. | ||
Line 15: | Line 14: | ||
Escape Processing is ENABLED by default in SQL View, but may be DISABLED by selecting the 'Run SQL directly' tool button. | 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. | + | 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. | Statements executed in this window serve two main purposes. | ||
− | *Data Manipulation Language ( DML ) statements. i.e. UPDATE, DELETE, INSERT. | + | *Data Manipulation Language ( DML ) statements. i.e. UPDATE, DELETE, INSERT. |
− | *Data Definition Language ( DDL ) statements. i.e. CREATE TABLE, ALTER TABLE, etc. | + | *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 | + | 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. | + | All functions listed below are available for use in the SQL window. |
Each table below is formatted as follows: | Each table below is formatted as follows: | ||
− | *Function Name | + | *Function Name — this is the actual function, along with required or optional parameters |
− | *Designer | + | *Designer — Yes in this column means it may be used in Designer View. No, the function must be used in SQL View. |
− | *Esc Proc | + | *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 | + | *Comments — A brief description of what the function does, the parameters expected and the type of output generated. |
− | + | == Numerical Functions == | |
− | ==Numerical Functions== | + | |
{|cellpadding="10" border="1" | {|cellpadding="10" border="1" | ||
− | |+ | + | |+ |
|- | |- | ||
− | ! Function Name || | + | ! Function Name || Designer || Esc Proc || Comments |
|- | |- | ||
− | ! ABS(d) | + | ! <span id="ABS">ABS(d)</span> |
− | | YES || YES || Returns the absolute value of a number. < | + | | YES || YES || Returns the absolute value of a number.<br>ABS( -1 ) = ABS( 1 ) |
|- | |- | ||
− | ! ACOS(d) | + | ! <span id="ACOS">ACOS(d)</span> |
− | | YES || YES || Returns the arc cosine of an angle, in the range of 0.0 through pi. < | + | | YES || YES || Returns the arc cosine of an angle, in the range of 0.0 through pi.<br>ACOS( .5 ) = 1.05 |
|- | |- | ||
! ASIN(d) | ! ASIN(d) | ||
− | |YES ||YES || Returns the arc sine of an angle, in the range of -pi/2 through pi/2. < | + | |YES ||YES || Returns the arc sine of an angle, in the range of -pi/2 through pi/2.<br>ASIN( .5 ) = 0.52 |
|- | |- | ||
! ATAN(d) | ! ATAN(d) | ||
− | | YES || YES || Returns the arc tangent of an angle, in the range of -pi/2 through pi/2. < | + | | YES || YES || Returns the arc tangent of an angle, in the range of -pi/2 through pi/2.<br>ATAN( .5 ) = 0.46 |
|- | |- | ||
! ATAN2(a,b) | ! ATAN2(a,b) | ||
− | | YES || YES || Returns the tangent of a/b. < | + | | YES || YES || Returns the tangent of a/b.<br>ATAN2( 1, 2 ) = 0.46 |
|- | |- | ||
! BITAND(a,b) | ! BITAND(a,b) | ||
− | | YES || YES || Returns a & b. < | + | | YES || YES || Returns a & b.<br>BITAND( 0, 1 ) = 0 |
|- | |- | ||
! BITOR(a,b) | ! BITOR(a,b) | ||
− | | YES ||YES || <NOWIKI>Returns a | b </ | + | | YES ||YES || <NOWIKI>Returns a | b</nowiki><br>BITOR( 0,1 ) = 1 |
+ | |- | ||
+ | ! BITXOR(a,b) | ||
+ | | YES || YES || Returns the bit-wise logical <em>xor</em> of the given integer values.<br>BITXOR( 0, 1 ) = 1 | ||
|- | |- | ||
! CEILING(d) | ! CEILING(d) | ||
− | | YES ||YES || | + | | YES ||YES || Returns the smallest integer that is not less than d<br>CEILING( 8.7 ) = 9 |
|- | |- | ||
! COS(d) | ! COS(d) | ||
− | | YES ||YES || Returns the trigonometric cosine of an angle. < | + | | YES ||YES || Returns the trigonometric cosine of an angle.<br>COS( .5 ) = 0.88 |
|- | |- | ||
! COT(d) | ! COT(d) | ||
− | | YES ||YES || | + | | YES ||YES || Returns the cotangent of an angle.<br>COT( .5 ) = 1.83 |
|- | |- | ||
! DEGREES(d) | ! DEGREES(d) | ||
− | | YES ||YES || | + | | YES ||YES || Converts radians to degrees.<br>DEGREES( 1 ) = 57.29577951 |
|- | |- | ||
! EXP(d) | ! EXP(d) | ||
− | | YES ||YES || | + | | YES ||YES || Returns the exponential number e (i.e., 2.718...) raised to the power of a double value.<br>EXP( 2 ) = 7.389056 |
|- | |- | ||
! FLOOR(d) | ! FLOOR(d) | ||
− | | YES ||YES || | + | | YES ||YES || Returns the largest double value that is not greater than the d.<br>FLOOR( -8.7 ) = -9 |
|- | |- | ||
! LOG(d) | ! LOG(d) | ||
− | | | + | | NO ||NO || Returns the natural logarithm (base e) of d.<br>d must be greater than 0.0.<br>LOG( 1.5 ) = 0.41<br>LOG( -1 ) = -1.#NAN<br>SEE <s>{{Bug|69054}}</s> Fixed] |
|- | |- | ||
! LOG10(d) | ! LOG10(d) | ||
− | | YES ||YES || | + | | YES ||YES || Returns the logarithm (base 10) of d.<br>d must be greater than 0.0.<br>LOG10( 1.5 ) = 0.18<br>LOG10( -1 ) = -1.#NAN |
|- | |- | ||
! MOD(a,b) | ! MOD(a,b) | ||
− | | YES ||YES || | + | | YES ||YES ||Returns a modulo b<br>MOD( 7, 2 ) = 1 |
|- | |- | ||
! PI() | ! PI() | ||
− | | YES ||YES || | + | | YES ||YES || The double value that is closer than any other to pi, the ratio of the circumference of a circle to its diameter.<br>PI() = 3.14159265 |
|- | |- | ||
! POWER(a,b) | ! POWER(a,b) | ||
− | | YES ||YES || | + | | YES ||YES || Returns of value of the first argument, a, raised to the power of the second argument, b.<br>POWER( 2, 2 ) = 4 |
|- | |- | ||
! RADIANS(d) | ! RADIANS(d) | ||
− | | YES ||YES || | + | | YES ||YES || Converts degrees to radians<br>RADIANS( 1 ) = 0.01745329 |
|- | |- | ||
! RAND() | ! RAND() | ||
− | | YES ||YES || | + | | YES ||YES || Returns a random number x >= 0.0 and < 1.0<br>RAND() MIGHT EQUAL 0.34 |
|- | |- | ||
! ROUND(a,b) | ! ROUND(a,b) | ||
− | | YES ||YES || | + | | YES ||YES || Rounds a to b digits after the decimal point.<br>ROUND( 1.0333, 2 ) = 1.0300 |
|- | |- | ||
! ROUNDMAGIC(d) | ! ROUNDMAGIC(d) | ||
− | | YES ||YES || | + | | YES'''*''' ||YES'''*''' || solves certain rounding problems, specifically when numbers approach 0.0 such as 3.11-3.1-0.001<br>ROUNDMAGIC( 3.11-3.1-0.001 ) = 0.01<br>'''*''' 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 double.<br>CAST( ROUNDMAGIC( D ) AS DOUBLE )<br>See <s>{{Bug|81031}}</s> [Closed Fixed] |
|- | |- | ||
− | ! SIGN( d ) | + | ! SIGN( d ) |
− | | YES ||YES || | + | | YES ||YES || Returns -1 if d<0, 0 if d=0 and 1 if d>0<br>SIGN( -0.001 ) = -1 |
|- | |- | ||
! SIN(d) | ! SIN(d) | ||
− | | YES ||YES || | + | | YES ||YES || Returns the trigonometric sine of an angle.<br>d is an angle in radians<br>SIN( PI() / 12 ) = 0.25881905 |
|- | |- | ||
− | ! SQRT( d ) | + | ! SQRT( d ) |
− | | YES ||YES || | + | | YES ||YES || Returns the square root of d.<br>SQRT( 9 ) = 3 |
|- | |- | ||
− | ! TAN( A ) | + | ! TAN( A ) |
− | | YES ||YES || | + | | YES ||YES || Returns the trigonometric tangent of an angle, A.<br>A — an angle in radians.<br>TAN( 0.01745329 * 20 ) = 0.36397018 |
|- | |- | ||
! TRUNCATE(a,b) | ! TRUNCATE(a,b) | ||
− | | YES ||YES || | + | | YES ||YES || Truncates a to b digits after the decimal point.<br>TRUNCATE( 1.0333, 3 ) = 1.0330 |
|- | |- | ||
|} | |} | ||
− | == | + | == String Functions == |
− | {|border="1" | + | {|cellpadding="10" border="1" |
− | |+ | + | |+ |
|- | |- | ||
− | + | ! Function Name || Designer || Esc Proc || Comments | |
|- | |- | ||
− | + | !ASCII(s) | |
− | | | + | | YES || YES || Returns the Unicode code value of the leftmost character of<code>s</code> as an <code>int</code>. This is the same as the ASCII value if the string contains only ASCII characters.<br>ASCII( 'ONE' ) = 79 |
|- | |- | ||
− | + | !BIT_LENGTH(str) | |
− | | | + | | YES || YES || Returns the length of the string in bits<br>BIT_LENGTH ( 'ONE' ) = 48<br>'''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.<br>'''Note:''' In some SQL CLI implementations, a <code>null</code> is returned if the range is outside 0...255.<br>In HSQLDB, the corresponding Unicode character is returned unchecked.<br>CHAR( 79 ) = O |
|- | |- | ||
− | + | !CHAR_LENGTH(str) | |
− | | | + | | YES || YES || Returns the length of the string in characters<br>CHAR_LENGTH('ONE') = 3 |
|- | |- | ||
− | | | + | !CONCAT(str1,str2) |
+ | | YES || YES || Returns str1 + str2<br>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.<br>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 hexadecimal 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.<br>'00FF', '00ff00ff' is acceptable ; '0x00FF', 'FF' is NOT and will return null<br>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 <code>length</code> number of characters from the second given <code>String</code> over the first string. The start position in the first string where the characters are overwritten is given by <code>start</code>. | ||
+ | '''Note:''' In order of precedence, boundary conditions are handled as follows: | ||
+ | # If either supplied <code>String</code> is null, then the other is returned; the check starts with the first given <code>String</code>. | ||
+ | # If <code>start</code> is less than one, <code>s1</code> is returned | ||
+ | # If <code>length</code> is less than or equal to zero, <code>s1</code> is returned | ||
+ | # If the length of <code>s2</code> is zero, <code>s1</code> is returned | ||
+ | # If <code>start</code> is greater than the length of <code>s1</code>, <code>s1</code> is returned | ||
+ | # If <code>length</code> is such that, taken together with <code>start</code>, the indicated interval extends beyond the end of <code>s1</code>, then the insertion is performed precisely as if upon a copy of <code>s1</code> extended in length to just include the indicated interval. | ||
− | = | + | INSERT( 'How brown cow.', 5, 1, 'now ' ) = How now brown cow. |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
|- | |- | ||
− | + | !LCASE(s) | |
− | | YES || YES || | + | | YES || YES || Converts s to lower case<br>LCASE( 'ONE' ) = one |
|- | |- | ||
− | + | !LEFT(s,count) | |
− | | YES || YES || | + | | YES || YES || Returns the leftmost count of characters of s<br> |
+ | '''Note:''' Boundary conditions are handled in the following order of precedence: | ||
+ | # If <code>s</code> is <code>null</code>, then <code>null</code> is returned | ||
+ | # If <code>count</code> is less than 1, then a zero-length <code>String</code> is returned | ||
+ | # If <code>count</code> is greater than the length of <code>s</code>, then a copy of <code>s</code> is returned | ||
+ | # - requires double quoting — use SUBSTRING() instead | ||
+ | # LEFT( 'ONE', 2 ) = ON | ||
|- | |- | ||
− | + | !LENGTH(s) | |
− | | YES || YES || | + | | YES || YES || Returns the number of characters in s<br>LENGTH( 'ONE' ) = 3 |
|- | |- | ||
− | + | !LOCATE(search,s,[start]) | |
− | | YES || YES || | + | | YES || YES || Returns the first index (1=left, 0=not found) where search is found in s, starting at start<br>LOCATE( 'ME', 'FIND ME IF YOU CAN', 1 ) = 6 ; LOCATE( 'ME', 'FIND ME IF YOU CAN', 7 ) = 0 |
|- | |- | ||
− | + | !LTRIM(s) | |
− | | YES || YES || | + | | YES || YES || Removes all leading blanks in s<br>LTRIM( ' ONE ' ) = "ONE " |
|- | |- | ||
− | + | !LOWER(s) | |
− | | YES || YES || | + | | YES || YES || Converts s to lower case<br>LOWER( 'ONE' ) = one |
|- | |- | ||
− | + | !OCTET_LENGTH(str) | |
− | | YES || YES || | + | | YES || YES || Returns the length of the string in bytes (twice the number of characters)<br>OCTET_LENGTH( 'ONE' ) = 6 |
|- | |- | ||
− | + | !RAWTOHEX(s1) | |
− | | YES || YES || | + | | YES || YES || Returns translated string, where s1 is any character string and returns the string representation of the characters as hex values<br>RAWTOHEX( 'A' ) = 0041 ; RAWTOHEX( 'Aa' ) = 00410061 |
|- | |- | ||
− | + | !POSITION (<string expression> IN <string expression 1>) | |
− | | YES || YES || | + | | YES || YES || If the first string is a substring of the second one, returns the position of the substring, counting from one; otherwise 0<br>POSITION ( 'THIS' IN 'THIS AND THAT' ) = 1 |
|- | |- | ||
− | + | !REPEAT(s,count) | |
− | | YES || YES || | + | | YES || YES || Returns s repeated count times<br>REPEAT( 'X', 4 ) = XXXX |
|- | |- | ||
− | + | !REPLACE(s,replace,s2) | |
− | | YES || YES || | + | | YES || YES || Replaces all occurrences of replace in s with s2<br>REPLACE( 'WHAT XXXX BROWN COW.', 'XXXX', 'NOW' ) = WHAT NOW BROWN COW. |
|- | |- | ||
− | + | !RIGHT(s,count) | |
− | | YES || YES || | + | | YES || YES || Returns the rightmost count of characters of s |
+ | <br>'''Note:'''Boundary conditions are handled in the following order of precedence:<br>if <code>s</code> is <code>null</code>, <code>null</code> is returned<br>if <code>count</code> is less than one, a zero-length <code>String</code> is returned<br>if <code>count</code> is greater than the length of <code>s</code>, a copy of <code>s</code> is returned<br>RIGHT( 'THIS AND THAT', 4 ) = THAT | ||
|- | |- | ||
− | + | !RTRIM(s) | |
− | | YES || YES || | + | | YES || YES || Removes all trailing spaces<br>RTRIM( ' ONE ' ) = " ONE" |
|- | |- | ||
− | + | !SOUNDEX(s) | |
− | | YES || YES || | + | | YES || YES || Returns a four-character code representing the sound of s.<br>Non-ASCII characters in the input <code>String</code> are ignored.<br>SOUNDEX( 'SMITH' ) = S530 ; SOUNDEX( 'SMYTH' ) = S530 |
|- | |- | ||
− | + | !SPACE(count) | |
− | | YES || YES || | + | | YES || YES || Returns a string consisting of ''count'' spaces<br>SPACE( 4 ) = " " |
|- | |- | ||
− | + | !SUBSTR(s,start[,len])<br>SUBSTRING(s,start[,len]) | |
− | | YES || YES || | + | | YES || YES || Returns the substring starting at start (1=left) with length len<br>'''Note:''' The rules for boundary conditions on s, start and length are,in order of precedence: |
+ | # If s is null, return null | ||
+ | # If length is less than 1, return null. | ||
+ | # If start is 0, it is treated as 1. | ||
+ | # If start is positive, count from the beginning of s to find the first character position. | ||
+ | # If start is negative, count backwards from the end of s to find the first character. | ||
+ | # If, after applying #2 or #3, the start position lies outside s, then return null | ||
+ | # If length is omitted or is greater than the number of characters from the start position to the end of s, return the remainder of s, starting with the start position.<br>SUBSTR( 'HERE I AM', 6, 1 ) = I ; SUBSTR( 'HERE I AM', 6 ) = I AM | ||
|- | |- | ||
− | + | !SUBSTRING( s FROM start <nowiki>[FOR len]</nowiki>) | |
− | | YES || YES || | + | | YES || YES ||Alternate syntax where s may be a string expression<br>SUBSTRING ( 'HERE I AM' FROM 6 FOR 1 ) = I ; SUBSTRING ( 'HERE I AM' FROM 6 ) = I AM |
|- | |- | ||
− | + | !TRIM( <nowiki>[[ LEADING | TRAILING | BOTH ] [TRIMSTR]]</nowiki> FROM s ) | |
− | | YES || YES || | + | | YES'''*''' || YES'''*''' || Returns the character sequence <code>s</code>, with the leading, trailing or both the leading and trailing occurrences of the first character of the character sequence <code>trimstr</code> removed.<br>If trimstr is not supplied <code>SPACE</code> is used.<br>TRIM( BOTH FROM ' ONE ' ) = "ONE" ; TRIM ( BOTH 'O' FROM 'OONEOO' ) = NE<br>'''* 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 || | + | | YES || YES || Converts s to upper case<br>UCASE( 'one' ) = ONE |
|- | |- | ||
− | + | !UPPER(s) | |
− | | YES || YES || | + | | YES || YES || Converts s to upper case<br>UPPER( 'one' ) = ONE |
|- | |- | ||
− | | | + | |} |
− | | | + | |
+ | == Date and Time Functions == | ||
+ | {|cellpadding="10" border="1" | ||
+ | |+ | ||
|- | |- | ||
− | | | + | ! Function Name || Designer || Esc Proc || Comments |
− | | | + | |
|- | |- | ||
− | + | !CURDATE() | |
− | | YES || YES || | + | | YES || YES || Returns the current date. This is the current system date on your PC.<br>CURDATE() = 09/01/07<br>'''Note:''' The parentheses are required. |
|- | |- | ||
− | + | !CURTIME() | |
− | | YES || YES || | + | | YES || YES || Returns the current time. This is the current system time on your PC.<br>CURTIME() = 10:16:04 PM<br>'''Note:''' The parentheses are required. |
|- | |- | ||
− | + | !DATEDIFF(string, datetime1, datetime2) | |
− | | YES || YES || | + | | 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.<br>DATEDIFF('dd', '2007-08-01', '2007-09-01' ) = 31 |
|- | |- | ||
− | + | !DAYNAME(date) | |
− | | YES || YES || | + | | YES || YES || Returns the name of the day of the week.<br>DAYNAME( '2007-09-01' ) = Saturday |
|- | |- | ||
− | + | !DAYOFMONTH(date) | |
− | | YES || YES || | + | | YES || YES || Returns the day of the month (1–31)<br>DAYOFMONTH( '2007-09-01' ) = 1 |
|- | |- | ||
− | + | !DAYOFWEEK(date) | |
− | | YES || YES || | + | | YES || YES || Returns the day of the week (1 means Sunday)<br>DAYOFWEEK( '2007-09-01' ) = 7 |
|- | |- | ||
− | | | + | !DAYOFYEAR(date) |
− | | YES || YES || | + | | YES || YES || Returns the day of the year (1–366)<br>DAYOFYEAR( '2007-09-01' ) = 244 |
+ | |- | ||
+ | !HOUR(time) | ||
+ | | YES || YES || Return the hour (0–23)<br>HOUR( '21:16:04' ) | ||
+ | |- | ||
+ | !MINUTE(time) | ||
+ | | YES || YES || Returns the minute (0–59)<br>MINUTE( '21:16:04' ) = 16 | ||
+ | |- | ||
+ | !MONTH(date) | ||
+ | | YES || YES || Returns the month (1–12)<br>MONTH( '2007-09-01' ) = 9 | ||
+ | |- | ||
+ | !MONTHNAME(date) | ||
+ | | YES || YES || Returns the name of the month,<br>MONTHNAME( '2007-09-01' ) = September | ||
+ | |- | ||
+ | !NOW() | ||
+ | | YES || YES || Returns the current date and time as a time stamp)<br>use of CURRENT_TIMESTAMP instead is suggested<br>NOW() = 09/01/07 10:34 PM | ||
+ | |- | ||
+ | !QUARTER(date) | ||
+ | | YES || YES || Returns the quarter (1–4), with the new year starting in January<br>QUARTER( '2007-09-01' ) = 3 | ||
+ | |- | ||
+ | !SECOND(time) | ||
+ | | YES || YES || Returns the second (0–59)<br>SECOND( CURRENT_TIME ) MAY EQUAL 6 | ||
+ | |- | ||
+ | !WEEK(date) | ||
+ | | YES || YES || Returns the week of this year (1–53)<br>WEEK( '2007-09-01' ) = 35 | ||
+ | |- | ||
+ | !YEAR(date) | ||
+ | | YES || YES || Returns the year<br>YEAR( '2007-09-01' ) = 2007 | ||
+ | |- | ||
+ | !CURRENT_DATE | ||
+ | | YES || YES || Returns the current date<br>CURRENT_DATE = 09/01/07 | ||
+ | |- | ||
+ | !CURRENT_TIME | ||
+ | | YES || YES || Returns the current time<br>CURRENT_TIME = 10:44:28 PM | ||
+ | |- | ||
+ | !CURRENT_TIMESTAMP | ||
+ | | YES || YES || Returns the current time stamp<br>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<br>Following is a list of valid format mask character sequences: | ||
+ | {|cellpadding="5" border="0" | ||
+ | |+ | ||
+ | |- | ||
+ | ! Chr seq || returned value || example | ||
+ | |- | ||
+ | | YYYY || 4 digit year || TO_CHAR( CURRENT_TIMESTAMP, 'YYYY' ) = 2007 | ||
+ | |- | ||
+ | | YYY<br>YY<br>Y || Last 3, 2 or 1 digits of year || TO_CHAR( CURRENT_TIMESTAMP, 'YY' ) = 07 | ||
+ | |- | ||
+ | | IYYY<br>IYY<br>IY || Last 4, 3, 2 digits of ISO year || TO_CHAR( CURRENT_TIMESTAMP, 'IY' ) = 07 | ||
+ | |- | ||
+ | | MM || Month (01–12; JAN = 01) || TO_CHAR( CURRENT_TIMESTAMP, 'MM' ) = 09 | ||
+ | |- | ||
+ | | MON || Abbreviated name of month || TO_CHAR( CURRENT_TIMESTAMP, 'MON' ) = Sep | ||
+ | |- | ||
+ | | MONTH || Name of month, padded with blanks to length of 9 characters. || TO_CHAR( CURRENT_TIMESTAMP, 'MONTH' ) = September | ||
+ | |- | ||
+ | | w || Week of year || TO_CHAR( CURRENT_DATE, 'w' ) = 35 | ||
+ | |- | ||
+ | | W || Week of month || TO_CHAR( CURRENT_DATE, 'W' ) = 1 | ||
+ | |- | ||
+ | | IW || Week of year (1–52 or 1–53) based on the ISO standard. || TO_CHAR( CURRENT_DATE, 'IW' ) = 35 | ||
+ | |- | ||
+ | | d || Day of week (1–7) || TO_CHAR( CURRENT_DATE, 'd' ) = 1 | ||
+ | |- | ||
+ | | D || Abbreviation for day of week || TO_CHAR( CURRENT_DATE, 'D' ) = Sat | ||
+ | |- | ||
+ | | DD<br>dd || Day of month as 2 digits || TO_CHAR( CURRENT_DATE, 'DD' ) = 01 | ||
+ | |- | ||
+ | | DDD || Day of year || TO_CHAR( CURRENT_DATE, 'DDD' ) = 244 | ||
+ | |- | ||
+ | | H || Hour of day 0–23 || TO_CHAR( CURRENT_TIME, 'H' ) = 23 | ||
+ | |- | ||
+ | | HH || Hour of day 0–11 || TO_CHAR( CURRENT_TIME, 'HH' ) = 11 | ||
+ | |- | ||
+ | | m || Minute of current hour || TO_CHAR( CURRENT_TIME, 'm' ) = 48 | ||
+ | |- | ||
+ | | s || Seconds of current minute || TO_CHAR( CURRENT_TIME, 's' ) = 33 | ||
+ | |- | ||
+ | | a || AM or PM ||TO_CHAR( CURRENT_TIME, 'a' ) = PM | ||
+ | |- | ||
+ | | All other characters || LITERALS || TO_CHAR( CURRENT_DATE, 'D - MON, dd YYYY' ) = Sat - Sep, 01 2007 | ||
+ | |- | ||
+ | |} | ||
|- | |- | ||
|} | |} | ||
+ | == System Functions == | ||
+ | {|cellpadding="10" border="1" | ||
+ | |+ | ||
+ | |- | ||
+ | ! Function Name || Designer || Esc Proc || Comments | ||
+ | |- | ||
+ | ! DATABASE() | ||
+ | | YES || YES || Returns the fully qualified file name of the Base file<br>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<br>'''Note:''' This command '''requires''' use of parentheses.<br>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().<br>'''Note:''' Use of parentheses is '''not''' allowed with this macro.<br>CURRENT_USER = SA | ||
+ | |- | ||
+ | ! IDENTITY() | ||
+ | | YES || YES || Returns the last identity value that was inserted by this connection.<br> | ||
+ | |- | ||
+ | |} | ||
− | == | + | == Aggregate Functions == |
− | {|border="1" | + | 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. | ||
+ | |||
+ | {|cellpadding="10" border="1" | ||
+ | |+ | ||
|- | |- | ||
− | + | ! Function Name || Designer || Esc Proc || Comments | |
|- | |- | ||
− | + | ! COUNT( <nowiki>Any | *</nowiki> ) | |
− | | YES || YES || | + | | YES || YES || Returns the count of not null entries in a column or when used with * the count of records.<br> |
|- | |- | ||
− | + | ! MIN(d) | |
− | | YES || YES || | + | | YES || YES || Returns the smallest value in a group. |
|- | |- | ||
− | + | ! MAX(d) | |
− | | YES || YES || | + | | YES || YES || Returns the largest value in a group. |
|- | |- | ||
− | + | ! SUM(d) | |
− | | YES || YES || | + | | YES || YES || Adds all values in a group together. |
|- | |- | ||
− | + | ! AVG(d) | |
− | | YES || YES || | + | | 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 || | + | | YES || YES || Returns TRUE only if '''all''' values in a group = TRUE. |
|- | |- | ||
− | | | + | |} |
− | | | + | |
+ | === Statistical Functions === | ||
+ | {|cellpadding="10" border="1" | ||
+ | |+ | ||
|- | |- | ||
− | | | + | ! Function Name || Designer || 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 return 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. | ||
+ | |||
+ | <em> Example Data </em><br> | ||
+ | {| class="prettytable" | ||
+ | |+ | ||
|- | |- | ||
− | | | + | ! TaskID || StartDate || EndDate || Description |
− | | | + | |
|- | |- | ||
− | | | + | ! 2 || 01/01/07 || 01/01/07 || Task1 |
− | | | + | |
|- | |- | ||
− | | | + | ! 3 || 01/01/07 || 01/02/07 || Task2 |
− | | | + | |
|- | |- | ||
− | | | + | ! 4 || 01/02/07 || 01/02/07 || Task3 |
− | | | + | |
|- | |- | ||
− | | | + | ! 5 || 01/02/07 || 01/03/07 || Task4 |
− | | | + | |
|- | |- | ||
− | | | + | ! 6 || 01/03/07 || 01/03/07 || Task5 |
− | | | + | |- |
+ | ! 7 || 01/04/07 || 01/04/07 || Task6 | ||
+ | |- | ||
+ | ! 8 || 02/01/07 || 02/10/07 || Task7 | ||
+ | |- | ||
+ | ! 9 || 02/11/07 || 02/11/07 || Task8 | ||
+ | |- | ||
+ | ! 10 || 03/01/08 || 03/02/07 || Task9 | ||
+ | |- | ||
+ | ! 11 || 03/02/08 || 03/03/07 || Task10 | ||
|- | |- | ||
|} | |} | ||
+ | === Limit === | ||
+ | The modifier LIMIT can be used in either of two ways: | ||
+ | *Just after the word SELECT — When used, one must supply two parameters, "Starting Record" and "Number of rows", separated by a space. | ||
+ | *At the end of a select statement, IF the statement contains a HAVING, GROUP BY or ORDER BY clause. Here one may supply one or two parameters. The "number of rows" and optionally the "Starting Record" is designated by the word OFFSET. | ||
− | + | Examples: | |
− | {| | + | |
− | |+ | + | <code>SELECT LIMIT 3 2 "TaskID", "StartDate", "EndDate", "Description" FROM "Tasks"</code> |
+ | This can be read as "return 2 rows, skipping the first 3 records in the result set". | ||
+ | |||
+ | The returned result set would be: | ||
+ | {| class="prettytable" | ||
+ | |+ | ||
|- | |- | ||
− | | | + | ! TaskID || StartDate || EndDate || Description |
|- | |- | ||
− | | | + | ! 5 || 01/02/07 || 01/03/07 || Task4 |
− | | | + | |
|- | |- | ||
− | | | + | ! 6 || 01/03/07 || 01/03/07 || Task5 |
− | | | + | |
|- | |- | ||
− | | | + | |} |
− | | | + | |
+ | <code>SELECT "TaskID", "StartDate", "EndDate", "Description" FROM "Tasks" ORDER BY "StartDate" DESC LIMIT 2 OFFSET 3</code> | ||
+ | This example can be also be read as "return 2 rows, skipping the first 3 records of the result set after sorting by StartDate in descending order". | ||
+ | |||
+ | The returned result set would be: | ||
+ | {| class="prettytable" | ||
+ | |+ | ||
|- | |- | ||
− | | | + | ! TaskID || StartDate || EndDate || Description |
− | | | + | |- |
+ | ! 8 || 02/01/07 || 02/10/07 || Task7 | ||
+ | |- | ||
+ | ! 7 || 01/04/07 || 01/04/07 || Task6 | ||
|- | |- | ||
|} | |} | ||
+ | === Top === | ||
+ | The modifier TOP is used to limit the number of records returned without an offset value. It must be included directly after the word SELECT and maybe used with any select statement. | ||
+ | |||
+ | <code>SELECT TOP 2 "TaskID", "StartDate", "EndDate", "Description" FROM "Tasks" ORDER BY "StartDate" DESC </code> | ||
+ | |||
+ | The returned result set would be: | ||
+ | {| class="prettytable" | ||
+ | |+ | ||
+ | |- | ||
+ | ! TaskID || StartDate || EndDate || Description | ||
+ | |- | ||
+ | ! 11 || 03/02/08 || 03/03/07 || Task10 | ||
+ | |- | ||
+ | ! 10 || 03/01/08 || 03/02/07 || Task9 | ||
+ | |- | ||
+ | |} | ||
+ | |||
+ | <code>SELECT TOP 2 "TaskID", "StartDate", "EndDate", "Description" FROM "Tasks"</code> | ||
+ | |||
+ | The returned result set would be: | ||
+ | {| class="prettytable" | ||
+ | |+ | ||
+ | |- | ||
+ | ! TaskID || StartDate || EndDate || Description | ||
+ | |- | ||
+ | ! 2 || 01/01/07 || 01/01/07 || Task1 | ||
+ | |- | ||
+ | ! 3 || 01/01/07 || 01/02/07 || Task2 | ||
+ | |- | ||
+ | |} | ||
+ | |||
+ | === Distinct === | ||
+ | The modifier DISTINCT limits the returned to rows to only those for which <em>every</em> row contains unique entries. | ||
+ | |||
+ | ''Author: [[User:DrewJensen|Drew]] 17:47, 11 September 2007 (CEST)''<br> | ||
+ | ''Please do not change the logical content of this site without | ||
+ | acknowledge of the author or the [http://wiki.services.openoffice.org/wiki/Project_Lead OOo QA Project Lead/Co-Leads].'' | ||
− | [[Category: | + | [[Category:HSQLDB]] |
[[Category:Base Documentation]] | [[Category:Base Documentation]] | ||
+ | [[Category:Quality Assurance]] |
Latest revision as of 11:46, 9 August 2022
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 | Designer | 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 bit-wise logical xor of the given integer values. BITXOR( 0, 1 ) = 1 |
CEILING(d) | YES | YES | Returns 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 |
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.11-3.1-0.001 ROUNDMAGIC( 3.11-3.1-0.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 double. CAST( ROUNDMAGIC( D ) AS DOUBLE ) See |
SIGN( d ) | YES | YES | Returns -1 if d<0, 0 if d=0 and 1 if d>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. SQRT( 9 ) = 3 |
TAN( A ) | YES | YES | Returns the trigonometric tangent of an angle, A. A — an angle 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 | Designer | 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 hexadecimal 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, boundary conditions are handled as follows:
INSERT( 'How brown cow.', 5, 1, 'now ' ) = How now brown cow. |
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: Boundary 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. Non-ASCII 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:
|
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 occurrences 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 | Designer | 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 parentheses 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 parentheses 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', '2007-08-01', '2007-09-01' ) = 31 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
DAYNAME(date) | YES | YES | Returns the name of the day of the week. DAYNAME( '2007-09-01' ) = Saturday | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
DAYOFMONTH(date) | YES | YES | Returns the day of the month (1–31) DAYOFMONTH( '2007-09-01' ) = 1 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
DAYOFWEEK(date) | YES | YES | Returns the day of the week (1 means Sunday) DAYOFWEEK( '2007-09-01' ) = 7 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
DAYOFYEAR(date) | YES | YES | Returns the day of the year (1–366) DAYOFYEAR( '2007-09-01' ) = 244 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
HOUR(time) | YES | YES | Return the hour (0–23) HOUR( '21:16:04' ) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
MINUTE(time) | YES | YES | Returns the minute (0–59) MINUTE( '21:16:04' ) = 16 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
MONTH(date) | YES | YES | Returns the month (1–12) MONTH( '2007-09-01' ) = 9 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
MONTHNAME(date) | YES | YES | Returns the name of the month, MONTHNAME( '2007-09-01' ) = September | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
NOW() | YES | YES | Returns the current date and time as a time stamp) use of CURRENT_TIMESTAMP instead is suggested NOW() = 09/01/07 10:34 PM | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
QUARTER(date) | YES | YES | Returns the quarter (1–4), with the new year starting in January QUARTER( '2007-09-01' ) = 3 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
SECOND(time) | YES | YES | Returns the second (0–59) SECOND( CURRENT_TIME ) MAY EQUAL 6 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
WEEK(date) | YES | YES | Returns the week of this year (1–53) WEEK( '2007-09-01' ) = 35 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
YEAR(date) | YES | YES | Returns the year YEAR( '2007-09-01' ) = 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 time stamp 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 | Designer | 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 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 | Designer | 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 | Designer | 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 return 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.
Example Data
TaskID | StartDate | EndDate | Description |
---|---|---|---|
2 | 01/01/07 | 01/01/07 | Task1 |
3 | 01/01/07 | 01/02/07 | Task2 |
4 | 01/02/07 | 01/02/07 | Task3 |
5 | 01/02/07 | 01/03/07 | Task4 |
6 | 01/03/07 | 01/03/07 | Task5 |
7 | 01/04/07 | 01/04/07 | Task6 |
8 | 02/01/07 | 02/10/07 | Task7 |
9 | 02/11/07 | 02/11/07 | Task8 |
10 | 03/01/08 | 03/02/07 | Task9 |
11 | 03/02/08 | 03/03/07 | Task10 |
Limit
The modifier LIMIT can be used in either of two ways:
- Just after the word SELECT — When used, one must supply two parameters, "Starting Record" and "Number of rows", separated by a space.
- At the end of a select statement, IF the statement contains a HAVING, GROUP BY or ORDER BY clause. Here one may supply one or two parameters. The "number of rows" and optionally the "Starting Record" is designated by the word OFFSET.
Examples:
SELECT LIMIT 3 2 "TaskID", "StartDate", "EndDate", "Description" FROM "Tasks"
This can be read as "return 2 rows, skipping the first 3 records in the result set".
The returned result set would be:
TaskID | StartDate | EndDate | Description |
---|---|---|---|
5 | 01/02/07 | 01/03/07 | Task4 |
6 | 01/03/07 | 01/03/07 | Task5 |
SELECT "TaskID", "StartDate", "EndDate", "Description" FROM "Tasks" ORDER BY "StartDate" DESC LIMIT 2 OFFSET 3
This example can be also be read as "return 2 rows, skipping the first 3 records of the result set after sorting by StartDate in descending order".
The returned result set would be:
TaskID | StartDate | EndDate | Description |
---|---|---|---|
8 | 02/01/07 | 02/10/07 | Task7 |
7 | 01/04/07 | 01/04/07 | Task6 |
Top
The modifier TOP is used to limit the number of records returned without an offset value. It must be included directly after the word SELECT and maybe used with any select statement.
SELECT TOP 2 "TaskID", "StartDate", "EndDate", "Description" FROM "Tasks" ORDER BY "StartDate" DESC
The returned result set would be:
TaskID | StartDate | EndDate | Description |
---|---|---|---|
11 | 03/02/08 | 03/03/07 | Task10 |
10 | 03/01/08 | 03/02/07 | Task9 |
SELECT TOP 2 "TaskID", "StartDate", "EndDate", "Description" FROM "Tasks"
The returned result set would be:
TaskID | StartDate | EndDate | Description |
---|---|---|---|
2 | 01/01/07 | 01/01/07 | Task1 |
3 | 01/01/07 | 01/02/07 | Task2 |
Distinct
The modifier DISTINCT limits the returned to rows to only those for which every row contains unique entries.
Author: 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/Co-Leads.