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

Did you try registerTempTable and then sqlContext.sql("...") ? — David Griffin
– David Griffin, Commented May 6, 2016 at 16:26
hi thanks for ur reply i have done that thing but i wanna do using api so have any idea how to do it ? — praveen
– praveen, Commented May 9, 2016 at 4:01
Praveen : Can you consider accepting the @David Griffin answer. It would be pointer to other users as well.Thx — Ram Ghadiyaram
– Ram Ghadiyaram, Commented Sep 12, 2016 at 3:12

David Griffin · Accepted Answer · 2016-05-09 05:14:54Z

60

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.

answered May 9, 2016 at 5:14

David Griffin

14k5 gold badges49 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

praveen Over a year ago

Thank u very much :)

David Griffin Over a year ago

You're welcome -- feel free to accept my answer ;-)

Saurav Sahu Over a year ago

The cleaner way is not correct, as col("CURRENCY") === lit("USD") && col("PAYMODE") === lit("M") is resulting into col("PREMIUM") * col("RATE") * 12 as "result", which is not correct.

David Griffin Over a year ago

From my reading of the original SQL, that's the desired result. At the end of the "WHEN c.PAYMODE = 'M'" clause there's this: "END * 12", meaning when PAYMODE="M" the result gets multiplied by 12

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related