Re: using alias in where clause

On Mon, Jan 25, 2016 at 9:32 PM, Larry Martell <[email protected]>
wrote:

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

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



-- 
-----------------------------
Johnny Withers
601.209.4985
[email protected]