Re: using alias in where clause

[Larry Martell]

On Tue, Jan 26, 2016 at 8:40 AM, Hal.sz S.ndor  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.

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

Re: using alias in where clause

[shawn l.green]

On 1/28/2016 1:14 PM, Larry Martell wrote:

On Tue, Jan 26, 2016 at 8:40 AM, Hal.sz S.ndor  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

Re: using alias in where clause

[shawn l.green]

On 1/28/2016 3:32 PM, Larry Martell wrote:

On Thu, Jan 28, 2016 at 2:13 PM, shawn l.green  wrote:



On 1/28/2016 1:14 PM, Larry Martell wrote:


On Tue, Jan 26, 2016 at 8:40 AM, Hal.sz S.ndor  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

Re: using alias in where clause

[Larry Martell]

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

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

Re: using alias in where clause

[Larry Martell]

On Thu, Jan 28, 2016 at 5:45 PM, shawn l.green  wrote:
>
>
> On 1/28/2016 3:32 PM, Larry Martell wrote:
>>
>> On Thu, Jan 28, 2016 at 2:13 PM, shawn l.green 
>> wrote:
>>>
>>>
>>>
>>> On 1/28/2016 1:14 PM, Larry Martell wrote:


 On Tue, Jan 26, 2016 at 8:40 AM, Hal.sz S.ndor  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