Using PU functions within CASE WHEN statements

Hello,

I have a need to replace NULL values with 0 in order to operate on them further down the line. I know that this can be done using CASE WHEN and ISNULL, but am running into problems because my ELSE output involves a PU_SUM function:

CASE
WHEN ISNULL (PU_SUM(VBAP,“LIPS”.“LFIMG”)) = 1
THEN ‘0’
ELSE PU_SUM(VBAP,“LIPS”.“LFIMG”) * “VBAP”.“UMZIZ” * 1.0
END

This results in an error: “Operator requirements are not met Operator ‘CASE’ is not compatible with cases of type: STRING and FLOAT.”

I believe this is where a DOMAIN_TABLE application would come into play, but don’t really understand how that would apply here, and would love to understand conceptually why PU functions cannot work in CASE WHEN statements.

Thanks for any help the team can provide.

Hi @alexhellweg ,

the issue might that the result of the THEN condition is a STRING and the result of the ELSE Condition is of type FLOAT. Could you please try to remove the quotation marks in the THEN condition (‘0’ to 0).

Hopefully, that will solve your issue. Otherwise please do not hesitate to contact us again.

Best regards,

Justin

Thanks for the reply, Justin. Unfortunately that throws the same error, but it did give me the idea to try this as a test:

CASE
WHEN
ISNULL (PU_SUM(VBAP,“LIPS”.“LFIMG”)) = 1
THEN (PU_SUM(VBAP,“LIPS”.“LFIMG”) * “VBAP”.“UMZIZ” * 0) - (PU_SUM(VBAP,“LIPS”.“LFIMG”) * “VBAP”.“UMZIZ” * 1.0)
ELSE
(PU_SUM(VBAP,“LIPS”.“LFIMG”) * “VBAP”.“UMZIZ” * 1.0)
END

When replacing the 0 with a PU statement of it’s own, the formula does function, but the problem is that I need the value to be zero for further calculation, and this PU_SUM minus PU_SUM still results in a NULL.

I did eventually find a pretty messy workaround. By referencing an unrelated table/field that I know will not have null values and multiplying it by 0, I was able to return zeros:

CASE
WHEN
ISNULL (PU_SUM(VBAP,“LIPS”.“LFIMG”)) = 1
THEN (“VBAP”.“KWMENG” * 0)
ELSE
(PU_SUM(VBAP,“LIPS”.“LFIMG”) * “VBAP”.“UMZIZ” * 1.0)
END

Let me know if you can think of a cleaner way to achieve this.

Thanks!