Implementing a Case Statement in U-SQL

Question

Wondering if anyone has suggestions on implementing a case or IF statement with U-SQL. For example, how do i convert this:

SELECT
    FirstName, LastName,
    Salary, DOB,
    CASE Gender
        WHEN 'M' THEN 'Male'
        WHEN 'F' THEN 'Female'
    END
FROM Employees;

score 5 · Answer 1 · answered May 16 '16 at 15:15

You can use an inline C# expression for simple things like so (did not test it yet though)

SELECT
    FirstName, LastName,
    Salary, DOB,
    Gender == "Male" ? "M" : "F"
FROM Employees

If it is more complex consider writing a user defined operator in C#.
Have a look at the MSDN tutorial here

score 3 · Answer 2 · edited Feb 14 '17 at 20:20

3

An example of using ?: in lieu of CASE is available at the official U-SQL Language Reference site under Common SQL Expressions in U-SQL.

edited Feb 14 '17 at 20:20

ravuya

8,586
4
30
33

answered Feb 14 '17 at 19:57

David Paul Giroux

657
4
10

score 2 · Answer 3 · answered Aug 24 '18 at 00:12

U-SQL supports the ANSI CASE expression as per the release notes here, since Spring 2018. A simple example:

DECLARE @outputFile string = @"\output\output.csv";

@Employees =
    SELECT *
    FROM (
        VALUES
        ( "w", "Bob", 1999, "31/12/1999", "M" ),
        ( "Sheena", "Easton", 1999, "31/12/1998", "F" )
        ) AS Employees(FirstName, LastName, Salary, dob, Gender);


@output =
    SELECT FirstName,
           LastName,
           Salary,
           dob,
           CASE Gender
               WHEN "M" THEN "Male"
               WHEN "F" THEN "Female"
           END AS Gender
    FROM @Employees;


OUTPUT @output
TO @outputFile
USING Outputters.Csv(quoting : false);

score 1 · Answer 4 · answered Jul 15 '16 at 23:19

1

The solution above is correct. But I'd prefer to use IF instead inline C# code.

SELECT
    FirstName, LastName,
    Salary, DOB,
    IF(Gender == "Male", "M", "F") AS Gender
FROM Employees

answered Jul 15 '16 at 23:19

Dmitry Petrov

1,490
1
19
34

score 1 · Answer 5 · answered Aug 23 '18 at 06:08

1

SELECT
FirstName, LastName,
Salary, DOB,
(Gender == 'M') ? 'Male'
(Gender == 'F') ? 'Female' AS Gender
FROM Employees;

This will Work on your Case

answered Aug 23 '18 at 06:08

Bala cse

119
1
2

score 0 · Answer 6 · answered Sep 03 '18 at 08:56

As other answers did not provide the exact equivalent, I am providing the same below.

The corresponding expression in U-SQL is. We have to use conditional expression (?:). For more information, you can refer to below links:

C# Expressions for ?:
TSQL and USQL equivalents

SELECT
    FirstName, LastName,
    Salary, DOB,
    (Gender == "M") ? "Male" : (Gender == "F") ? "Female" : "Unknown" AS Gender
FROM Employees;

Implementing a Case Statement in U-SQL

6 Answers6