From: Dmitry Y. <fir...@ya...> - 2007-02-05 19:10:28
|
Leyne, Sean wrote: > > Consider you have a TV Program which has a number of title/name fields. > Each of the fields are intended to represent slightly different/shorter > versions of the title, to meet certain equipment/interface requirements. > But instead of assigning title values to all the fields, you are using > the logic which 'looks up' the fields as follows: > > COALESCE( Short-title, medium-title, long-title) > > Thus, if the Short Title is defined/assigned, it will be used. > Else, if the Medium Title is assigned, it will be used... > > But as you can see, the logic will breakdown when the short or medium > title is an empty string. You just should be storing NULLs instead of empty strings there. The next thing you will ask for would be a non-standard concatenation operator and so on :-) > > Coalesce(NullIf(Field1, ''), Field2) > > As you can see, this is not the logic which we looking for. Why not? COALESCE(NULLIF(short-title, ''), NULLIF(medium-title, ''), NULLIF(long-title, '')) Dmitry |