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.
Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN
Become certified in MySQL! Visit https://www.mysql.com/certification/
for details.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
