Re: using alias in where clause
- Date: Thu, 28 Jan 2016 17:45:57 -0500
- From: "shawn l.green" <shawn.l.green@xxxxxxxxxx>
- Subject: Re: using alias in where clause
On 1/28/2016 3:32 PM, Larry Martell wrote:
On Thu, Jan 28, 2016 at 2:13 PM, shawn l.green <shawn.l.green@xxxxxxxxxx> wrote:
On 1/28/2016 1:14 PM, Larry Martell wrote:
On Tue, Jan 26, 2016 at 8:40 AM, Hal.sz S.ndor <hsv@xxxxxxxx> wrote:
2016/01/25 19:16 ... Larry Martell:
IFNULL(f_tag_ch_y_bottom, NULL))))) as ftag,
Of course, this isn't your real problem, but you could use COALESCE
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,
As Johnny Withers points out, you may repeat the expression in the
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
One option to consider is to add another column to the query with a CASE
similar to this...
, ... original fields ...
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 ...
END as match_flag
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
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.
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/
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql