SPARK SQL: Implement AND condition inside a CASE statement

Question

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;

Answer 1

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