Chaining WHEN clauses in Teradata SQL

Question

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?

Answer 1

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