Re: using alias in where clause
- Date: Mon, 25 Jan 2016 19:26:37 -0600
- From: Johnny Withers <johnny@xxxxxxxxxxxxx>
- Subject: Re: using alias in where clause
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 <larry.martell@xxxxxxxxx>
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
johnny@xxxxxxxxxxxxx