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>
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.
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.
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]) ]]
);
It can be tempting to use short query parameters or global values (using the VALUE statement) such as @t to avoid keyboarding long table names or in big queries with multiple statements on multiple tables to avoid tedious keyboarding or copying/pasting of table names. However, using query parameters or global values for table names works only in statements like SELECT, INSERT, and DELETE (SQL's "data manipulation language", DML) but not in statements that create tables or change table schemas such as CREATE, ALTER, and DROP (SQL's "data definition language" or DDL).
One of the reasons for that limitation is that the Manifold query engine compiles the whole query before it begins execution, but it cannot do that in cases where tables either do not exist at the beginning of the query or change within the query. So, for example, the following SQL will not work:
VALUE @t TABLE = [real_table];
DROP TABLE @t;
The best way to write a very large query with multiple statements (that might include many CREATE, ALTER, or DROP statements) on multiple tables without resorting to copying and pasting table names is to generate the query using a script. If there is only one table involved in the query, the best way is to write the query using the literal name of the table. That is easy to do by writing the query using a short abbreviation and then searching and replacing that short abbreviation with the full table name.
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.