Search code examples
azureazure-data-factoryazure-synapse

ADF Understanding the Case Statement

Given the following Derived column Expression

case(Rolling =='A'||Rolling == 'B'||Rolling == 'C'|| Rolling =="S"
     , "
     , case(Alpha== 'EE'
         , toString(toDate(Manu_Date, 'yyyy-MM-dd'))
         , case(Alpha=='CW', Del_Date,"))
)

2 questions

  1. Is there a Better way to write this code?
  2. What is this code trying to do ?

I am trying to understand what they are trying to achieve with this expression?

Solution
    • In the given expression, after Rolling=="S", it is not double Quotes ". It should be two single quotes''
    • Similarly, after Del_date, also it should be two single Quotes.
    case(Rolling =='A'||Rolling ==  'B'||Rolling ==  'C'|| Rolling =="S", '',
case(Alpha==  'EE', toString(toDate(Manu_Date, 'yyyy-MM-dd')),
case(Alpha=='CW', Del_Date,'' )))
    1. What is this code trying to do ?

    • Syntax for case statement is case(condition,true_expression,false_expression)

    • Initially, this expression checks if Rolling is 'A' or 'B' or 'C' or 'S' and then assign the value as '' (empty string) for the derived column.

    • When the above condition is false, then checks if Alpha is 'EE' and assign the value of Manu_Date in string format.

    • When the second condition also fails, it checks if Alpha='CW' and assign the value of Del_Date column.

    • When all the above conditions are not met, '' (empty string) is assigned. This will be the default value.

    • I repro'd this with sample input. enter image description here img1: input data

    • In derived column transformation, new column is added, and the expression is given as in below script.

    case(Rolling =='A'||Rolling ==  'B'||Rolling ==  'C'|| Rolling =="S", '',
case(Alpha==  'EE', toString(toDate(Manu_Date, 'yyyy-MM-dd')),
case(Alpha=='CW', Del_Date,'' )))

    enter image description here img2: Derived column transformation output

    1. Is there a Better way to write this code?
    • Since the order of condition is important to assign the values to the new column, case statement is better way to do.
    • But, instead of using nested case statements, we can use single case statement to achieve the same. Syntax: case( condition_1, expression_1, condition_2, expression_2,.......... condition_n,expression_n,default_expression). Null will be the default value, when the default expression is omitted.

    Modified expression

    case(Rolling =='A'||Rolling ==  'B'||Rolling ==  'C'|| Rolling =="S", '',
Alpha==  'EE', toString(toDate(Manu_Date, 'yyyy-MM-dd')),
Alpha=='CW', Del_Date,'' )

    enter image description here img 3: Results of both case statements

    Both the expressions are added in the derived column transformation and results are same in both cases.