How to write the select and case statement together in scala

Tanmay 0
Viewed 57

I am new to scala, I have a below sql that needs to be converted to scala, I have pasted what I tried but I am getting an error.

SQL code:

select (jess,
mark,
timestamp1,
timestamp2,
(CASE WHEN timestamp1>timstamp2 then null else salary) as salary,
(CASE WHEN timestamp1>timstamp2 then null else manager) as manager
)

Scala code I tried:

df.select (jess,
mark,
timestamp1,
timestamp2,
salary
)
.withColumn("salary", when($"timestamp1">$"timstamp2", salary ).otherwise("null"))

Is there a different way to write this.

apache-spark scala
1 Answers

As mentioned above in comments, it will be easier with error message but what i can see for now:

  1. You have missing comma in your select after "mark" column
  2. In your example you are first doing select without "salary" column but then you are trying to use this column in when/otherwise. Include salary in first select or do the withColumn before selecting

Edit: If you want to have case/when inside select you can do it this way:

import org.apache.spark.sql.functions.{when, col}


df.select(col("jess"),
col("mark"),
col("timestamp1"),
col("timestamp2"),
when(col("timestamp1") > col("timestamp2"),salary)
      .otherwise("null").alias("salary")
)

If you want to find out more about case/when please read this: https://sparkbyexamples.com/spark/spark-case-when-otherwise-example/

M_S0
Related