On Thu, Jan 28, 2016 at 5:45 PM, shawn l.green <shawn.l.gr...@oracle.com> wrote: > > > On 1/28/2016 3:32 PM, Larry Martell wrote: >> >> On Thu, Jan 28, 2016 at 2:13 PM, shawn l.green <shawn.l.gr...@oracle.com> >> wrote: >>> >>> >>> >>> On 1/28/2016 1:14 PM, Larry Martell wrote: >>>> >>>> >>>> On Tue, Jan 26, 2016 at 8:40 AM, Hal.sz S.ndor <h...@tbbs.net> 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
