Frequently asked questions - Base

    From The Document Foundation Wiki
    < Faq‎ | Base

    TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

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

    1. If either supplied String is null, then the other is returned; the check starts with the first given String.
    2. If start is less than one, s1 is returned
    3. If length is less than or equal to zero, s1 is returned
    4. If the length of s2 is zero, s1 is returned
    5. If start is greater than the length of s1, s1 is returned
    6. If length is such that, taken together with start, the indicated interval extends beyond the end of s1, then the insertion is performed precisely as if upon a copy of s1 extended in length to just include the indicated interval.

    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:

    1. If s is null, then null is returned
    2. If count is less than 1, then a zero-length String is returned
    3. If count is greater than the length of s, then a copy of s is returned
    4. - requires double quoting — use SUBSTRING() instead
    5. LEFT( 'ONE', 2 ) = ON
    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


    Note:Boundary conditions are handled in the following order of precedence:
    if s is null, null is returned
    if count is less than one, a zero-length String is returned
    if count is greater than the length of s, a copy of s is returned
    RIGHT( 'THIS AND THAT', 4 ) = THAT

    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:
    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 position.
    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 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.
      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 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:
    Chr seq returned value example
    YYYY 4 digit year TO_CHAR( CURRENT_TIMESTAMP, 'YYYY' ) = 2007
    YYY
    YY
    Y    		 Last 3, 2 or 1 digits of year TO_CHAR( CURRENT_TIMESTAMP, 'YY' ) = 07
    IYYY
    IYY
    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
    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

    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 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.

    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.

    Retrieved from "https://wiki.documentfoundation.org/index.php?title=Faq/Base/HSQLFunctions&oldid=407768"