VALUES returns a table populated with the specified values. VALUES is an SQL statement that can stand alone to form a query. In what follows remember that "column" and "field" are synonyms as are "row" and "record".
VALUES (<value>, ...), ...
In the VALUES statement parentheses ( ) characters group rows, which could be a list of rows separated by commas as in ( ), ( ), ..., ( ). A list of values within a row indicate columns, as in (<value>, <value>, <value>) indicating a single row with three columns.
The examples below use values that are strings consisting of a single character, such as the letter a or b.
VALUES ('a');
A single value within parentheses results in a table with one row and a single column named result by default.
VALUES ('a', 'b', 'c');
A list of three values within the same set of parentheses ( ) results in one row with three columns within that row, named result, result 2 and result 3 by default.
VALUES ('a'), ('b'), ('c');
A list three sets of parentheses ( ) each enclosing a single value, results in a table with three rows and one column named result.
VALUES (('a'), ('b'), ('c'));
VALUES looks at the outer set of parentheses ( ) to know how many rows to create. Enclosing the previous example with an outer set of parentheses ( ) tells VALUES there is a single row. Within that single row VALUES sees a list of three expressions in the form ('a'), each of which evaluates to a single character such as a. The result is the same as
VALUES ('a', 'b', 'c');
... creating a table with one row and three columns.
VALUES ('a', 'x'), ('b', 'y'), ('c', 'z');
VALUES now sees a list of three expressions in parentheses ( ) so it knows to create three rows. Within each set of parentheses there is a list of two values, so VALUES knows to create two columns. The result is a table with three rows and two columns.
By default the VALUES statement returns a table using the default names for columns of results, results 2, results 3.... To specify different names we use the AS clause:
VALUES ('Tom', 'Jane', 'Alex');
VALUES ('Tom', 'Jane', 'Alex') AS (Masonry, Plumbing, Electrical);
The column names above do not use square [ ] brackets since the names are simple and unambiguous and brackets are thus optional. Using brackets to ensure unambiguous names, the query would be written as:
VALUES ('Tom', 'Jane', 'Alex') AS ([Masonry], [Plumbing], [Electrical]);
or
VALUES ('Tom', 'Jane', 'Alex') AS ([Column A], [Column B], [Column C]);
The examples above use string literals for simplicity. <value> can be any expression, including functions, computations and so on. For example:
PRAGMA ('custom'='abc');
VALUES ('custom', PragmaValue('custom'));
or
SELECT mfd_id, SPLIT (VALUES ('a', mfd_id), ('b', mfd_id+1)), Name FROM mfd_root;
or
FUNCTION f() TABLE AS mfd_root END;
VALUES (ComponentName( CALL f() ));
or, given a Google geocoding data source in the project named Gcode (do not forget that for this to work we need to provide our API key when creating the geocoding data source).
VALUES (GeocodeAddress([Gcode], 'Chicago'));
Because VALUES can be used as a standalone query statement it is useful as a general purpose way to evaluate <value> expressions, to do work in the Command window or within larger queries. The following uses of Values are quotations from Command Window use of VALUES to demonstrate coordinate system functions in Manifold, with comments at the end (comments in an SQL query line are preceded with -- characters) giving the results.
Re-projecting Latitude / Longitude coordinates to Pseudo-Mercator (the starting default):
--SQL
VALUES (CoordConvertPoint(CALL CoordConverterMake(
CoordSystemDefault(), -- to: pseudo-mercator
CoordSystemDefaultLatLon()), -- from: lat/lon
VectorMakeX2(5, 15))) -- lon=5, lat=15
-- 556597.4539663679, 1689200.1396078924
Computing the distance between meridian 5 and meridian 6 at a latitude of 15. Manifold reports 107 km, which seems right:
--SQL
FUNCTION PMercatorFromLatLon(@lon FLOAT64, @lat FLOAT64) FLOAT64X2 AS
CoordConvertPoint(CALL CoordConverterMake(
CoordSystemDefault(),
CoordSystemDefaultLatLon()), VectorMakeX2(@lon, @lat))
END;
VALUES (GeomDistancePoint(PMercatorFromLatLon(5, 15), PMercatorFromLatLon(6, 15)))
-- 111319.49079327355
Convert coordinate system while preserving the value of height:
--SQL
VALUES (CoordConvertPoint3(CALL CoordConverterMake(
CoordSystemDefault(), -- to: pseudo-mercator
CoordSystemDefaultLatLon()), -- from: lat/lon
VectorMakeX3(5, 15, 1000))) -- lon=5, lat=15, height=1000 meters
-- 556597.4539663679, 1689200.1396078924, 1000
Convert the datum, which results in adjustment of all longitude, latitude and height values:
--SQL
VALUES (CoordConvertPoint3(CALL CoordConverterMake(
CoordSystemParse('CRS:27'), -- to: lat/lon (NAD27)
CoordSystemDefaultLatLon()), -- from: lat/lon (WGS84)
VectorMakeX3(5, 15, 1000))) -- lon=5, lat=15, height=1000 meters
-- 4.998511736536917, 14.999549538313975, 895.165404680185
Latitude Longitude projection converted to Mercator:
--SQL
VALUES (CoordConvertPoint3(CALL CoordConverterMake(
'{ "System": "Mercator" }',
'{ "System": "Latitude \\/ Longitude" }'),
VectorMakeX3(5, 15, 1000)))
-- 556597.4539663679, 1678147.5163917786, 1000
Latitude Longitude projection converted to Mercator and in addition with use of scale and swapping of coordinates:
--SQL
VALUES (CoordConvertPoint3(CALL CoordConverterMake(
'{ "System": "Mercator", ' &
'"LocalScaleX": 1000, ' &
'"LocalScaleY": 1000, ' & -- we want kilometers
'"Axes": "YXH" }', -- and YX instead of XY
'{ "System": "Latitude \\/ Longitude" }'),
VectorMakeX3(5, 15, 1000)))
-- 1678.1475163917787, 556.5974539663679, 1000
Latitude Longitude projection converted to Mercator and in addition with use of scale and swapping of coordinates, but re-projecting from a sphere instead of WGS84. The difference at a latitude of 15 between a sphere and WGS84 really is that much, as seen by the dramatically different value in height:
--SQL
VALUES (CoordConvertPoint3(CALL CoordConverterMake(
'{ "System": "Mercator", ' &
'"LocalScaleX": 1000, ' &
'"LocalScaleY": 1000, ' & -- we want kilometers
'"Axes": "YXH" }', -- and YX instead of XY
'{ "System": "Latitude \\/ Longitude", ' &
'"Eccentricity": 0 }'), -- and lat/lon are for sphere
VectorMakeX3(5, 15, 1000)))
-- 1689.1967240635192, 556.5974539663679, 2439.248261812143
No brackets - In this topic to reduce visual clutter we do not use square [ ] brackets around simple field and table names. Square brackets are optional if the name is unambiguous.
Same number of columns in all rows - VALUES must have the same number of column values within each row. For example the query:
VALUES ('a', 'x'), ('b'), ('c', 'z');
is incorrect and will fail, because the second row, that is the second set of parentheses ( ), has only one value in it instead of two values like the other rows.