Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
SCALAR
On this page
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_
A query type value.
field_
One field_ and one data_ must be specified.
field_
The name of the field.
data_
Any scalar-valued data type.
data_
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_
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.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() ASDECLAREq QUERY(a INT) = SELECT MAX(a) FROM t;v INT;BEGINv = 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)) ASDECLAREv INT;BEGINv = 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
SELECTstatement.
Last modified: April 24, 2021