Table of contents
TOC
Collapse the table of content
Expand the table of content

U-SQL SELECT Selecting from the VALUES Table Value Constructor

Michael Rys|Last Updated: 5/15/2018
|
4 Contributors

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.

Semantics of Syntax Elements

  • 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();

See Also

© 2018 Microsoft