Re: using alias in where clause

On 1/28/2016 6:30 PM, Larry Martell wrote:
> On Thu, Jan 28, 2016 at 5:45 PM, shawn l.green <[email protected]> wrote:
>>
>>
>> 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.
>
> Yes, I see that, but does the case only look at the filtered rows? For
> example, lets say there's this data:
>
> row 1: f_tag_bottom = "ABC"
> row 2: f_tag_bottom_major_axis = "XYZ"
>
> and my where clause has this:
>
> 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) = 'XYZ'
>
> won't the CASE pick up row 1? Whereas I want it to pick up row 2.
>

Yes it would.

Just modify the check in the case from "IS NOT NULL" to "='XYZ'" to make 
both functions (CASE and COALESCE) find the same field value in the same 
row at the same time.

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