Re: using alias in where clause

[shawn l.green]

On 1/28/2016 6:30 PM, Larry Martell wrote:

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.



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

Re: using alias in where clause

[Larry Martell]

On Fri, Jan 29, 2016 at 11:15 AM, shawn l.green
 wrote:
>
>
> On 1/28/2016 6:30 PM, Larry Martell wrote:
>>
>> 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.
>>
>
> 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

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

Re: using alias in where clause

[Hal.sz S.ndor]

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)


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

[Johnny Withers]

On Mon, Jan 25, 2016 at 9:32 PM, Larry Martell 
wrote:

> On Mon, Jan 25, 2016 at 8:26 PM, Johnny Withers 
> wrote:
> > You should probably turn this into a UNION and put an index on each
> column:
> >
> > SELECT f_tag_ch_y_bottom AS ftag FROM data_cst WHERE f_tag_ch_y_bottom =
> > 'E-CD7'
> > UNION ALL
> > SELECT f_tag_ch_x_bottom AS ftag FROM data_cst WHERE f_tag_ch_x_bottom =
> > 'E-CD7'
> > UNION ALL
> > SELECT f_tag_bottom_minor_axis AS ftag FROM data_cst WHERE
> > f_tag_bottom_minor_axis = 'E-CD7'
> > UNION ALL
> > SELECT f_tag_bottom_major_axis AS ftag FROM data_cst WHERE
> > f_tag_bottom_major_axis = 'E-CD7'
> > UNION ALL
> > SELECT f_tag_bottom FROM data_cst AS ftag WHERE f_tag_bottom = 'E-CD7'
> > ;
>
> This may work for me, but I need to explore this more tomorrow. I need
> the select to control the rows included in the aggregation. For
> example, the rows where the f_tag_* col that is used does not =
> 'E-CD7' should not be included in the aggregation. Also, I grossly
> simplified the query for this post. In reality I have 15 items in the
> where clause and a having as well.
>

I see, I missed the STDDEV() function you had, perhaps you could add that
column to each SELECT in the untion, then wrap the entire union inside
another select:

SELECT ftag, STDDEV(ch_x_top) FROM (
..union stuff here...
)


>
> > Doing this any other way will prevent index usage and a full table scan
> will
> > be required.
>
> Yes, I will be adding indices - right now I am just worried about
> getting the query to work. But this is important as it's a big table.
> So I do appreciate you mentioning it.
>

In reality, you could repeat the IFNULL(...) in the where clause the same
way you have it in the column list. Not the optimal solution but it'd work
for a proof of concept.


>
> > Is there a possibility of more than one column matching? How would you
> > handle that?
>
> I was told only 1 of the 5 will be populated and the other 4 will be
> null. But still, I said I have to code for the case where that is not
> true. So then I was told to use the first one I find that is not null,
> looking in the order I had in my original post.
>

You could also wrap another select around the union to handle more than one
of the columns having a value. You could use the outer select to pick the
one you wanted, something similar to:

SELECT
IFNULL(f_tag_ch_y_bottom,IFULL(f_tag_ch_x_bottom,IFNULL(,STDEV(ch_x_top)
FROM (
SELECT f_tag_ch_y_bottom,NULL as f_tag_ch_x_bottom,NULL AS
f_tag_bottom_minor,..., ch_x_top FROM data_cst WHERE f_tag_ch_y_bottom =
'E-CD7'
UNION ALL
SELECT NULL AS f_tag_ch_y_bottom, f_tag_ch_x_bottom,NULL AS
f_tag_bottom_minor,...,ch_x_top FROM data_cst WHERE f_tag_ch_x_bottom
= 'E-CD7'
UNION
)

And so on for each column/query.


>
>
> > On Mon, Jan 25, 2016 at 6:16 PM, Larry Martell 
> > wrote:
> >>
> >> I know I cannot use an alias in a where clause, but I am trying to
> >> figure out how to achieve what I need.
> >>
> >> If I could have an alias in a where clause my sql would look like this:
> >>
> >> 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)
> >> FROM data_cst
> >> WHERE ftag = 'E-CD7'
> >> GROUP BY wafer_id, lot_id
> >>
> >> But I can't use ftag in the where clause. I can't put it in a having
> >> clause, as that would exclude the already aggregated rows and I want
> >> to filter then before the aggregation. Anyone have any idea how I can
> >> do this?
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe:http://lists.mysql.com/mysql
> >>
> >
> >
> >
> > --
> > -
> > Johnny Withers
> > 601.209.4985
> > joh...@pixelated.net
>



-- 
-
Johnny Withers
601.209.4985
joh...@pixelated.net

Re: using alias in where clause

[Larry Martell]

On Mon, Jan 25, 2016 at 8:26 PM, Johnny Withers  wrote:
> You should probably turn this into a UNION and put an index on each column:
>
> SELECT f_tag_ch_y_bottom AS ftag FROM data_cst WHERE f_tag_ch_y_bottom =
> 'E-CD7'
> UNION ALL
> SELECT f_tag_ch_x_bottom AS ftag FROM data_cst WHERE f_tag_ch_x_bottom =
> 'E-CD7'
> UNION ALL
> SELECT f_tag_bottom_minor_axis AS ftag FROM data_cst WHERE
> f_tag_bottom_minor_axis = 'E-CD7'
> UNION ALL
> SELECT f_tag_bottom_major_axis AS ftag FROM data_cst WHERE
> f_tag_bottom_major_axis = 'E-CD7'
> UNION ALL
> SELECT f_tag_bottom FROM data_cst AS ftag WHERE f_tag_bottom = 'E-CD7'
> ;

This may work for me, but I need to explore this more tomorrow. I need
the select to control the rows included in the aggregation. For
example, the rows where the f_tag_* col that is used does not =
'E-CD7' should not be included in the aggregation. Also, I grossly
simplified the query for this post. In reality I have 15 items in the
where clause and a having as well.

> Doing this any other way will prevent index usage and a full table scan will
> be required.

Yes, I will be adding indices - right now I am just worried about
getting the query to work. But this is important as it's a big table.
So I do appreciate you mentioning it.

> Is there a possibility of more than one column matching? How would you
> handle that?

I was told only 1 of the 5 will be populated and the other 4 will be
null. But still, I said I have to code for the case where that is not
true. So then I was told to use the first one I find that is not null,
looking in the order I had in my original post.


> On Mon, Jan 25, 2016 at 6:16 PM, Larry Martell 
> wrote:
>>
>> I know I cannot use an alias in a where clause, but I am trying to
>> figure out how to achieve what I need.
>>
>> If I could have an alias in a where clause my sql would look like this:
>>
>> 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)
>> FROM data_cst
>> WHERE ftag = 'E-CD7'
>> GROUP BY wafer_id, lot_id
>>
>> But I can't use ftag in the where clause. I can't put it in a having
>> clause, as that would exclude the already aggregated rows and I want
>> to filter then before the aggregation. Anyone have any idea how I can
>> do this?
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:http://lists.mysql.com/mysql
>>
>
>
>
> --
> -
> Johnny Withers
> 601.209.4985
> joh...@pixelated.net

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

[Johnny Withers]

You should probably turn this into a UNION and put an index on each column:

SELECT f_tag_ch_y_bottom AS ftag FROM data_cst WHERE f_tag_ch_y_bottom =
'E-CD7'
UNION ALL
SELECT f_tag_ch_x_bottom AS ftag FROM data_cst WHERE f_tag_ch_x_bottom =
'E-CD7'
UNION ALL
SELECT f_tag_bottom_minor_axis AS ftag FROM data_cst WHERE
f_tag_bottom_minor_axis = 'E-CD7'
UNION ALL
SELECT f_tag_bottom_major_axis AS ftag FROM data_cst WHERE
f_tag_bottom_major_axis = 'E-CD7'
UNION ALL
SELECT f_tag_bottom FROM data_cst AS ftag WHERE f_tag_bottom = 'E-CD7'
;

Doing this any other way will prevent index usage and a full table scan
will be required.

Is there a possibility of more than one column matching? How would you
handle that?


On Mon, Jan 25, 2016 at 6:16 PM, Larry Martell 
wrote:

> I know I cannot use an alias in a where clause, but I am trying to
> figure out how to achieve what I need.
>
> If I could have an alias in a where clause my sql would look like this:
>
> 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)
> FROM data_cst
> WHERE ftag = 'E-CD7'
> GROUP BY wafer_id, lot_id
>
> But I can't use ftag in the where clause. I can't put it in a having
> clause, as that would exclude the already aggregated rows and I want
> to filter then before the aggregation. Anyone have any idea how I can
> do this?
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>


-- 
-
Johnny Withers
601.209.4985
joh...@pixelated.net

Re: using alias in where clause

[Reindl Harald]

Am 26.01.2016 um 01:16 schrieb Larry Martell:

I know I cannot use an alias in a where clause, but I am trying to
figure out how to achieve what I need.

If I could have an alias in a where clause my sql would look like this:

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)
FROM data_cst
WHERE ftag = 'E-CD7'
GROUP BY wafer_id, lot_id

But I can't use ftag in the where clause. I can't put it in a having
clause, as that would exclude the already aggregated rows and I want
to filter then before the aggregation. Anyone have any idea how I can
do this?


why would you want to do that?
it would make the where clause not be able to use proper indexes



signature.asc
Description: OpenPGP digital signature

Re: using alias in where clause

[Rebecca Love]

Have you tried using a select case statement for ftag?


> On Jan 25, 2016, at 6:39 PM, Larry Martell  wrote:
> 
> On Mon, Jan 25, 2016 at 7:27 PM, Reindl Harald  wrote:
>> 
>> 
>> Am 26.01.2016 um 01:16 schrieb Larry Martell:
>>> 
>>> I know I cannot use an alias in a where clause, but I am trying to
>>> figure out how to achieve what I need.
>>> 
>>> If I could have an alias in a where clause my sql would look like this:
>>> 
>>> 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)
>>> FROM data_cst
>>> WHERE ftag = 'E-CD7'
>>> GROUP BY wafer_id, lot_id
>>> 
>>> But I can't use ftag in the where clause. I can't put it in a having
>>> clause, as that would exclude the already aggregated rows and I want
>>> to filter then before the aggregation. Anyone have any idea how I can
>>> do this?
>> 
>> 
>> why would you want to do that?
>> it would make the where clause not be able to use proper indexes
> 
> I do not know which of the 5 f_tag_* columns will be populated and I
> need to filter on the one that is populated.
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
> 


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

using alias in where clause

[Larry Martell]

I know I cannot use an alias in a where clause, but I am trying to
figure out how to achieve what I need.

If I could have an alias in a where clause my sql would look like this:

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)
FROM data_cst
WHERE ftag = 'E-CD7'
GROUP BY wafer_id, lot_id

But I can't use ftag in the where clause. I can't put it in a having
clause, as that would exclude the already aggregated rows and I want
to filter then before the aggregation. Anyone have any idea how I can
do this?

-- 
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 Mon, Jan 25, 2016 at 7:27 PM, Reindl Harald  wrote:
>
>
> Am 26.01.2016 um 01:16 schrieb Larry Martell:
>>
>> I know I cannot use an alias in a where clause, but I am trying to
>> figure out how to achieve what I need.
>>
>> If I could have an alias in a where clause my sql would look like this:
>>
>> 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)
>> FROM data_cst
>> WHERE ftag = 'E-CD7'
>> GROUP BY wafer_id, lot_id
>>
>> But I can't use ftag in the where clause. I can't put it in a having
>> clause, as that would exclude the already aggregated rows and I want
>> to filter then before the aggregation. Anyone have any idea how I can
>> do this?
>
>
> why would you want to do that?
> it would make the where clause not be able to use proper indexes

I do not know which of the 5 f_tag_* columns will be populated and I
need to filter on the one that is populated.

-- 
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 Mon, Jan 25, 2016 at 8:01 PM, Rebecca Love  wrote:
> Have you tried using a select case statement for ftag?

How would that help? Unless I'm missing something, I'd still have to
have a reference to the column alias in the where clause.


>> On Jan 25, 2016, at 6:39 PM, Larry Martell  wrote:
>>
>> On Mon, Jan 25, 2016 at 7:27 PM, Reindl Harald  
>> wrote:
>>>
>>>
>>> Am 26.01.2016 um 01:16 schrieb Larry Martell:

 I know I cannot use an alias in a where clause, but I am trying to
 figure out how to achieve what I need.

 If I could have an alias in a where clause my sql would look like this:

 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)
 FROM data_cst
 WHERE ftag = 'E-CD7'
 GROUP BY wafer_id, lot_id

 But I can't use ftag in the where clause. I can't put it in a having
 clause, as that would exclude the already aggregated rows and I want
 to filter then before the aggregation. Anyone have any idea how I can
 do this?
>>>
>>>
>>> why would you want to do that?
>>> it would make the where clause not be able to use proper indexes
>>
>> I do not know which of the 5 f_tag_* columns will be populated and I
>> need to filter on the one that is populated.
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:http://lists.mysql.com/mysql
>>
>

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