SCALAR

SCALAR(query_type_value)

SCALAR(select_statement, QUERY(field_definition))

  field_definition:
    field_name data_type [data_type_modifier]

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.

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.

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

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