2.16.1 System built-in scalar functions
(1) ABS
(a) Function
The ABS scalar function returns the absolute value of a value expression.
(b) Format
ABS(value-expression)
(c) Rules
- The following items can be specified as the value expression:
- Numeric literals
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Arithmetic operations
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The data type of a value expression must be numeric data, date interval data, or time interval data.
- The data type of the result will be the same as the data type of the value expression.
- The NOT NULL constraint does not apply to the result value (the null value is allowed). If the value expression is the null value, the result will also be the null value.
- The result must be a value that can be expressed as the absolute value of the value expression. If a value that cannot be expressed as the absolute value is specified, an overflow error occurs (for the result when overflow error suppression is set, see 2.18 Operational results with overflow error suppression specified).
(2) BIT_AND_TEST
(a) Function
Determines a bit-by-bit logical product of value-expression-1 and value-expression-2, and returns the BOOLEAN value TRUE if any of the bits in the results of the logical product is 1.
(b) Format
BIT_AND_TEST(value-expression-1, value-expression-2) |
(c) Rules
- The following items can be specified in value-expression-1 and value-expression-2:
- Literals
- USER
- Column specification
- Component specification
- SQL variables or SQL parameters
- Concatenation operations
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Embedded variables or ? parameters
- Function calls
- Scalar subquery
- The data type of value-expression-1 and value-expression-2 should be either a character data type (CHAR or VARCHAR) or BINARY with a maximum length of 32,000 bytes. Table 2-24 shows combinations of data types that can be specified in value-expression-1and value-expression-2:
Table 2-24 Combinations of data types that can be specified in value-expression-1 and value-expression-2 (system built-in scalar function BIT_AND_TEST)
Data type of value expression 1 | Data type of value expression 2 |
---|
Character data (CHAR and VARCHAR) | Binary data (BINARY) |
---|
Character data (CHAR and VARCHAR) | Y | N* |
Binary data (BINARY) | N* | Y |
- Legend:
- Y: Can be specified.
- N: Cannot be specified.
- * Only hexadecimal character string literals can be specified as a character data value expression.
- Value expressions consisting solely of embedded variables or ? parameters cannot be specified in both value-expression-1and value-expression-2.
- If one value expression is an embedded variable or a ? parameter, HiRDB assumes that the data type of the embedded variable or ? parameter is VARCHAR, provided that the data type of the other value expression is character data, and BINARY if the data type of the other value expression is binary data. Similarly, HiRDB assumes that the data length of the embedded variable or ? parameter is equal to the data length of the other value expression.
- If value-expression-1and value-expression-2 have different data lengths, HiRDB fills the shorter data with X'00' on the right and determines a bit-by-bit logical product after making the two value expressions equal in character string length.
- The data type of the result is the BOOLEAN type.
- The value of the result is not NOT NULL constrained (the null value is allowed). If either value-expression-1or value-expression-2 is the null value, the result also is the null value.
- Determines a bit-by-bit logical product of value-expression-1 and value-expression-2, and the result is TRUE if any of the bits in the results of the logical product is 1; it is FALSE otherwise.
- If both value-expression-1and value-expression-2 are character strings of a length 0, the result is FALSE.
(d) Notes
The BIT_AND_TEST scalar function can be specified in the following locations:
- Value expressions in a logical predicate in a search condition
- RETURN statement in CREATE FUNCTION for which the data type of the return value is the BOOLEAN type
(e) Example
Performs a test to determine if C1 in a column of table T1 (data type: VARCHAR(2)) contains bits.
SELECT * FROM T1
WHERE BIT_AND_TEST(C1,X'FFFF') IS TRUE
(3) CHARACTER
(a) Function
Converts date data, time data, or time stamp data into a character string representation.
(b) Format
CHAR[ACTER](value-expression)
(c) Rules
- The following items can be specified as the value expression:
- CURRENT_DATE
- CURRENT_TIME
- CURRENT_TIMESTAMP[(p)]
- Columns of the date data type, time data type, or time stamp data type
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Date operations that produce date data type results
- Time operations that produce time data type results
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The data type of value-expression should be the date data type (DATE), time data type (TIME), or time stamp data type (TIMESTAMP).
- The data types of the result are as follows:
- If value-expression is the date data type:
- CHAR(10)
- If value-expression is the time data type:
- CHAR(8)
- If value-expression is the time stamp data type:
- CHAR(19), CHAR(22), CHAR(24), or CHAR(26)
- The value of the result is a predefined character string representation of the data type of value-expression.
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the source value expression is the null value, the result is also the null value.
(d) Examples
- Update any dates older than 6 months in column C1 of table T1 (CHAR data type) to the date 02-06-1995:
UPDATE T1
SET C1=CHAR(CURRENT_DATE)
WHERE CHAR(CURRENT_DATE - 6 MONTHS) > C1
![[Figure]](figure/zu2s0180.gif)
- Update the time 0:0:0 in column C1 of table T2 (CHAR data type) to the time 14:24:45:
UPDATE T2
SET C1=CHAR(CURRENT_TIME)
WHERE C1='00:00:00'
![[Figure]](figure/zu2s0190.gif)
(4) DATE
(a) Function
- The DATE scalar function performs the following conversions:
- Converts the character string representation of a date in a specified format into date data.
- Converts the cumulative number of days since January 1, 1 (Gregorian calendar) into equivalent date data.
(b) Format
- Format of function 1
DATE(value-expression[,datetime-format])
- Format of function 2
DATE(value-expression)
(c) Rules for function 1
- The following items can be specified as the value expression:
- Literals that are character string expressions of dates
- CURRENT_DATE
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Date operations that produce date data type results
- Concatenation operation
- Set functions (MAX, MIN)
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The value expression should be one of the following data types:
- A datetime format is specified:
Character data type (CHAR, VARCHAR) or mixed character data type (MCHAR, MVARCHAR) with a definition length of 8 to 255 bytes
- A datetime format is not specified:
Character data type (CHAR, VARCHAR) or date data type (DATE) with a definition length of 10 bytes
- The value expression should be the character string representation of a date in a format specified in a datetime format. If a datetime format is omitted, the value expression should be the predefined character string representation of a date.
Examples:
Datetime format 'YYYY/MM/DD'
'1995/06/30'
Datetime format omitted
'1995-06-30'
- If the value expression is of the date data type, the result will be the equivalent date.
- For datetime formats, see 1.10 Specifying a datetime format.
(d) Rules for function 2
- The following items can be specified as the value expression:
- Numeric literals
- Column specifications
- Arithmetic operations
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The data type of the value expression should be an integer (INTEGER).
If an arithmetic operation, set function, or CASE expression is specified, the result of the operation should be the integer data type.
- The allowable range of values is 1 to 3652059.
- The result is the date (specified numeric value - 1) from January 1, 1 (Gregorian calendar).
Example: If the value expression is 35, then the date is February 4, 1 (Gregorian calendar).
(e) Common rules
- The data type of the result is the date data type (DATE).
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression or the datetime format is the null value, the result also is the null value.
(f) Example
- The following uses the DATE scalar function to perform the same processing as the example in the section on the CHARACTER scalar function:
UPDATE T1
SET C1=CHAR(CURRENT DATE)
WHERE CURRENT DATE - 6 MONTHS > DATE(C1)
- Obtains date data from the character string, in a format other than a predefined character string representation ('DD/MON/YYYY') of a date in column C1 (data type: CHAR) in table T2:
SELECT DATE(C1,'DD/MON/YYYY') FROM T2
![[Figure]](figure/zu2s0510.gif)
(5) DAY
(a) Function
Extracts the day part from date data, time stamp data, or date interval data.
(b) Format
DAY (value-expression)
(c) Rules
- The following items can be specified as the value expression:
- CURRENT_DATE
- CURRENT_TIMESTAMP[(p)]
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Date operations
- Set functions (MAX, MIN)
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The data type of the value expression should be either the date data type (DATE), time stamp data type (TIMESTAMP), or the date interval data type (INTERVAL YEAR TO DAY).
- The data type of the result is integer (INTEGER).
- If the value expression is of the date data type or time stamp data type, the results will be in the range 1 to 31.
- If the value expression is of the date interval data type, the result will be in the range -99 to 99.
If the result is non-zero, the result has the same sign as the value expression.
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the source value expression is the null value, the result is also the null value.
(d) Example
Retrieve all rows with the current date from the rows in column C1 (date data type data type) of table T1:
SELECT * FROM T1
WHERE DAY(C1)=DAY(CURRENT_DATE)
(6) DAYS
(a) Function
Converts date data or time stamp data into a cumulative number of days since January 1, year 1 (A.D.).
(b) Format
DAYS (value-expression)
(c) Rules
- The following items can be specified as the value expression:
- Predefined character string representation literals of a date
- CURRENT_DATE
- CURRENT_TIMESTAMP[(p)]
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Date operations that produce date data type results
- Concatenation operations producing a result that is the predefined character string representation literal of a date
- Set functions (MAX, MIN)
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The data type of the value expression should be the date data type (DATE) or time stamp data type (TIMESTAMP).
- The data type of the result should be an integer (INTEGER).
- The result of executing the DAYS scalar function on a specified date is the cumulative number of days, including the specified date, since January 1, 1 (Gregorian calendar).
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
(d) Example
Determine the number of days through the current date (06-30-1995) since the value in column C1 of table T1:
SELECT DAYS(CURRENT_DATE) - DAYS(C1)
FROM T1
(7) DECIMAL
(a) Function
The DECIMAL scalar function converts numeric data into decimal data.
(b) Format
DEC[IMAL] (value-expression [, precision [, decimal-scaling-position]])
(c) Rules
- The following items can be specified as the value expression:
- Numeric literals
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Arithmetic operations
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The following items can be specified as the data type of value-expression:
- The precision should be an integer in the range 1 to 29.
The default precision depends on the data type of the specified value expression, as shown in Table 2-25.
Table 2-25 Default precisions of the DECIMAL scalar function
Data Type | Precision (number of digit positions) |
---|
INTEGER | 10 |
SMALLINT | 5 |
DECIMAL | 15 |
FLOAT | 29 |
SMALLFLT |
- Scaling is specified in the range of values from 0 to the specified precision. The scaling should either be an integer or a character string representation of an integer. The default scaling is 0.
- Table 2-26 shows the data type of the result.
Table 2-26 Data type of the result of the DECIMAL scalar function
Data Type | Precision of result |
---|
DECIMAL | Up to 29 digits |
- The integer part of the value expression should be expressed by a value that is within the specified precision and decimal scaling position. If the integer part exceeds the specified precision, an overflow error results.
- Any digits following the specified decimal scaling position in the result are rounded off.
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
(d) Example
Obtain the result as decimal data, DEC(29,19), of dividing column C1 (DECIMAL(10,0) data type) by column C2 (INTEGER data type) in Table T1; delete extraneous digits to make the result DEC(4,2) (the result is converted with the DECIMAL scalar function):
SELECT DECIMAL(C1/C2,4,2)
FROM T1
![[Figure]](figure/zu2s0200.gif)
(8) DIGITS
(a) Function
The DIGITS scalar function extracts the digits part of an integer, decimal number, date interval data, or time interval data and converts it into a character string expression.
(b) Format
DIGITS (value-expression)
(c) Rules
- The following items can be specified as the value expression:
- Integer or decimal literals
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Arithmetic operations
- Date operations that produce results that are of the date interval data type
- Time operations that produce results that are of the time interval data type
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The data type of the value expression should be one of the following:
- Integer (INTEGER, SMALLINT)
- Fixed-point number (DECIMAL)
- Date interval data type (INTERVAL YEAR TO DAY)
- Time interval data type (INTERVAL HOUR TO SECOND)
- The data type of the result is a fixed-length character string (CHAR).
- The data length of the result depends on the data type of the value expression, as shown in Table 2-27.
Table 2-27 Data lengths of the result of the DIGITS scalar function
Data type | Data length of result |
---|
INTEGER | 10 |
SMALLINT | 5 |
DECIMAL(p,s) | p |
INTERVAL YEAR TO DAY | 8 |
INTERVAL HOUR TO SECOND | 6 |
p: Precision.
s: Decimal scaling position.
- The result is a character string expression of the absolute value of the value expression, without a sign or decimal point. If the actual value has fewer digits than the data length of the destination field, the destination field is filled with leading zeros.
Example: The data type of the values to be converted is DECIMAL(4,1):
15.
'0150'
-12.4
'0124'
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
(d) Example
Retrieve data for which the value of column C1 (CHAR data type) and the value of column C2 (DECIMAL data type) in table T3 are equal:
SELECT * FROM T3
WHERE C1 = DIGITS(C2)
![[Figure]](figure/zu2s0210.gif)
(9) FLOAT
(a) Function
The FLOAT scalar function converts numeric data into floating-point data.
(b) Format
FLOAT (value-expression)
(c) Rules
- The following items can be specified as the value expression:
- Numerical literals
- Component specification
- SQL variables or SQL parameters
- Arithmetic operations
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The following items can be specified as the data type of value-expression:
- The data type of the result should be a double-precision floating-point number (FLOAT).
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
(d) Example
Obtain the result in floating-point data of dividing column C1 (INTEGER data type) by column C2 (INTEGER data type) in Table T1; use the FLOAT scalar function before the division operation to convert either operand to the FLOAT type:
SELECT FLOAT(C1)/C2 FROM T1
![[Figure]](figure/zu2s0220.gif)
(10) HEX
(a) Function
The HEX scalar function converts a value expression into a hexadecimal character string expression.
(b) Format
HEX (value-expression)
(c) Rules
- The HEX scalar function converts the format of the value expression, represented internally in the system, into a hexadecimal character string expression.
Table 2-28 shows the formats of internal representations and examples of execution results.
Table 2-28 Formats of internal representations by the HEX scalar function and examples of execution results
Value expression | Internal representation format | HEX(value-expression) |
---|
'#AB12' | Data type: CHAR(5) 23|41|42|31|32 | '2341423132' |
1234 | Data type: INTEGER In Windows: D2|04|00|00 In UNIX: 00|00|04|D2 | In Windows: 'D2040000' In UNIX: '000004D2' |
1234. | Data type: DECIMAL(4,0) 01| 23| 4C | '01234C' |
- The following items can be specified as the value expression:
- Literals
- USER
- CURRENT_DATE
- CURRENT_TIME
- CURRENT_TIMESTAMP[(p)]
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Arithmetic operations
- Date operations
- Time operations
- Concatenation operations
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- Table 2-29 shows the relationship between data types that can be specified as the value expression and the data type and data length of the result.
Table 2-29 Relationship between the data type of a value expression for the HEX scalar function and the data type and data length of a result
Value expression | Execution result |
---|
Data type | Defined length | Actual length | Data type | Defined length | Actual length |
---|
CHAR(n) | 1 n < 128 | ![[Figure]](figure/zueng033.gif) | CHAR | n*2 | ![[Figure]](figure/zueng033.gif) |
128 n 16,000 | VARCHAR | n*2 |
NCHAR(n) | 1 n < 64 | CHAR | n*4 | ![[Figure]](figure/zueng033.gif) |
64 n 8,000 | VARCHAR | n*4 |
MCHAR(n) | 1 n < 128 | CHAR | n*2 | ![[Figure]](figure/zueng033.gif) |
128 n 16,000 | VARCHAR | n*2 |
VARCHAR(n) | 1 n 16,000 | [r] | VARCHAR | n*2 | r*2 |
NVARCHAR(n) | 1 n 8,000 | n*4 | r*4 |
MVARCHAR(n) | 1 n 16,000 | n*2 | r*2 |
INTEGER | ![[Figure]](figure/zueng033.gif) | ![[Figure]](figure/zueng033.gif) | CHAR | 8 | ![[Figure]](figure/zueng033.gif) |
SMALLINT | 4 |
DECIMAL(P,S) | 1 P 29 0 S 29 S P | ( P/2 + 1) * 2 |
FLOAT | ![[Figure]](figure/zueng033.gif) | 16 |
SMALLINT | 8 |
DATE | 8 |
TIME | 6 |
TIMESTAMP(p) | p = 0, 2, 4, or 6 | (7+p/2)*2 |
INTERVAL YEAR TO DAY | ![[Figure]](figure/zueng033.gif) | 10 |
INTERVAL HOUR TO SECOND | 8 |
BINARY(n) | 1 n 16,000 | [r] | VARCHAR | n*2 | r*2 |
P: Precision
S: Decimal scaling position
p: Fractional second precision
: Not applicable
Note: If only a character string literal (including national and mixed character string literals) whose length is 0 is specified in the value expression, the defined length of the execution result will be 1.
- The HEX scalar function cannot be specified for a value expression if execution of the value expression produces any of the following data types:
- CHAR, VARCHAR, MCHAR, or MVARCHAR with a minimum length of 16,001 bytes
- NCHAR or NVARCHAR with a minimum length of 8,001 characters
- BLOB
- BINARY with a minimum length of 16,001 bytes
- BOOLEAN
- A value expression that contains embedded variables or ? parameters cannot be specified.
- If an operand in the value expression or the argument of the function is composed solely of literals, the HEX scalar function cannot be specified if it produces a result with a length exceeding 255 bytes.
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
- In the Windows version, the result value (numeric data excluding the DECIMAL type) is represented internally in Little Endian. Specifically, the value 1234 of the INTEGER type is represented internally as D2 04 00 00 and the execution result is represented as D2040000.
- In the UNIX version, the result value depends on the internal expression of the server platform. For example, in the case of Linux running on an Intel family CPU, numeric data excluding the DECIMAL type is represented internally in Little Endian. Specifically, the value 1234 of the INTEGER type is represented internally as D2 04 00 00, and the execution result is represented as D2040000.
(11) HOUR
(a) Function
Extracts the time part from time data, time stamp data, or time interval data.
(b) Format
HOUR (value-expression)
(c) Rules
- The following items can be specified as the value expression:
- CURRENT_TIME
- CURRENT_TIMESTAMP[(p)]
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Time operations
- Set functions (MAX, MIN)
- Scalar functions (ABS, TIME, VALUE)
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The data type of the value expression should be the time data type (TIME), time stamp data type (TIMESTAMP), or time interval data type (INTERVAL HOUR TO SECOND).
- The data type of the result is integer (INTEGER).
- If the value expression is of the time data type or the time stamp data type, the result is in the range 0 to 23.
- If the value expression is of the time interval data type, the result will be in the range -99 to 99.
If the result is non-zero, the result has the same sign as the value expression.
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
(d) Example
Retrieve all rows with the current time from the rows in column C1 (time data type) of table T1:
SELECT * FROM T1
WHERE HOUR (C1) = HOUR(CURRENT TIME)
(12) INTEGER
(a) Function
The INTEGER scalar function converts numeric data into an integer.
(b) Format
INT[EGER] (value-expression)
(c) Rules
- The following items can be specified as the value expression:
- Numeric literals
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Arithmetic operations
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The following items can be specified as the data type of value-expression:
- The data type of the result is an integer (INTEGER).
- The result of executing the INTEGER scalar function should be a value that can be expressed in INTEGER.
- Any numeric digits in the result that follow the decimal point are rounded off.
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
(d) Example
Obtain only the fractional part from column C1 (DECIMAL(4,3)) in table T1:
SELECT C1 - INTEGER(C1) FROM T1
![[Figure]](figure/zu2s0230.gif)
(13) IS_USER_CONTAINED_IN_HDS_GROUP
(a) Function
The IS_USER_CONTAINED_IN_HDS_GROUP scalar function returns a Boolean value indicating whether or not the executing user belongs to the Sun Java System Directory Server role.
(b) Format
IS_USER_CONTAINED_IN_HDS_GROUP (value-expression)
(c) Rules
- The following items can be specified in the value expression:
- USER
- Character string literal or mixed character string literal
- Column specification (dictionary table column only)
- Component specification
- SQL variable or parameter
- Concatenation operation
- Set function
- Scalar function
- CASE expression
- CAST specification
- Function call
- Scalar subquery
- The data type of the value expression must be character string (CHAR or VARCHAR) or mixed character string (MCHAR or MVARCHAR).
- For the value of the value expression, specify a role name that is registered with the directory server. For role names, see the HiRDB Version 8 System Operation Guide. For restrictions on the length of a role name, see 1.1.7 Specification of names.
- When a column specification is specified in the value expression, only a dictionary table column can be specified.
- Multiple table columns cannot be specified in the value expression. If the OR operation is to be performed on a predicate for which IS_USER_CONTAINED_IN_HDS_GROUP is specified, multiple table columns cannot be specified in the OR operation. An AND operation negated by a NOT is also treated as an OR operation.
- IS_USER_CONTAINED_IN_HDS_GROUP cannot be specified in a selection expression or in a HAVING clause.
- Cannot be specified in a retrieval using a list.
- If IS_USER_CONTAINED_IN_HDS_GROUP is specified in ON search condition for a joined table for an outer join, a column of the outer table cannot be specified in the value expression.
- If a table joined to an outer join is specified in a FROM clause and IS_USER_CONTAINED_IN_HDS_GROUP is specified in a WHERE clause, columns of the inner table of the outer join cannot be specified.
- The following items cannot be specified if a column of a dictionary table is not specified in the value expression:
- Multiple tables specified in the FROM clause
- ORDER BY specification
- DISTINCT specification
- GROUP BY specification
- Set function specification
- FOR UPDATE specification
- FOR READ ONLY specification
- Specify one or more dictionary tables in the FROM clause.
- The data type of the result is BOOLEAN.
- The value of the result is not NOT NULL constrained (the null value is allowed). If the value expression is the null value, the result also is the null value.
- Use of the directory server linkage facility produces the following results:
- If the executing user does not belong to the role, the result is FALSE.
- If the role is not registered in the directory server (the role does not exist), the result is FALSE.
- If the executing user belongs to the role, the result is TRUE.
- If the role name is registered in duplicate, the result is FALSE.
- If the directory server linkage facility is not being used, the result always is FALSE.
- If there is a change in user information or role information on the directory server after an execution user has connected to HiRDB and while he or she is executing IS_USER_CONTAINED_IN_HDS_GROUP, HiRDB may fail to determine whether the execution user belongs to a role, and results may change.
(d) Example
Assume that a role with a condition of manager is specified in the Sun Java System Directory Server, and that a manager role (MGRROLE) is registered. The SELECT privilege for table T1 to which user A belongs is granted to the manager role. In addition, assume that the SELECT privilege for table T2 owned by user A is granted to user B. This example shows retrieval of the dictionary table by using IS_USER_CONTAINED_IN_HDS_GROUP in order to check the names of the tables that user B can access.
- The executing user can access the following tables:
- Tables owned by the executing user
- Tables for which the executing user has access privilege
- Tables for which the access privilege is PUBLIC
- Tables for which the role to which the executing user belongs has access privilege
- The SELECT privilege for table T1 to which user A belongs is granted to the manager role (MGRROLE).
GRANT SELECT ON T1 TO GROUP MGRROLE
- Grant the SELECT privilege for table T2 owned by user A to user B (USERB):
GRANT SELECT ON T2 TO USERB
Check the names of the tables that user B can access (the example shows use of IS_USER_CONTAINED_IN_HDS_GROUP to retrieve the dictionary table, the contents of the dictionary table, and the retrieval results):
Retrieval example:
SELECT TABLE_NAME FROM MASTER.SQL_TABLE_PRIVILEGES X
WHERE (X.GRANTOR=USER OR
X.GRANTEE_TYPE IS NULL AND X.GRANTEE IN
(USER, 'PUBLIC') OR
X.GRANTEE_TYPE='G' AND
IS_USER_CONTAINED_IN_HDS_GROUP(X.GRANTEE IS TRUE)
Dictionary table:
GRANTOR GRANTEE TABLE_SCHEMA TABLE_NAME ... GRANTEE_TYPE
USERA USERA USERA T1 -
USERA MGRROLE USERA T1 G
USERA USERA USERA T2 -
USERA USERB USERA T2 -
- Retrieval results:
- When user B (USERB) belongs to the manager role (when the result is TRUE)
T1, T2
- When user B (USERB) does not belong to the manager role (when the result is FALSE)
T2
(14) LENGTH
(a) Function
The LENGTH scalar function determines the length of a value expression.
(b) Format
LENGTH ({value-expression
|GET_JAVA_STORED_ROUTINE_SOURCE specification})
(c) Rules
- The following items can be specified as the value expression:
- Literals
- USER
- CURRENT_DATE
- CURRENT_TIME
- CURRENT_TIMESTAMP[(p)]
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Arithmetic operations
- Date operations
- Time operation
- Concatenation operations
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- : embedded-variable [: indicator-variable] AS data-type (allowable data types: BLOB or BINARY types only)
- ? AS data-type (allowable data types: BLOB or BINARY types only)
- The following data types cannot be specified in value-expression:
- BOOLEAN
- Abstract data type
- When specifying an embedded variable or a ? parameter in value-expression, its data type should be specified in the AS clause. An error may result if the actual length (for a locator, the actual length of the data allocated to the locator) of the data assigned to the embedded variable or ? parameter is greater than the maximum length of the data type specified in the AS clause.
- The data type of the result is integer (INTEGER).
- The result of execution of the LENGTH function depends on the data type of the value expression, as shown in Table 2-30.
Table 2-30 Execution result as a function of the data type of the value expression for the LENGTH scalar function
Data type of value expression | Execution result |
---|
Fixed-length character data | Definition length in bytes |
Variable-length character data | Actual number of data bytes |
Fixed-length national character data | Definition length in characters |
Variable-length national character data | Actual number of data characters |
Fixed-length mixed character data | Actual number of data characters |
Variable-length mixed character data | Actual number of data characters |
Numeric data | Definition length in bytes For the DECIMAL data type, the length is ( number of digits specified in precision/2 + 1). For details, see 1.2 Data types. |
Date, time, or time stamp data | Definition length in bytes |
Date interval/time interval data | For details, see 1.2 Data types. |
Large-object data | Actual number of data bytes |
Binary data | Actual number of data bytes |
- If the value expression is of the character string data type, a space is counted as one character.
- If the value expression is a literal, it is processed according to the data type interpreted by HiRDB; see 1.3 Literals for details.
- If the value expression is USER, CURRENT_DATE, CURRENT_TIME, or CURRENT_TIMESTAMP[(p)], the value expression is processed according to the data type as interpreted by HiRDB. For details, see 1.4 USER, CURRENT_DATE value function, CURRENT_TIME value function, and CURRENT_TIMESTAMP value function.
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
(15) LOWER
(a) Function
The LOWER function converts the uppercase alphabetic characters in character data, national character data, or mixed character data into lowercase characters.
(b) Format
LOWER (value-expression)
(c) Rules
- The following items can be specified as a value expression:
- Literals
- USER
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Concatenation operations
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- NULL, embedded variables, or the ? parameter cannot be specified in the value expression.
- The data type of the value expression should be character string data type (CHAR or VARCHAR), national character string data type (NCHAR or NVARCHAR) or mixed character string data type (MCHAR or MVARCHAR).
- The execution result inherits the data type and the data length of the value expression.
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
(16) MINUTE
(a) Function
Extracts the minute part from time data, time stamp data, or time interval data.
(b) Format
MINUTE (value-expression)
(c) Rules
- The following items can be specified as the value expression:
- CURRENT_TIME
- CURRENT_TIMESTAMP[(p)]
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Time operations
- Set functions (MAX, MIN)
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The data type of the value expression should be either the time data type (TIME) or the time interval data type (INTERVAL HOUR TO SECOND).
- The data type of the result is integer (INTEGER).
- If the value expression is the time data type or time stamp data type, the result is in the range 0 to 59.
- If the value expression is of the time interval data type, the result will be in the range -59 to 59.
If the result is non-zero, the result has the same sign as the value expression.
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
(d) Example
Retrieve the data for which the difference between column C2 (time data type) and column C3 (time data type) in table T1 is less than or equal to 30 minutes:
SELECT C1 FROM T1
WHERE MINUTE(C3-C2) <= 30
![[Figure]](figure/zu2s0240.gif)
(17) MOD
(a) Function
The MOD scalar function returns the remainder from a division operation.
(b) Format
MOD (value-expression-1, value-expression-2)
(c) Rules
- The following items can be specified as value-expression-1 and value-expression-2:
- Integer literals or decimal number literals
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Arithmetic operations
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The numerator is specified as value-expression-1, and the denominator is specified as value-expression-2.
- The data types of value-expression-1 and value-expression-2 must be one of the following:
- Integer (INTEGER, SMALLINT)
- Fixed-point number (DECIMAL)
- The value of the result is not NOT NULL constrained (the null value is allowed). If value-expression-1 or value-expression-2 is the null value, the result will also be the null value.
- If value-expression-1 or value-expression-2 contains a decimal part, the result value will also contain a decimal part.
- The sign of the result will be the same as the sign of value-expression-1.
- If value-expression-2 is 0, an error results (for the result when overflow error suppression is set, see 2.18 Operational results with overflow error suppression specified).
- If the following inequality holds, overflow will occur during the computation, resulting in an overflow error:
- (p1 - s1) + s2 > 29
- p1: Effective precision of the value of value-expression-1
- s1: Effective decimal scaling position of the value of value-expression-1
- p2: Effective precision of the value of value-expression-2
- s2: Effective decimal scaling position of the value of value-expression-2
For the result when overflow error suppression is set, see 2.18 Operational results with overflow error suppression specified.
- Table 2-31 shows the relationship between the data type of the result and the data types of value-expression-1 and value-expression-2.
Table 2-31 Relationship between a result data type and value-expression-1 and value-expression-2 data types
value-expression-1 Data Type | Value-expression-2 data type |
---|
SMALLINT | INTEGER | DECIMAL |
---|
SMALLINT | SMALLINT | INTEGER | DECIMAL |
INTEGER | SMALLINT | INTEGER | DECIMAL |
DECIMAL (p, 0) | SMALLINT | INTEGER | DECIMAL |
DECIMAL (p, s) (s > 0) | DECIMAL | DECIMAL | DECIMAL |
- Table 2-32 shows the precision and decimal scaling position of the result when the data type of the result is DECIMAL.
Table 2-32 Precision and decimal scaling position of a result when the result data type is DECIMAL
Item | Precision or decimal scaling position |
---|
Precision (p) | p=MIN (p2-s2+s, 29) |
Decimal scaling position (s) | s=MAX (s1, s2) |
- Note 1
- value-expression-1 data type: DECIMAL(p1,s1)
- value-expression-2 data type: DECIMAL(p2,s2)
- Note 2
- INTEGER is treated as DECIMAL(10,0).
- SMALLINT is treated as DECIMAL(5,0).
(18) MONTH
(a) Function
Extracts the month part from date data, time stamp data, or date interval data.
(b) Format
MONTH (value-expression)
(c) Rules
- The following items can be specified as the value expression:
- CURRENT_DATE
- CURRENT_TIMESTAMP[(p)]
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Date operations
- Set functions (MAX, MIN)
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The data type of the value expression should be the date data type (DATE), time stamp data type (TIMESTAMP), or date interval data type (INTERVAL YEAR TO DAY).
- The data type of the result is integer (INTEGER).
- If the value expression is the date data type or time stamp data type, the result is in the range 1 to 12.
- If the value expression is of the date interval data type, the result will be in the range -11 to 11.
If the result is non-zero, the result has the same sign as the value expression.
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
(d) Example
Delete all rows from table T1 that are not of the current month (September):
DELETE FROM T1
WHERE MONTH(C1) <> MONTH(CURRENT_DATE)
![[Figure]](figure/zu2s0250.gif)
(19) POSITION
(a) Function
Determines the starting position of the first part in a data string (a character string or binary string) that matches a given data substring.
(b) Format
POSITION (value-expression-1 IN value-expression-2 [ FROM value-expression-3]) |
(c) Rules
- In value-expression-1, specify the search data substring. In value-expression-2, specify the data string to be searched for. Items that can be specified in value-expression-1 and value-expression-2 are listed below. Items that can be specified vary depending upon combinations of the data types of value-expression-1 and value-expression-2. For specifiable combinations, see Rule 2.
- Literals (character strings, national character strings, mixed character strings, or hexadecimal character strings)
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Concatenation operations
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- : embedded-variable [: indicator-variable] AS data-type (allowable data types: BLOB or BINARY types only)
- ? AS data-type (allowable data types: BLOB or BINARY types only)
- The following table shows combinations of data types that can be specified in value-expression-1 and value-expression-2:
Table 2-33 Combinations of data types that can be specified in value expression 1 and value expression 2 of the scalar function POSITION
- Legend:
- Y: Specifiable
- R: Specifiable, subject to restrictions
- N: Not specifiable
- Table number: Table of combinations of corresponding item if item is specifiable
- 1 Can be specified only if value expression-1 is a hexadecimal character string literal.
- 2 Can be specified only if value expression-2 is a hexadecimal character string literal.
- The following table shows combinations of items that can be specified in value expression-1 and value expression-2.
Table 2-34 Combinations of items that can be specified in value expression 1 and value expression 2 of the scalar function POSITION (where value expression 1 and value expression 2 are both a character string data type, national character string data type, mixed character string data type, or BINARY type with a maximum length of 32,000 bytes)
Value expression 1 | Value expression 2 |
---|
Lit | Col spc | Cmp spc | SQL | Con | Set fnc | Scl fnc | CSE | CST | Fnc call | Scl sq | Emb var, ? para* |
---|
Literal | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
Column specification | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
Component specification | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
SQL variable, SQL parameter | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
Concatenation | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
Set function | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
Scalar function | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
CASE expression | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
CAST specification | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
Function call | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
Embedded variable, ? parameter* | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
- Legend:
- Y: Specifiable
- Lit: Literal
- Col spc: Column specification
- Cmp spc: Component specification
- SQL: SQL variable, SQL parameter
- Con: Concatenation
- Set fnc: Set function
- Scl fnc: Scalar function
- CSE: CASE expression
- CST: CAST specification
- Fnc call: Function call
- Scl sq: Scalar subquery
- Emb var: Embedded variable
- ? para: ? parameter
- * Embedded variables and ? parameters can be specified only if they are of the BINARY type.
Table 2-35 Combinations of items that can be specified in value expression 1 and value expression 2 of the scalar function POSITION (where value expression 2 is either the BLOB type or the BINARY type with a maximum length of 32,0001 bytes or greater)
Value expression 1 | Value expression 2 |
---|
Lit | Col spc | Cmp spc | SQL | Con | Set fnc | Scl fnc | CSE | CST | Fnc call | Scl sq | Emb var, ? para |
---|
Literal* | N | Y | N | Y | N | N | N | N | N | N | N | Y |
Column specification | N | N | N | N | N | N | N | N | N | N | N | N |
Component specification | N | N | N | N | N | N | N | N | N | N | N | N |
SQL variable, SQL parameter | N | Y | N | Y | N | N | N | N | N | N | N | Y |
Concatenation | N | N | N | N | N | N | N | N | N | N | N | N |
Set function | N | N | N | N | N | N | N | N | N | N | N | N |
Scalar function | N | N | N | N | N | N | N | N | N | N | N | N |
CASE expression | N | N | N | N | N | N | N | N | N | N | N | N |
CAST specification | N | N | N | N | N | N | N | N | N | N | N | N |
Function call | N | N | N | N | N | N | N | N | N | N | N | N |
Embedded variable, ? parameter | N | Y | N | Y | N | N | N | N | N | N | N | Y |
- Legend:
- Y: Specifiable
- N: Not specifiable
- Lit: Literal
- Col spc: Column specification
- Cmp spc: Component specification
- SQL: SQL variable, SQL parameter
- Con: Concatenation
- Set fnc: Set function
- Scl fnc: Scalar function
- CSE: CASE expression
- CST: CAST specification
- Fnc call: Function call
- Scl sq: Scalar subquery
- Emb var: Embedded variable
- ? para: ? parameter
- * Literals can be specified only if they are of the character string data type (hexadecimal character string literal).
Table 2-36 Combinations of items that can be specified in value expression 1 and value expression 2 of the scalar function POSITION (where value expression 1 is either the BLOB type or the BINARY type with a maximum length of 32,001 bytes or greater, and value expression 2 is either the character string data type or the BINARY type with a maximum length of 32,000 bytes)
Value expression 1 | Value expression 2 |
---|
Lit | Col spc | Cmp spc | SQL | Con | Set fnc | Scl fnc | CSE | CST | Fnc call | Scl sq | Emb var, ? para* |
---|
Literal | N | N | N | N | N | N | N | N | N | N | N | N |
Column specification | N | N | N | N | N | N | N | N | N | N | N | N |
Component specification | N | N | N | N | N | N | N | N | N | N | N | N |
SQL variable, SQL parameter | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
Concatenation | N | N | N | N | N | N | N | N | N | N | N | N |
Set function | N | N | N | N | N | N | N | N | N | N | N | N |
Scalar function | N | N | N | N | N | N | N | N | N | N | N | N |
CASE expression | N | N | N | N | N | N | N | N | N | N | N | N |
CAST specification | N | N | N | N | N | N | N | N | N | N | N | N |
Function call | N | N | N | N | N | N | N | N | N | N | N | N |
Embedded variable, ? parameter | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
- Legend:
- Y: Specifiable
- N: Not specifiable
- Lit: Literal
- Col spc: Column specification
- Cmp spc: Component specification
- SQL: SQL variable, SQL parameter
- Con: Concatenation
- Set fnc: Set function
- Scl fnc: Scalar function
- CSE: CASE expression
- CST: CAST specification
- Fnc call: Function call
- Scl sq: Scalar subquery
- Emb var: Embedded variable
- ? para: ? parameter
- * Embedded variables and ? parameters can be specified only if they are of the BINARY type.
- When specifying an embedded variable or a ? parameter in value-expression-1 or value-expression-2, specify its data type in the AS clause. An error may result if the actual length (for a locator, the actual length of the data allocated to the locator) of the data assigned to the embedded variable or ? parameter is greater than the maximum length of the data type specified in the AS clause.
- value-expression-3 specifies a starting search position. If value-expression-2 is a character string data type, BLOB type, or BINARY type, the starting position is specified in bytes; if value-expression-2 is a national character string data type or mixed character string data type, it is specified in units of characters. If the data type of value-expression-2 is character string data type, national character string data type, or mixed character string data type, the starting position should be within a 1
(value-expression-3)
(maximum of value-expression-2) range. If the data type of value-expression-2 is the BLOB or BINARY type, the starting position should be in a 1
(value-expression-3) range. For the mixed character string data type, the (maximum of value-expression-2) is in units of bytes. If value-expression-3 is omitted, the default starting search position is 1. - The following items can be specified in value-expression-3:
- Unsigned integer literal
- Column specification
- Component specification
- Arithmetic operations
- Set function
- Scalar function
- CASE expression
- CAST specification
- Function call
- Scalar subquery
- SQL variable or SQL parameter
- Embedded variable or ? parameter
The following items can be specified in value-expression-3 if value-expression-1 or value-expression-2 is a BLOB type or a BINARY type with a maximum length of 32,0001 bytes or greater:
- Unsigned integer literal
- SQL variable or SQL parameter
- Embedded variable or ? parameter
- The data type of value-expression-3 should be integer (INTEGER, SMALLINT).
- The data type of a result will be integer (INTEGER).
- The result is in bytes if value-expression-2 is a character string data type, BLOB type, or BINARY type, and it is a position in units of characters if value-expression-2 is a national character string data type or mixed character string data type.
- If the actual length of value-expression-1 is 0, the result will be the value of value-expression-3. If value-expression-3 is omitted, the actual length will be 1.
- If the actual length of value-expression-1 is greater than 0 and value-expression-3 is greater than the actual length of value-expression-2, the result will be 0.
- If the data substring of value-expression-1 is not found beyond the starting search position in the data string specified in value-expression-2, the result will be 0.
- The value of the result will be the NOT NULL constraint (allows the null value). If value-expression-1, value-expression-2, or value-expression-3 is the null value, the result will also be the null value.
(d) Example
Find the first position where the character string 'TIME' occurs at byte 6 or beyond in column C1 (data type: CHAR) of table T1:
SELECT POSITION('TIME:' IN C1 FROM 6) FROM T1
![[Figure]](figure/zu2s0255.gif)
(20) SECOND
(a) Function
Extracts the second part from time data, time stamp data, or time interval data.
(b) Format
SECOND (value-expression)
(c) Rules
- The following items can be specified as the value expression:
- CURRENT_TIME
- CURRENT_TIMESTAMP[(p)]
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Time operations
- Set functions (MAX, MIN)
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The data type of the value expression should be the time data type (TIME), time stamp data type (TIMESTAMP), or time interval data type (INTERVAL HOUR TO SECOND).
- The data type of the result is integer (INTEGER).
- If the value expression is the time data type or time stamp data type, the result is in the range 0 to 59.
- If the value expression is of the time interval data type, the result will be in the range -59 to 59.
If the result is non-zero, the result has the same sign as the value expression.
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
(d) Example
Determine in units of seconds the difference between the earliest time and the latest time in column C1 (time data type) in table T1:
SELECT MINUTE(MAX(C1)-MIN(C1))
*60+SECOND(MAX(C1)
-MIN(C1))
FROM T1
![[Figure]](figure/zu2s0260.gif)
(21) SUBSTR
(a) Function
Extracts a part of character string data, national character string data, mixed character string data, or binary data.
(b) Format
SUBSTR (value-expression-1, value-expression-2 [, value-expression-3])
(c) Rules
- In value-expression-1, specify the data string (a character string or a binary string) to be processed. The following items can be specified in value-expression-1, subject to variation depending upon the data type of value-expression-1:
- Literals (character strings, national character strings, or mixed character strings)
- Column specification
- Component specification
- SQL variables or ? parameters
- Concatenation operation
- Set functions
- Scalar functions
- CASE expression
- CAST specification
- Function calls
- Scalar subquery
- : embedded-variable [: indicator-variable] AS data-type (allowable data types: BLOB or BINARY types only)
- ? AS data-type (allowable data types: BLOB or BINARY types only)
The following table shows data types that can be specified.
Table 2-37 Items that can be specified depending on the data type of value expression 1 of the scalar function SUBSTR
Item | Data type of value expression 1 |
---|
Character string data type, national character string data type, mixed character string data type | BINARY type with a maximum length of 32,000 bytes | BLOB type, or BINARY type with a maximum length of 32,001 bytes or greater |
---|
Literal | Y | N | N |
Column specification | Y | Y | Y |
Component specified | Y | Y | N |
SQL variable, SQL parameter | Y | Y | Y |
Concatenation Set function Scalar function CASE expression CAST specification | Y | Y | N |
Function call | Y | Y | Y |
Scalar subquery | Y | Y | N |
Embedded variable ? parameter | N | Y | Y |
- Legend:
- Y: Specifiable
- N: Not specifiable
- The data type of value-expression-1 should be the character string data type (CHAR, VARCHAR), national character string data type (NCHAR, NVARCHAR), mixed character string data type, (MCHAR, MVARCHAR), BLOB type, or BINARY type.
- When specifying an embedded variable or a ? parameter in value-expression-1, specify its data type in the AS clause. An error may result if the actual length (for a locator, the actual length of the data allocated to the locator) of the data assigned to the embedded variable or ? parameter is greater than the maximum length of the data type specified in the AS clause.
- In value-expression-2, specify the starting position of the partial data string to be extracted as a positive integer. For the character string data type, the BLOB type, or the BINARY type, specify the starting position in bytes; for the national character string data type or the mixed character string data type, specify it in units of characters. If the data type of value-expression-1 is of the character string data type, the national character string data type, or the mixed character string data type, the starting position should be in the range 1
(value-expression-2)
(maximum length of value-expression-1). If the data type of value-expression-1 is of the BLOB type or the BINARY type, the starting position should be in the range 1
(value-expression-2). For the mixed character string data type, the unit of (maximum length of value-expression-1) is in bytes. - In value-expression-3, specify the length of the partial data string to be extracted, as a positive integer.
For the character string data type, the BLOB type, or the BINARY type, specify the length in bytes. For the national character string data type or the mixed character string data type, specify it in units of characters.
If the data type of value-expression-1 is of the character string data type, the national character string data type, or the mixed character string data type, the length should be specified in the range 0
(value-expression-3)
(maximum length of value-expression-1) - (value-expression-2) + 1. If the data type of value-expression-1 is of the BLOB type or the BINARY type, the length should be in the range 0
(value-expression-3). The literal 0 cannot be specified in value-expression-3.
- The following items can be specified as value-expression-2 and value-expression-3:
- Unsigned integer literals
- Column specifications
- Component specification
- Arithmetic operations
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- SQL variables or SQL parameters
- Embedded variables or ? parameters
- Scalar subquery
If value-expression-1 is of the BLOB type of the BINARY type with a minimum length of 32,001 bytes, the following items can be specified in value-expression-2 and value-expression-3:
- Unsigned integer literals
- SQL variables or SQL parameters
- Embedded variables or ? parameters
- The data type of value-expression-2 and value-expression-3 should be an integer (INTEGER or SMALLINT).
- If value-expression-2 is greater than the real length of value-expression-1, the result is the null value (the length of the result: 0).
- If the data length of a result is 0, the result is the null value.
- If value-expression-3 is omitted and value-expression-1 is fixed-length data, HiRDB extracts characters from the starting position indicated by value-expression-2 through the last character indicated by the definition length. If value-expression-1 is variable-length data, HiRDB extracts characters from the starting position indicated by value-expression-2 through the last character indicated by the real length.
- If value-expression-1 is of the BLOB type and value-expression-3 is not omitted, and if the data string in the specified range includes a part that does not contain real data, HiRDB only extracts the part that contains real data.
- The value of the result is not NOT NULL constrained (the null value is allowed).
- Table 2-38 and Table 2-39 show the data types and lengths of the result.
Table 2-38 Data types and lengths of results of the SUBSTR scalar function (value-expression-3 specified)
Data type of character string (value-expression-1) from which partial string is extracted | Actual length | Length L (value-expression-3) |
---|
Literal (character string) | Non-literal |
---|
L1 255 | L1 256 |
---|
CHAR(n) | ![[Figure]](figure/zueng033.gif) | CHAR(L) | VARCHAR(L)[L] | VARCHAR[L] |
VARCHAR(n) | [r] |
NCHAR(n) | ![[Figure]](figure/zueng033.gif) | NCHAR(L) | NVARCHAR(L)[L] | NVARCHAR[L] |
NVARCHAR(n) | [r] |
MCHAR(n) | ![[Figure]](figure/zueng033.gif) | MCHAR(L1) | MVARCHAR(L1)[L2] | MVARCHAR[L2] |
MVARCHAR(n) | [r] |
BLOB(n) | [r] | BLOB(L)[k5] | BLOB(L)[k5] | BLOB(n)[k5] |
BINARY(n) | [r] | BINARY(L)[k5] | BINARY(L)[k5] | BINARY(n)[k5] |
- [ ]
- Value enclosed in square brackets is the actual length.
- L1
- If data type is character string: Length L of value-expression-3 (in bytes).
- If data type is national character string: Length L of value-expression-3 (in characters)
2 - If data type is mixed character string: min(length L of value-expression-3 (in characters)
c, n).
- L2
- Length (in bytes) of the extracted partial character string containing L characters (L
L2
L1).
- n
- Definition length of the data string (value-expression-1) to be processed
- For the character string data type, the mixed character string data type, or the BLOB type, in bytes
- For the national character string data type, in units of characters
- k5
- min(L,r-S + 1)
- c
- Maximum number of bytes representing each character
- When utf-8 is specified for the character code type in the pdntenv command (pdsetup command in the case of UNIX), the following specifications are used:
- pd_substr_length in the system common definition
- PDSUBSTRLEN in the client environment definition
- SUBSTR LENGTH in the SQL compile option
- Note that the value is 2 when the character code type is not utf-8.
: Not applicable.
Note 1: When value-expression-3 is specified, the specification of value-expression-2 does not affect the data type of the result.
- When value-expression-2 is longer than the actual length of value-expression-1, the result is the null value (length of the result is 0).
- When the length of the partial character string of the result is 0, the result is the null value.
- When value-expression-3 is omitted and value-expression-1 is fixed-length data, characters are extracted from the beginning of value-expression-2 for as many characters as the defined length. If value-expression-1 is variable-length data, characters are extracted from the beginning of value-expression-2 through the last character of the actual length.
- The value of the result is not NOT NULL constrained (null value is allowed). If any of value-expression-1, value-expression-2, or value-expression-3, is the null value, the result will also be the null value.
Note 2: If value-expression-1 is a variable-length character string and value-expression-3 is not omitted, the portion of the character string into which characters are to be extracted but which does not contain real data will be padded with spaces.
- Example:
- Executing SUBSTR(character-string-1, 3, 5) on character string 1 of VARCHAR(8)[5] causes spaces to be set in the two right-side characters in the character string being extracted.
If value-expression-1 is of the BLOB type or the BINARY type and value-expression-3 is not omitted, and if the binary string in the specified range includes a part that does not contain real data, HiRDB extracts only the part that contains real data, without setting spaces.
- Example:
- Executing SUBSTR(binary-data-1, 101, 600) on binary data 1 of BLOB(1024)[512] produces a result that is from bytes 101 to 512 of binary data 1.
Table 2-39 Data types and lengths of results of the SUBSTR scalar function (value-expression-3 omitted)
Data type of character string (value-expression-1) from which partial string is extracted | Actual length | Begin position S (value-expression-2) |
---|
Literal (character string) | Non-literal |
---|
value-expression-1: Fixed length | value-expression-1: Variable length |
---|
k0 255 | k0 256 |
---|
CHAR(n) | ![[Figure]](figure/zueng033.gif) | CHAR(k1) | VARCHAR(n)[k1] | ![[Figure]](figure/zueng033.gif) | VARCHAR(n)[k1] |
VARCHAR(n) | [r] | ![[Figure]](figure/zueng033.gif) | ![[Figure]](figure/zueng033.gif) | VARCHAR(n)[k2] | VARCHAR(n)[k2] |
NCHAR(n) | ![[Figure]](figure/zueng033.gif) | NCHAR(k1) | NVARCHAR(n)[k1] | ![[Figure]](figure/zueng033.gif) | NVARCHAR(n) [k1] |
NVARCHAR(n) | [r] | ![[Figure]](figure/zueng033.gif) | ![[Figure]](figure/zueng033.gif) | NVARCHAR(n)[k2] | NVARCHAR(n) [k2] |
MCHAR(n) | ![[Figure]](figure/zueng033.gif) | MCHAR(k1) | MVARCHAR(n) [k3] | ![[Figure]](figure/zueng033.gif) | MVARCHAR(n) [k3] |
MVARCHAR(n) | [r] | ![[Figure]](figure/zueng033.gif) | ![[Figure]](figure/zueng033.gif) | MVARCHAR(n)[k4] | MVARCHAR(n) [k4] |
BLOB(n) | [r] | ![[Figure]](figure/zueng033.gif) | ![[Figure]](figure/zueng033.gif) | BLOB(n)[k2] | BLOB(n)[k2] |
BINARY(n) | [r] | ![[Figure]](figure/zueng033.gif) | ![[Figure]](figure/zueng033.gif) | BINARY(n)[k2] | BINARY(n)[k2] |
- [ ]
- Value enclosed in square brackets is the actual length.
- n
- Definition length of the character string of value-expression-1 from which data is extracted (in bytes for the character string or mixed character string data type; in characters for the national character string data type)
- k0
- If data type is character string or mixed character string: n - S + 1
- If data type is mixed character string: (n - S + 1)
2
- k1
- n - S + 1
- k2
- max(r - S + 1, 0)
- k3
- Number of bytes in the partial character string from the Sth character through the nth byte
- max (n - (S - 1)
c, 0)
l3
n - S + 1
- l4
- Number of bytes in the partial character string from the Sth character through the rth byte
- max (r - (S - 1)
c, 0))
l3
max (r - S + 1, 0))
- c
- Maximum number of bytes representing each character
- When utf-8 is specified for the character code type in the pdntenv command (pdsetup command in the case of UNIX), the following specification is used:
- pd_substr_length in the system common definition
- PDSUBSTRLEN in the client environment definition
- SUBSTR LENGTH in the SQL compile option
- Note that the value is 2 when the character code type is not utf-8.
: Not applicable.
Note: When value-expression-1 is a variable-length character string, value-expression-3 is omitted, and l2 or l4 is 0, the result will be the null value.
(d) Example
Retrieve the rows in which the two characters in column C1 (CHARACTER data type) of table T1 beginning with the second character are 95:
SELECT C1 FROM T1
WHERE SUBSTR(C1,2,2)='95'
![[Figure]](figure/zu2s0270.gif)
(22) TIME
(a) Function
Converts the character string representation of time in a specified format into time data.
(b) Format
TIME (value-expression[,datetime-format])
(c) Rules
- The following items can be specified as the value expression:
- Literals that are character string expressions of the time
- CURRENT_TIME
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Time operations that produce results that are of the time data type
- Concatenation operations
- Set functions (MAX, MIN)
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The value expression should be in one of the following data types:
- A datetime format specified:
Character data type (CHAR, VARCHAR) or mixed character data type (MCHAR, MVARCHAR) of a definition length of 6 to 255
- A datetime format not specified:
Character data type (CHAR, VARCHAR) or time data type (TIME) of a definition length of 8 bytes
- The value expression should be the character string representation of time in the format specified in datetime-format. If datetime-format is omitted, the predefined character string representation of time should be used.
Examples:
Datetime format 'HH-MI-SS'
'13-45-17'
Datetime format omitted
'13:45:17'
- If the value expression is of the time data type, the result will be the equivalent time.
- For datetime formats, see 1.10 Specifying a datetime format.
- The data type of the result is the time data type (TIME).
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression or the datetime format is the null value, the result also is the null value.
(d) Example
- Obtains the difference between the earliest time and the latest time in column C1 (data type: CHAR) of table T1:
SELECT MAX(TIME(C1))-MIN(TIME(C1))
FROM T1
![[Figure]](figure/zu2s0280.gif)
- Obtains time data from a character string in column C1 (data type: CHAR) of table T2, expressed in a format ('HHMISS') other than the predefined character string representation of time:
SELECT TIME(C1,'HHMISS') FROM T2
![[Figure]](figure/zu2s0520.gif)
(23) TIMESTAMP
(a) Function
- Converts the predefined character expression of a time stamp into time stamp data.
- Converts the cumulative number of days from January 1, year 1 (A.D.) into the equivalent time stamp data.
- Converts date data and time data into time stamp data that is the combination of the two data items.
(b) Format
- Format of function 1:
TIMESTAMP(value-expression) |
- Format of function 2:
TIMESTAMP(value-expression) |
- Format of function 3:
TIMESTAMP(value-expression-1, value-expression-2) |
(c) Rules for function 1
- The following items can be specified in value-expression:
- Time stamp literals in predefined character string representation
- CURRENT_TIMESTAMP[(p)]
- Column specification
- Component specification
- SQL variables or SQL parameters
- Concatenation operation
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function calls
- Scalar subquery
- In value-expression, specify a character data type (CHAR, VCHAR) with a definition length of 19-26 bytes, or the time stamp data type (TIMESTAMP).
- When specifying a character data type in value-expression, specify the predefined character string representation of a time stamp.
- If value-expression is of the time stamp data type, the result is that time stamp.
- The data type of the result is the time stamp data type (TIMESTAMP) taking the following fractional second precision:
- If value-expression is of the character data type, its fractional second precision is based on the predefined character string representation of the time stamp of the value-expression.
- If value-expression is the time stamp data, its fractional second precision is that of the time stamp data.
(d) Rules for function 2
- The following items can be specified in value-expression:
- Numeric literals
- Column specification
- Component specification
- SQL variables or SQL parameters
- Arithmetic operations
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function calls
- Scalar subquery
- The data type of value-expression should be an integer (INTEGER). When specifying an arithmetic operation, a scalar function, a CAST expression, a CASE specification, a function call, or a set function, ensure that the result of the operation is an integer data type.
- The range of value-expression should be 1 to 3,652,059.
- The result is a time stamp after (specified-numeric-value - 1) since January 1, year 1 (A.D.). The time part of the result is 0:0:0.
Example:
If value-expression is 35
February 4, year 1 (A.D.), 0:0:0.
(e) Rules for function 3
- The following items can be specified in value-expression-1:
- Predefined character string representation literals of dates
- CURRENT_DATE
- Column specification
- Component specification
- SQL variables or SQL parameters
- Date operations producing operation results that are of the date data type
- Concatenation operation
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function calls
- Scalar subquery
- The data type of value-expression-1 should be either a character data type (CHARACTER, VARCHAR) of a length of 10 bytes, or the date data type (DATE).
- In value-expression-1, specify the predefined character string representation of a date.
Example:
'1995-06-30'
- The following items can be specified in value-expression-2:
- Predefined character string representation literals of time
- CURRENT_TIME
- Column specification
- Component specification
- SQL variables or SQL parameters
- Time operations producing results that are time data
- Concatenation operation
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function calls
- Scalar subquery
- The data type of value-expression-2 should be a character data type (CHARACTER, VARCHAR) of a length of 8 bytes, or the time data type (TIME).
- In value-expression-2, specify the predefined character string representation of time.
Example:
'13:45:17'
(f) Common rules
- The data type of the result is of the time stamp data type (TIMESTAMP).
- The value of the result is not NOT NULL constrained (the null value is allowed). If the value expression is the null value, the result also is the null value.
(g) Examples
- From column C1 (data type: time stamp data type) in table T1, retrieve data occurring since a specified time stamp:
SELECT C1 FROM T1
WHERE C1 >= TIMESTAMP('2000-01-01 00:00:00.00')
![[Figure]](figure/zu2s0530.gif)
- Convert column C1 (data type: numeric data type) in table T1 into time stamp data and insert the results into column C1 (data type: time stamp data type) in table T2:
INSERT INTO T2(C1) SELECT TIMESTAMP(C1) FROM T1
![[Figure]](figure/zu2s0540.gif)
- Combine the data from column C1 (data type: date data type) in table T1 with the data from column C2 (data type: time data type) in table T1, and insert the results into column C1 (data type: time stamp data type) in table T2:
INSERT INTO T2(C1) SELECT TIMESTAMP(C1,C2) FROM T1
![[Figure]](figure/zu2s0550.gif)
(24) TIMESTAMP_FORMAT
(a) Function
Converts the character string representation of a time stamp based on a specified datetime format into time stamp data.
(b) Format
TIMESTAMP_FORMAT(value-expression, datetime-format) |
(c) Rules
- The following items can be specified in value-expression:
- Character string representation literals of time stamps
- Column specification
- Component specification
- SQL variables or SQL parameters
- Concatenation operation
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function calls
- Scalar subquery
- In value-expression, specify a character data type (CHARACTER, VARCHAR) of a definition length of 14 to 255 bytes, or a mixed character data type (MCHAR, MVARCHAR).
- In value-expression, specify the character string representation of a time stamp in a format specified in the datetime format.
Example:
Datetime format 'YYYY/MM/DD HH-MI-SS.NNNN':
->'2002/06/30 10-45-30.1523'
- For datetime formats, see 1.10 Specifying a datatime format.
- The data type of the result is of the time stamp data type (TIMESTAMP) with a fractional second precision of 6.
(d) Common rules
- The value of the result is not NOT NULL constrained (the null value is allowed). If the value expression or the datetime format is the null value, the result also is the null value.
(e) Example
Obtain time stamp data from a character string in column C1 (data type: CHAR) in table T1, represented in a format ('DD/MON/YYYY HH-MI-SS NNNN') other than the predefined character string representation of a time stamp:
SELECT TIMESTAMP_FORMAT(C1,'DD/MON/YYYY HH-MI-SS NNNN')
FROM T1
![[Figure]](figure/zu2s0560.gif)
(25) UPPER
(a) Function
Converts the lowercase alphabetic characters in character data, national character data, or mixed character data into uppercase.
(b) Format
UPPER (value-expression)
(c) Rules
- The following items can be specified as a value-expression:
- Literals
- USER
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Concatenation operations
- Set functions
- Scalar functions (HEX, LOWER, SUBSTR, UPPER)
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- NULL, embedded variables, or the ? parameter cannot be specified in the value expression.
- The data type of the value expression should be the character string data type (CHAR or VARCHAR), the national character string data type (NCHAR or NVARCHAR) or the mixed character string data type (MCHAR or MVARCHAR).
- The execution result inherits the data type and the data length of value expression.
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
(26) VALUE
(a) Function
The VALUE scalar function extracts the value indicated by the first non-null value expression from a list of value expressions.
(b) Format
VALUE (value-expression [, value-expression]...)
(c) Rules
- The following items can be specified as a value expression:
- Literals
- USER
- CURRENT_DATE
- CURRENT_TIME
- CURRENT_TIMESTAMP[(p)]
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Arithmetic operations
- Date operations
- Time operations
- Concatenation operations
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- ? parameters or embedded variables
- Function call
- Scalar subquery
- The maximum allowable number of value expressions is 255.
- The VALUE scalar function cannot be specified for a value expression if execution of the value expression produces any of the following data types:
- BLOB
- BINARY with a minimum length of 32,001 bytes
- BOOLEAN
- Abstract data type
- NULL cannot be specified in a value expression.
- The ? parameter or an embedded variable cannot be specified alone in the first value expression (including specification in monomial operational expressions).
- All the value expressions must have data types that are compatible for comparison purposes.
Example: If one value expression is the CHAR data type, all other value expressions must also be the CHAR data type.
For data types that can be compared, see 1.2 Data types.
The following data types cannot be compared:
- Date data and a character string expression of date data
- Time data and a character string expression of time data
- Time stamp data and the character string representation of time stamp data
- Date interval data and a decimal expression of date interval data
- Time interval data and a decimal expression of time interval data
- Binary data and hexadecimal character string literals
- If one or more value expressions of VALUE are ? parameters or embedded variables, the data types of the ? parameters or embedded variables will be assumed to be the same as in the first value expression.
- The list of value expressions is evaluated sequentially from left to right. The first value that is not the null value is taken as the result.
- The data type and the data length of the result are the same as data type and data length of the result of a set operation (UNION ALL or EXCEPT ALL). For details, see 2.2 Query expressions.
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
(d) Example
Extract the null value from column C2 of table T5, and assign the value 0:
SELECT VALUE(C1,C2,C3,0)
FROM T5
![[Figure]](figure/zu2s0290.gif)
* Null value
(27) VARCHAR_FORMAT
(a) Function
Converts date data, time data, or time stamp data into a character string representation according to a specified datetime format.
(b) Format
VARCHAR_FORMAT(value-expression, datetime-format) |
(c) Rules
- The following items can be specified in value-expression:
- CURRENT_DATE
- CURRENT_TIME
- CURRENT_TIMESTAMP[(p)]
- Column specification
- Component specification
- SQL variables or SQL parameters
- Date operations producing results that are of the date data type
- Time operations producing results that are of the time data type
- Set functions
- Scalar functions
- CASE expressions
- CAST specification
- Function calls
- Scalar subquery
- The data type of value-expression should be the date data type (DATE), time data type (TIME), or time stamp data type (TIMESTAMP).
- For datetime formats, see 1.10 Specifying a datetime format.
- The results take the following data types:
- The datetime format is a character data type (CHAR or VARCHAR):
- VARCHAR(n)
- The datetime format is a mixed character data type (MCHAR or MVARCHAR):
- MVARCHAR(n)
The definition length n takes the following values:
If the value expression is specified as a non-literal value and the datetime format is specified in a literal, the definition length is the maximum length of the character string that can be converted according to a specified format.
If the value expression is specified as a literal and the datetime format is specified in a literal, the definition length is equal to the length of the character string that is converted according to the format.
If the datetime format is specified in a non-literal item, the definition length is equal to definition-length + 15 of the data type of the datetime format.
- The value of the result is the predefined character string representation of the data type of the value expression.
- The value of the result is not NOT NULL constrained (the null value is allowed). If the value expression or the datetime format is the null value, the result also is the null value.
(d) Example
From column C1 (data type: DATE) in table T1, obtain a result in a character string represented in a specified datetime format ('DD/MON/YYYY'):
SELECT VARCHAR_FORMAT(C1,'DD/MON/YYYY') FROM T1
![[Figure]](figure/zu2s0500.gif)
(28) YEAR
(a) Function
Extracts the year part from date data, time stamp data, or date interval data.
(b) Format
YEAR (value-expression)
(c) Rules
- The following items can be specified in value-expression:
- CURRENT_DATE
- CURRENT_TIMESTAMP[(p)]
- Column specifications
- Component specification
- SQL variables or SQL parameters
- Date operations
- Set functions (MAX, MIN)
- Scalar functions
- CASE expressions
- CAST specification
- Function call
- Scalar subquery
- The data type of value-expression should be the date data type (DATE), time stamp data type (TIMESTAMP) or date interval data type (INTERVAL YEAR TO DAY).
- The data type of the result is integer (INTEGER).
- If value-expression is of the date data type or the time stamp data type, the result is 1 to 9999.
- If the value expression is of the date interval data type, the result will be in the range -9999 to 9999. If the result is non-zero, the result has the same sign as the value expression.
- The value of the result is not NOT NULL constrained (null values are allowed). Therefore, if the value expression is the null value, the result is also the null value.
(d) Example
Obtain the year of column C1 (date data type) for which the value of column C2 (INTEGER data type) of table T1 is 221140; because column C1 expresses the end of a year, display the result as the year that is one year prior to the year indicated by column C1:
SELECT SUBSTR(DIGITS(YEAR(C1)-1),7,4),N'YEAR' FROM T1
WHERE C2=221140
![[Figure]](figure/zu2s0300.gif)