Re: using alias in where clause

On Mon, Jan 25, 2016 at 8:26 PM, Johnny Withers <[email protected]> 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 <[email protected]>
> 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
> [email protected]

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