CODEWITHSUNDEEP
scalaapache-sparkapache-spark-sql
Prash
Prash

Reputation: 97

SPARK SQL: Implement AND condition inside a CASE statement

I am aware of how to implement a simple CASE-WHEN-THEN clause in SPARK SQL using Scala. I am using Version 1.6.2. But, I need to specify AND condition on multiple columns inside the CASE-WHEN clause. How to achieve this in SPARK using Scala ?

Thanks in advance for your time and help!

Here's the SQL query that I have: 

select  sd.standardizationId,
   case when sd.numberOfShares = 0 and
             isnull(sd.derivatives,0) = 0 and 
             sd.holdingTypeId not in (3,10)
        then 
             8
        else
             holdingTypeId
       end 
   as holdingTypeId 
from sd;

Upvotes: 3

Views: 5626

Answers (2)

Daniel de Paula
Daniel de Paula

Reputation: 17872

An alternative option, if it's wanted to avoid using the full string expression, is the following:

import org.apache.spark.sql.Column
import org.apache.spark.sql.functions._

val sd = sqlContext.table("sd")

val conditionedColumn: Column = when(
  (sd("numberOfShares") === 0) and
  (coalesce(sd("derivatives"), lit(0)) === 0) and
  (!sd("holdingTypeId").isin(Seq(3,10): _*)), 8
).otherwise(sd("holdingTypeId")).as("holdingTypeId")

val result = sd.select(sd("standardizationId"), conditionedColumn)

Upvotes: 0

FaigB
FaigB

Reputation: 2281

First read table as dataframe

val table = sqlContext.table("sd")

Then select with expression. There align syntaxt according to your database.

val result = table.selectExpr("standardizationId","case when numberOfShares = 0 and isnull(derivatives,0) = 0 and holdingTypeId not in (3,10) then 8 else holdingTypeId end as holdingTypeId")

And show result 

result.show

Upvotes: 2

Related Questions