VALUE

The VALUE statement declares global values.  It exists in two forms: the full form used to declare global values using an expression, and a short form that omits the expression.    Using VALUE to declare global values can dramatically simplify query text and make SQL way more legible.

 

VALUE @<value> <type> = <expression>

VALUE @<value> <type>

Examples

VALUE @deg2rad FLOAT64 = Pi / 180;

 

VALUE @angle FLOAT64 = 30;

 

VALUE @anglerad FLOAT64 = @angle * @deg2rad;

 

In the above, a type is required. The type can be TABLE.  A global variable can be redefined and assigned a different type and value. When this happens, previous references to the global variable will continue to use its former type and value,  similar to what happens when overriding functions.

Short form example

VALUE @name NVARCHAR;

 

The short form of VALUE takes an existing value and converts it to the specified type. If there is no value with the specified name defined, the short form defines it and sets it to a NULL value.  The short form is useful for handling query parameters.

Additional Examples

Add a computed field that uses a global value:

 

ALTER TABLE t (

  ADD d INT32

    WITH [[ VALUE @mul INT32 = 500; ]]

    AS [[ a * @mul ]]

);

 

Add a constraint that uses a global value:

 

ALTER TABLE t (

  ADD CONSTRAINT a_c

    WITH [[ VALUE @min INT32 = 0; ]]

    AS [[ a>=@min ]]

);

 

VALUE is a great way to keep queries, even short queries, well organized, clear and succinct.   Following are some examples from the Computed Fields topic, contributed by Manifold expert Riivo Kolka to the georeference.org forum.  These create Release 9 computed fields that are analogous to Release 8 intrinsic fields.  See the Computed Fields topic for additional examples.

 

Assuming we have a table called T1 that includes a geometry field called Geom, we can open a Command Window and then paste the desired query into the window.   Run the query and the computed field will be created in the table.

 

Bearing (+/- 180 degrees)

 

ALTER TABLE [T1] (

 ADD [Bearing] FLOAT64

 WITH

  [[

  VALUE @system NVARCHAR = ComponentFieldCoordSystem([T1], 'Geom');

  VALUE @measure TABLE = CALL CoordMeasureMake(@system, 'Meter', FALSE);

  VALUE @unitDeg FLOAT64 = CoordUnitScale(CoordUnitByName('Degree'));

  ]]

 AS [[ CoordMeasureBearing(@measure, [Geom]) / @unitDeg ]]

);

 

Area in Square Meters

 

ALTER TABLE [T1] (

 ADD [Area] FLOAT64

  WITH

  [[

  VALUE @system NVARCHAR = ComponentFieldCoordSystem([T1], 'Geom');

  VALUE @measure TABLE = CALL CoordMeasureMake(@system, 'Meter', FALSE);

  ]]

  AS [[ CoordMeasureArea(@measure, [Geom]) ]]

);

 

Length in Meters

 

ALTER TABLE [T1] (

 ADD [Length] FLOAT64

  WITH

  [[

  VALUE @system NVARCHAR = ComponentFieldCoordSystem([T1], 'Geom');

  VALUE @measure TABLE = CALL CoordMeasureMake(@system, 'Meter', FALSE);

  ]]

  AS [[ CoordMeasureLength(@measure, [Geom]) ]]

);

 

Radius of a Minimum Enclosing Circle - The radius of the minimum enclosing circle is a useful measure of which objects are "bigger" than others.

 

ALTER TABLE [T1] (

 ADD [Radius] FLOAT64

 WITH

 [[

 VALUE @system NVARCHAR = ComponentFieldCoordSystem([T1], 'Geom');

 VALUE @measure TABLE = CALL CoordMeasureMake(@system, 'Meter', FALSE);

    ]]

 AS [[ CoordMeasureRadius(@measure, [Geom]) ]]

);

 

Notes

Functions are their own world - Functions are their own world when it comes to parameters.   All references to parameters inside a function body are resolved to local parameters, that is, to parameters explicitly passed as arguments to that function.  

 

VALUE @deg2rad FLOAT64 = Pi / 180;

FUNCTION rad_1(@deg FLOAT64) FLOAT64 AS @deg * @deg2rad END;

 

In the above query fragment, the function rad_1 would cause an error (and shown in red text to emphasize it is incorrect) because it uses @deg2rad, a parameter that has not been declared as an argument.   

 

VALUE @deg2rad FLOAT64 = Pi / 180;

FUNCTION rad_2(@deg FLOAT64, @convert FLOAT64) FLOAT64 AS @deg * @convert END;

 

SELECT * FROM [TABLE] WHERE [angle] > rad_2(45, @deg2rad);

 

The function rad_2 will work in the query above, because all parameters used within the function are declared as arguments.

 

See Also

Queries

 

Computed Fields

 

Command Window

 

Query Builder

 

SQL Statements

 

EXECUTE

 

GROUP BY

 

SPLIT Clause

 

TABLE

 

UNION / EXCEPT / INTERSECT

 

VALUES