Re: using alias in where clause

On 1/28/2016 1:14 PM, Larry Martell wrote:
> 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.
>
One option to consider is to add another column to the query with a CASE 
similar to this...

SELECT
, ... original fields ...
, CASE
   WHEN f_tag_bottom THEN 'f_tag_bottom'
   WHEN f_tag_bottom_major_axis THEN 'f_tag_bottom_major_axis'
   ... repeat for the rest of the fields to test ...
   ELSE 'none'
   END as match_flag
FROM ...

Technically, the term in the WHERE clause should prevent a 'none' result 
but I put it there to help future-proof the code.

-- 
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql