VALUES

 

VALUES

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.

Rows and Columns

The examples below use values that are strings consisting of a single character, such as the letter a or b.

 

VALUES ('a');

 

il_values01_01.png

A single value within parentheses results in a table with one row and a single column named result by default.

 

VALUES ('a', 'b', 'c');

 

il_values01_02.png

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');

 

il_values01_03.png

 

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'));

 

il_values01_04.png

 

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');

 

il_values01_05.png

 

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.

 

AS clause

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');

 

il_values01_06.png

 

VALUES ('Tom', 'Jane', 'Alex') AS (Masonry, Plumbing, Electrical);

 

il_values01_07.png

 

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]);

 

Expressions within VALUES

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

 

VALUES (GeocodeAddress([Gcode], 'Chicago'));

 

VALUES for General Purpose Evaluation

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 lon/lat coordinates to Orthographic:

 

--SQL

VALUES (CoordConvertPoint(CALL MakeCoordConverter(

    CoordSystemDefault(),        -- to: orthographic

    CoordSystemDefaultLatLon()), -- from: lat/lon

  MakeX2(5, 15)))                --   lon=5, lat=15

-- 536949.7, 1650783.3

 

Computing the distance between meridian 5 and meridian 6 at a latitude of 15.  Manifold reports 107 km, which seems right:

 

--SQL

FUNCTION OrthoFromLatLon(lon FLOAT64, lat FLOAT64) FLOAT64X2 AS

  CoordConvertPoint(CALL MakeCoordConverter(

    CoordSystemDefault(),

    CoordSystemDefaultLatLon()), MakeX2(lon, lat))

END

VALUES (GeomDistancePoint(OrthoFromLatLon(5, 15), OrthoFromLatLon(6, 15)))

-- 107029.9

 

Convert coordinate system while preserving the value of height:

 

--SQL

VALUES (CoordConvertPoint3(CALL MakeCoordConverter(

    CoordSystemDefault(),        -- to: orthographic

    CoordSystemDefaultLatLon()), -- from: lat/lon

  MakeX3(5, 15, 1000)))          --   lon=5, lat=15, height=1000 meters

-- 536949.7, 1650783.3, 1000

 

Convert the datum, which results in adjustment of all longitude, latitude and height values:

 

--SQL

VALUES (CoordConvertPoint3(CALL MakeCoordConverter(

    CoordSystemParse('CRS:27'),  -- to: lat/lon (NAD27)

    CoordSystemDefaultLatLon()), -- from: lat/lon (WGS84)

  MakeX3(5, 15, 1000)))          --   lon=5, lat=15, height=1000 meters

-- 4.998, 14.999, 895.1

 

Latitude Longitude projection converted to Mercator:

 

--SQL

VALUES (CoordConvertPoint3(CALL MakeCoordConverter(

    '{ "System": "Mercator" }',

    '{ "System": "Latitude \\/ Longitude" }'),

  MakeX3(5, 15, 1000)))

-- 556597.5, 1678147.5, 1000

 

Latitude Longitude projection converted to Mercator and in addition with use of scale and swapping of coordinates:

 

--SQL

VALUES (CoordConvertPoint3(CALL MakeCoordConverter(

    '{ "System": "Mercator", ' &

       '"LocalScaleX": 1000, ' &

       '"LocalScaleY": 1000, ' & -- we want kilometers

       '"Axes": "YXH" }',        -- and YX instead of XY

    '{ "System": "Latitude \\/ Longitude" }'),

  MakeX3(5, 15, 1000)))

-- 1678.1, 556.6, 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 MakeCoordConverter(

    '{ "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

  MakeX3(5, 15, 1000)))

-- 1689.2, 556.6, 2439.2

 

Notes

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.  

 

 

See Also

Queries

 

Command Window

 

Query Builder

 

SQL Statements

 

GROUP BY

 

SPLIT

 

UNION / EXCEPT / INTERSECT