On Tue, Jan 26, 2016 at 8:40 AM, Hal.sz S.ndor <h...@tbbs.net> wrote: > 2016/01/25 19:16 ... Larry Martell: >> >> SELECT IFNULL(f_tag_bottom, >> IFNULL(f_tag_bottom_major_axis, >> IFNULL(f_tag_bottom_minor_axis, >> IFNULL(f_tag_ch_x_bottom, >> IFNULL(f_tag_ch_y_bottom, NULL))))) as ftag, >> STDDEV(ch_x_top) >> .... > > Of course, this isn't your real problem, but you could use COALESCE instead > of all those IFNULLs (and you don't need the last one): > SELECT COALESCE(f_tag_bottom, f_tag_bottom_major_axis, > f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) as ftag, > STDDEV(ch_x_top) > .... > > As Johnny Withers points out, you may repeat the expression in the > WHERE-clause: > WHERE COALESCE(f_tag_bottom, f_tag_bottom_major_axis, > f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) = 'E-CD7' > If really only one of those is not NULL, it is equivalent to this: > 'E-CD7' IN (f_tag_bottom, f_tag_bottom_major_axis, > f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom)
Many thanks to Hal.sz and Johnny - I had forgotten about coalesce and I didn't know I could use that in a where clause. This worked great for the requirement I had, but of course, once that was implemented my client changed the requirements. Now they want to know which of the 5 f_tag_* columns was matched. Not sure how I'll do that. Probably need another query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
