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;

Does that query work? If not, what error do you get? Don't make us guess. See minimal reproducible example — The Archetypal Paul
– The Archetypal Paul, Commented Nov 10, 2016 at 8:23

FaigB · Accepted Answer · 2016-11-10 08:31:30Z

2

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

answered Nov 10, 2016 at 8:31

FaigB

2,2811 gold badge16 silver badges22 bronze badges

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

2 Comments

Shankar Over a year ago

Just curious to know, if this is working , the above query in the question also work... right? what is the difference between these two?

FaigB Over a year ago

In first example it is used as sql which querying table and creating dataframe (If author used in same way) and it should rise parsing exception at "not in". In second example the select applied on dataframe which already created from table and there manipulations merely rises parsing exceptions depending on syntax support.

Daniel de Paula · Accepted Answer · 2016-11-10 13:01:01Z

0

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)

answered Nov 10, 2016 at 13:01

Daniel de Paula

18k9 gold badges75 silver badges73 bronze badges

Collectives™ on Stack Overflow

SPARK SQL: Implement AND condition inside a CASE statement

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related