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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With