On Fri, Jan 29, 2016 at 11:15 AM, shawn l.green <shawn.l.gr...@oracle.com> wrote: > > > On 1/28/2016 6:30 PM, Larry Martell wrote: >> >> 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. >> > > Yes it would. > > Just modify the check in the case from "IS NOT NULL" to "='XYZ'" to make > both functions (CASE and COALESCE) find the same field value in the same row > at the same time.
Thanks very much Shawn. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
