What are the challenges you face when working across database platforms? Take the survey

Substitution unexpected behaviour

For a table of Clients, using a Substitution rule I first updated all FirstName with dataset Names, First Name, Male. I followed that with a dependent Row Internal Rule to update by Salutation with:
CASE WHEN Salutation='Mr.' THEN DMSPARAM1 WHEN Salutation='M.' THEN DMSPARAM1 WHEN Salutation='Miss' THEN DMSPARAM2 WHEN Salutation='Mlle.' THEN DMSPARAM2 WHEN Salutation='Mme.' THEN DMSPARAM2 WHEN Salutation='Mrs.' THEN DMSPARAM2 WHEN Salutation='Ms' THEN DMSPARAM2 END

In this case DMSPARAM1 is the dataset Name, First Name, Male; DMSPARAM2 is Name, First Name, Female.

The result was NULL values in FirstName for the Salutations that I had overlooked: Mr and Ms.

Why is that?

Best Answer

  • Options
    patrick_jpatrick_j Posts: 13 Bronze 2
    Answer ✓
    I'm answering my own question. CASE returns NULL when no condition evaluates to True and there is no ELSE clause. The first-step conversion of all rows to Male is therefore not required when using a CASE if the ELSE evaluates as that.


Sign In or Register to comment.