adding a variable into a CASE statement THEN output

Question

If I have the following code in Impala:

(CASE 
  WHEN a3.shipped_qty1 > a4.shipped_qty2 AND a1.min1 < a2.min2 THEN "KEEP"
  WHEN a3.shipped_qty1 < a4.shipped_qty2 AND a1.min1 < a2.min2 THEN "MOVE"
  WHEN a3.shipped_qty1 > a4.shipped_qty2 AND a1.min1 > a2.min2 THEN "MOVE"
  WHEN a3.shipped_qty1 < a4.shipped_qty2 AND a1.min1 > a2.min2 THEN "KEEP"
  ELSE "NOT SHIPPING"
END) AS move

and I want the text instead to output something like this:

(CASE 
  WHEN a3.shipped_qty1 > a4.shipped_qty2 AND a1.min1 < a2.min2 THEN "KEEP"
  WHEN a3.shipped_qty1 < a4.shipped_qty2 AND a1.min1 < a2.min2 THEN "MOVE TO **a3.supplier**"
  WHEN a3.shipped_qty1 > a4.shipped_qty2 AND a1.min1 > a2.min2 THEN "MOVE TO **a4.supplier**"
  WHEN a3.shipped_qty1 < a4.shipped_qty2 AND a1.min1 > a2.min2 THEN "KEEP"
  ELSE "NOT SHIPPING"
END) AS move

how do I code that?

Answer 1

You can use CONCAT() to build the output string:

(CASE 
  WHEN a3.shipped_qty1 > a4.shipped_qty2 AND a1.min1 < a2.min2 THEN "KEEP"
  WHEN a3.shipped_qty1 < a4.shipped_qty2 AND a1.min1 < a2.min2 THEN concat("MOVE TO **",coalesce(a3.supplier,''),"**")
  WHEN a3.shipped_qty1 > a4.shipped_qty2 AND a1.min1 > a2.min2 THEN concat("MOVE TO **",coalesce(a4.supplier,''),"**")
  WHEN a3.shipped_qty1 < a4.shipped_qty2 AND a1.min1 > a2.min2 THEN "KEEP"
  ELSE "NOT SHIPPING"
END) AS move

P.S. I also wrapped the supplier values in a COALESCE() since I don't know if those columns can be null, otherwise the query would fail if those values are null.