Two conditions failure in teradata case - NOT NULL AND NOT 0-CodePudding

Trying to return a column, giving 1, when column1 is NOT NULL and different than 0. So far managed to do this:

MAX(CASE WHEN column1 IS NOT NULL 
        THEN CASE WHEN column1 <> 0 THEN 1 ELSE 0 END
        ELSE 0 END)

Getting this error:

SELECT Failed. 2620: The format or data contains a bad character.

It works quite ok with NOT NULL as a single condition, though.

CodePudding user response：

I'm not sure why you need to nest anything, but it looks like you're missing the non-equality sign.

You might try,

CASE
  WHEN column1 IS NOT NULL AND column1 <> 0
  THEN 1
  ELSE 0
END

Alternatively, this will produce the same result as an OR operator, where, but CASE executes the WHEN clauses in order.

CASE
  WHEN column1 IS NOT NULL
  THEN 1
  WHEN column1 <> 0
  THEN 1
  ELSE 0
END

But in your description, it sounds like you wanted BOTH conditions to be true, so it doesn't make sense to nest or use multiple WHEN statements, because you can just connect them together with AND