Home Case when for statement with multiple grouped conditions converted from Pyspark

Questions

Case when for statement with multiple grouped conditions converted from Pyspark

March 3, 2023

I am converting a PySpark dataframe into SQL and am having a hard time converting

.withColumn("portalcount", when(((F.col("tCounts") == 3) & (F.col("Type1").contains("pizza"))) & ((~(F.col("Type1").contains("singleside"))) | (~(F.col("Type1").contains("side")))), 2)
           .when(((F.col("tCounts") == 3) & (F.col("Type1").contains("pizza"))) & ((F.col("Type1").contains("singleside")) | (F.col("Type1").contains("side"))), 1)

CASE
WHEN (tCounts = 3 AND Type1 IN 'pizza') AND (Type1 NOT IN 'singleside' OR Type1 NOT IN 'side') then 2
WHEN (tCounts = 3 AND Type1 IN 'pizza') AND (Type1 IN 'singleside' OR Type1 IN 'side') then 1
END portalcount

The rest of my query runs but not with the grouped case when statement. I am getting a parse syntax error. Should this be restructured as a nested case when statement? I’ve tried many versions of grouping the parentheses but I haven’t had success.

>Solution :

In SQL IN needs a list of elements, so you need parenthesis around the elements

CASE
WHEN (tCounts = 3 AND Type1 IN ('pizza')) AND (Type1 NOT IN ('singleside','side') ) then 2
WHEN (tCounts = 3 AND Type1 IN ('pizza')) AND (Type1 IN ('singleside','side') ) then 1
END portalcount