Re: using alias in where clause
Larry Martell <[email protected]>
| Newsgroups | gmane.comp.db.mysql.general |
|---|---|
| Message-ID | <CACwCsY6JB4SBiwmDZFY=3Sv9ppf1uoqh7yJq3+7eMQX53ak5FA@mail.gmail.com> |
On Thu, Jan 28, 2016 at 2:13 PM, shawn l.green <[email protected]> wrote: > > > On 1/28/2016 1:14 PM, Larry Martell wrote: >> >> On Tue, Jan 26, 2016 at 8:40 AM, Hal.sz S.ndor <[email protected]> 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