Sign inTry Free

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

Last modified: April 24, 2021

Was this article helpful?

On this page

Was this article helpful?