SCALAR
The SCALAR
function executes a query that returns a single value.
Syntax
SCALAR(query_type_value) SCALAR(select_statement, QUERY(field_definition)) field_definition: field_name data_type [data_type_modifier]
Arguments
query_type_value
A query type value.
field_definition
One field_name
and one data_type
must be specified.
field_name
The name of the field.
data_type
Any scalar-valued data type. For a complete list of data types, see the Data Types topic.
data_type_modifier
Optional.
If not present or NULL
, indicates that the one row, one column value can be NULL
.
If NOT NULL
, indicates that the one row, one column value cannot be NULL
.
select_statement
A string containing a SELECT
statement that returns one row with one column.
Remarks
The examples above use a QUERY
with the field definition a INT
. When used with SCALAR
, the QUERY
may only define one field, whose name doesn’t affect SCALAR
’s behavior.
Example 1: Using SCALAR
with Static Queries
The procedure in the following example retrieves the maximum value from the single column table.
DROP DATABASE IF EXISTS singlestore_docs_example; CREATE DATABASE singlestore_docs_example; USE singlestore_docs_example; CREATE TABLE t(a INT); CREATE TABLE output_log(msg TEXT); INSERT INTO t VALUES (1),(2),(3),(4),(5); DELIMITER // CREATE PROCEDURE p() AS DECLARE q QUERY(a INT) = SELECT MAX(a) FROM t; v INT; BEGIN v = SCALAR(q); INSERT INTO output_log VALUES (CONCAT('max value is ', v)); END // DELIMITER ; CALL p(); SELECT * FROM output_log;
Output:
+----------------+ | msg | +----------------+ | max value is 5 | +----------------+
Example 2: Using SCALAR
with Dynamic Queries
The following procedure finds the maximum value of a single column table, using a dynamic query.
DROP DATABASE IF EXISTS singlestore_docs_example; CREATE DATABASE singlestore_docs_example; USE singlestore_docs_example; CREATE TABLE t1(a INT); CREATE TABLE t2(a INT); CREATE TABLE output_log(msg TEXT); INSERT INTO t1 VALUES (1), (2), (3); INSERT INTO t2 VALUES (4), (5), (6); DELIMITER // CREATE PROCEDURE p(tbl VARCHAR(30)) AS DECLARE v INT; BEGIN v = SCALAR(CONCAT('SELECT MAX(a) FROM ', tbl), QUERY(a INT)); INSERT INTO output_log VALUES (CONCAT('max value is ',v)); END // DELIMITER ; CALL p('t1'); SELECT * FROM output_log;
Output:
+----------------+ | msg | +----------------+ | max value is 3 | +----------------+
Related Topics
QUERY: A data type representing a
SELECT
statement.