英文:
Chaining WHEN clauses in Teradata SQL
问题
在Teradata中，你可以将多个WHEN子句链接在一起吗？如果可以，最佳方法是什么？还有没有比链接WHEN更好的替代方法？
我有四个0或1的标志字段。我想生成一个新字段，当所有四个字段都为0时，该字段为1；当其中任何一个字段为1时，该字段为0。
我最初的想法是需要一个类似以下的CASE语句：
(CASE
WHEN field1 = 0
AND field2 = 0
AND field3 = 0
AND field4 = 0
THEN 1 ELSE 0
END) AS field5
然而，当我尝试这样做时，我收到以下错误信息：
[Teradata Database] [3706] 语法错误：期望在'AND'关键字和'WHEN'关键字之间有某些内容。
我正在尝试的目标是否可行，还是有不同的解决方法？
英文:
Is it possible to chain WHEN clauses together in Teradata? If so, what is the best method to do so? Is there a better alternative to chained WHEN?
I have four 0-or-1 flag fields. I want to generate a new field that is a 1 when all four fields are 0; and a 0 when any of those four fields is a 1.
My initial thought is that I'll need to have a CASE statement something like the following:
(CASE
WHEN field1 = 0
AND WHEN field2 = 0
AND WHEN field3 = 0
AND WHEN field4 = 0
THEN 1 ELSE 0
END) AS field5
When I try this, however, I get:
> [Teradata Database] [3706] Syntax error: expected something between
> the 'AND' keyword and the 'WHEN' keyword.
Is what I am trying to accomplish possible, or is there a different way to tackle the issue?
答案1
得分: 1
正确的语法应该是：
CASE
WHEN field1 = 0 AND field2 = 0 AND field3 = 0 AND field4 = 0 THEN 1
ELSE 0
END AS field5
话虽如此，对于给定的示例，您可以简单地编写：
CASE
WHEN GREATEST(field1, field2, field3, field4) = 0 THEN 1
ELSE 0
END AS field5
英文:
The correct syntax would be:
CASE
WHEN field1 = 0 AND field2 = 0 AND field3 = 0 AND field4 = 0 THEN 1
ELSE 0
END AS field5
That being said, for the given example you can simply write:
CASE
WHEN GREATEST(field1, field2, field3, field4) = 0 THEN 1
ELSE 0
END AS field5
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论