Share via

IF...ELSE (U-SQL)

  • Article

Summary

Imposes conditions on the execution of a U-SQL statement which is evaluated at compile time. The U-SQL statement that follows an IF keyword and its condition is executed if the condition is satisfied: the Boolean expression returns TRUE. The optional ELSE keyword introduces another U-SQL statement that is executed when the IF condition is not satisfied: the Boolean expression returns FALSE.

The Boolean expression has to be compile-time constant foldable, such as the value passed as a parameter.

Syntax

If_Else_Statement :=                                                                                      
    'IF' Boolean_Expression 'THEN'
         U-SQL_Statement 
    [ 'ELSEIF' Boolean_Expression 'THEN'
         U-SQL_Statement ]
    [ 'ELSE' Boolean_Expression 'THEN'
         U-SQL_Statement ]
'END'.

Remarks

  • Boolean_Expression
    Is a constant-foldable expression that returns TRUE or FALSE.

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 examples below are based on the dataset defined below. Ensure your execution includes the rowset variable.

Dataset

@employees = 
    SELECT * FROM 
        ( VALUES
        (1, "Noah",   100, (int?)10000, new DateTime(2012,05,31), "cell:030-0074321,office:030-0076545"),
        (2, "Sophia", 100, (int?)15000, new DateTime(2012,03,19), "cell:(5) 555-4729,office:(5) 555-3745"),
        (3, "Liam",   100, (int?)30000, new DateTime(2014,09,14), "cell:(5) 555-3932"),
        (4, "Amy",    100, (int?)35000, new DateTime(1999,02,27), "cell:(171) 555-7788,office:(171) 555-6750, home:(425) 555-6238"),
        (5, "Justin", 100, (int?)15000, new DateTime(2015,01,12), "cell:0921-12 34 65,office:0921-12 34 67"),
        (6, "Emma",   200, (int?)8000,  new DateTime(2014,03,08), (string)null),
        (7, "Jacob",  200, (int?)8000,  new DateTime(2014,09,02), ""),
        (8, "Olivia", 200, (int?)8000,  new DateTime(2013,12,11), "cell:88.60.15.31,office:88.60.15.32"),
        (9, "Mason",  300, (int?)50000, new DateTime(2016,01,01), "cell:(91) 555 22 82,office:(91) 555 91 99, home:(425) 555-2819"),
        (10, "Ava",   400, (int?)15000, new DateTime(2014,09,14), "cell:91.24.45.40,office:91.24.45.41"),
        (11, "Ethan", 400, (int?)9000,  new DateTime(2015,08,22), "cell:(604) 555-4729,office:(604) 555-3745"),
        (12, "David", 800, (int?)100,   new DateTime(2016,11,01), "cell:(171) 555-1212"),
        (13, "Andrew", 100, (int?)null, new DateTime(1995,07,16), "cell:(1) 135-5555,office:(1) 135-4892"),
        (14, "Jennie", 100, (int?)34000, new DateTime(2000,02,12), "cell:(5) 555-3392,office:(5) 555-7293")
        ) AS T(EmpID, EmpName, DeptID, Salary, StartDate, PhoneNumbers);

One condition

DECLARE @DeptID int = 100;

IF @DeptID == 100 THEN
    @result = SELECT * FROM @employees WHERE DeptID == 100;
END;

OUTPUT @result
TO "/Output/ReferenceGuide/Concepts/ControlOfFlow/IF/ExampleA.csv"
USING Outputters.Csv();

One condition and ELSE

DECLARE @DeptID int = 100;

IF @DeptID == 200 THEN
    @result =  SELECT * FROM @employees WHERE DeptID = 200;
ELSE 
    @result =  SELECT * FROM @employees WHERE DeptID == @DeptID;
END;

OUTPUT @result
TO "/Output/ReferenceGuide/Concepts/ControlOfFlow/IF/ExampleB.csv"
USING Outputters.Csv();

Two conditions and ELSE

DECLARE @DeptID int = 100;

IF @DeptID == 200 THEN
    @result =  SELECT * FROM @employees WHERE DeptID = 200;
ELSEIF @DeptID == 100 THEN
    @result = SELECT * FROM @employees WHERE DeptID == 100;
ELSE 
    @result =  SELECT * FROM @employees WHERE DeptID == @DeptID;
END;

OUTPUT @result
TO "/Output/ReferenceGuide/Concepts/ControlOfFlow/IF/ExampleC.csv"
USING Outputters.Csv();

Nested conditions

DECLARE @DeptID int = 100;
DECLARE @flag bool = true;

IF @DeptID == 100 
THEN
    IF @flag == true 
    THEN
        @result =  SELECT * FROM @employees WHERE DeptID == 100 AND Salary >= 30000;
    END;
ELSEIF @DeptID == 200 
THEN
    @result = SELECT * FROM @employees WHERE DeptID == 200;
ELSE 
    @result =  SELECT * FROM @employees WHERE DeptID == @DeptID;
END;

OUTPUT @result
TO "/Output/ReferenceGuide/Concepts/ControlOfFlow/IF/ExampleD.csv"
USING Outputters.Csv();

See Also