how to write case with when condition in spark sql using scala

Question

SELECT c.PROCESS_ID, 
       CASE WHEN c.PAYMODE = 'M' 
           THEN 
               CASE WHEN CURRENCY = 'USD' 
                   THEN c.PREMIUM * c.RATE 
                   ELSE c.PREMIUM END * 12
           ELSE 
               CASE WHEN CURRENCY = 'USD' 
                   THEN c.PREMIUM * c.RATE 
                   ELSE c.PREMIUM END END VAlue
FROM CMM c

i want to convert sql query spark sql api how can i do?

thanks

Answer 1

If you are looking for the way to do this using Column objects, you can do a literal translation like this:

val df: DataFrame = ...

df.select(
  col("PROCESS_ID"),
  when(col("PAYMODE") === lit("M"),
    (when(col("CURRENCY") === lit("USD"), col("PREMIUM") * col("RATE"))
    .otherwise(col("PREMIUM"))) * 12
  ).otherwise(
    when(col("CURRENCY") === lit("USD"), col("PREMIUM") * col("RATE"))
    .otherwise(col("PREMIUM"))
  )
)

Probably a cleaner way to do it, however, is to do something like:

df.withColumn(
"result",
  when(col("CURRENCY") === lit("USD"), col("PREMIUM") * col("RATE"))
    .otherwise(col("PREMIUM"))
).withColumn(
  "result",
  when(col("PAYMODE") === lit("M"), col("result") * 12)
    .otherwise(col("result"))
)

At least, the second one is a lot easier to read to me.