Web lists-archives.com

Re: using alias in where clause




On Thu, Jan 28, 2016 at 5:45 PM, shawn l.green <shawn.l.green@xxxxxxxxxx> wrote:
>
>
> On 1/28/2016 3:32 PM, Larry Martell wrote:
>>
>> On Thu, Jan 28, 2016 at 2:13 PM, shawn l.green <shawn.l.green@xxxxxxxxxx>
>> wrote:
>>>
>>>
>>>
>>> On 1/28/2016 1:14 PM, Larry Martell wrote:
>>>>
>>>>
>>>> On Tue, Jan 26, 2016 at 8:40 AM, Hal.sz S.ndor <hsv@xxxxxxxx> 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.

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