Re: using alias in where clause

On 1/28/2016 3:32 PM, Larry Martell wrote:
> On Thu, Jan 28, 2016 at 2:13 PM, shawn l.green <[email protected]> wrote:
>>
>>
>> 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.
>
> Won't that find the first one of the f_tags that is not null, but not
> necessarily the one that was matched by the where clause?
>

I slightly cheated in my example.

My CASE...END was listing terms in the same order as the COALESCE() 
function you were using in the WHERE clause. The cheat was that only a 
non-null value could be TRUE. To be more accurate, I should have used
    ... WHEN f_tag_bottom IS NOT NULL THEN ...
That way you end up with a true boolean check within the CASE decision 
tree.

As the COALESCE() is testing its terms in the same sequence as the 
CASE...END, there should be no difference between the two checks.  But, 
that also adds to the maintenance cost of this query. If you should 
change the order of the f_tag checks in the COALESCE() function, you 
would need to change the CASE...END to the same sequence.

Yours,
-- 
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