Speaking of "standard logic", your "answer" to the "question" actually contains a potential bug due to improper handling of NULLS; the COALASCE, as coded, may still return NULLS in the event that ALL fields contain NULLS and therefore needs an extra NULLs test: eg.

SELECT NULLIF(COALESCE( ..., ..., ...), [...]) AS ...

Or was it your intention to allow NULLs in the result in any event ? If not, let's skip any more talk about "standard developer gaussian distributions" (sic).


Why this complication?


If i allow NULLs COALESCE() and SurrogateN.myField Nullable are suffiecient.

If i not allow NULL:

solution A:

SurrogateN.myField is NOT Nullable and is filled with the default value

solution B:

... COALESCE(M,......,SurrogateN.myField,DefaultValue)