U-SQL SELECT Selecting from the VALUES Table Value Constructor
Summary
U-SQL offers the ability to select from a constant value table that is generated by the Table Value Constructor expression VALUES.
Syntax
Table_Value_Constructor :=
'(' Table_Value_Constructor_Expression ')' Derived_Table_Alias.
Table_Value_Constructor_Expression :=
'VALUES' Row_Constructor_List.
Remarks
Table_Value_Constructor_Expression
The VALUES sub expression takes a list of row constructors that create a value for the column at the given position. Follow the link for more details on the VALUES expression and the row constructor list.Derived_Table_Alias
The name for the rowset and the columns is provided by the mandatory derived table alias:
Syntax
Derived_Table_Alias :=
'AS' Quoted_or_Unquoted_Identifier '(' Column_Alias_List ')'.
Column_Alias_List :=
Quoted_or_Unquoted_Identifier {',' Quoted_or_Unquoted_Identifier}.
Examples
- The examples can be executed in Visual Studio with the Azure Data Lake Tools plug-in.
- The scripts can be executed locally. An Azure subscription and Azure Data Lake Analytics account is not needed when executed locally.
The following example creates a table containing three rows with three columns. The first column is of type long, the second of type string and the third is of type DateTime. The rowset will be output into a comma-separated file.
@res = SELECT *
FROM (VALUES
(1L, "val1", new DateTime(2000,1,1))
, (2L, "val2", new DateTime(2000,1,2))
, (3L, "val3", new DateTime(2000,1,3))) AS vt(a, b, c);
OUTPUT @res
TO "/myoutput/table_value.csv"
USING Outputters.Csv();
The following example creates a table where the second integer column contains a null value. Since C# does not allow the promotion of a non-nullable type instance to its nullable type, each row constructor has to cast the values in the column that contains the null to its type’s nullable type:
@employees = SELECT * FROM (VALUES
("Rafferty", (int?) 31)
, ("Jones", (int?) 33)
, ("Heisenberg”, (int?) 33)
, ("Robinson", (int?) 34)
, ("Smith", (int?) 34)
, ("Williams", (int?) null)) AS E(EmpName, DepID);
OUTPUT @employees
TO "/myoutput/table_value_with_null.csv"
USING Outputters.Csv();