英文:
Case when for statement with multiple grouped conditions converted from Pyspark
问题
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)
to
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.
英文:
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)
to
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.
答案1
得分: 1
在SQL中，IN需要一个元素列表，所以你需要在元素周围加上括号。
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
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论