Re: using alias in where clause

On Fri, Jan 29, 2016 at 11:15 AM, shawn l.green
<[email protected]> wrote:
>
>
> 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.

Thanks very much Shawn.

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