Re: using alias in where clause

On Tue, Jan 26, 2016 at 8:40 AM, Hal.sz S.ndor <[email protected]> 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