Re: using alias in where clause
On Fri, Jan 29, 2016 at 11:15 AM, shawn l.green wrote: > > > On 1/28/2016 6:30 PM, Larry Martell wrote: >> >> On Thu, Jan 28, 2016 at 5:45 PM, shawn l.green >> wrote: >>> >>> >>> >>> On 1/28/2016 3:32 PM, Larry Martell wrote: On Thu, Jan 28, 2016 at 2:13 PM, shawn l.green wrote: > > > > > On 1/28/2016 1:14 PM, Larry Martell wrote: >> >> >> >> On Tue, Jan 26, 2016 at 8:40 AM, Hal.sz S.ndor 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
Re: using alias in where clause
On 1/28/2016 6:30 PM, Larry Martell wrote: On Thu, Jan 28, 2016 at 5:45 PM, shawn l.green wrote: On 1/28/2016 3:32 PM, Larry Martell wrote: On Thu, Jan 28, 2016 at 2:13 PM, shawn l.green wrote: On 1/28/2016 1:14 PM, Larry Martell wrote: On Tue, Jan 26, 2016 at 8:40 AM, Hal.sz S.ndor 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. -- 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
RE: my.cnf authencication
Thanks for the reply - that was indeed a typo. I resolved this buy making these changes: mysqldump --defaults-extra-file="c:\sql\dump.cnf" dname > c:\loc with my cnf containing: [client] user = user password = pass Looks like I misplaced "" and or [client] / [mysqldump] in the cnf Thanks -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: vrijdag 29 januari 2016 15:06 To: Harrie Robins Cc: MySql Subject: Re: my.cnf authencication - Original Message - > From: "Harrie Robins" > Subject: my.cnf authencication > > mysqldump --defaults-file dbase > c:\sql\dbase.sql 2>> c:\log.tct Might just be a typo in your mail, but you'll need to actually pass the defaults-file, too: --defaults-file=c:\sql\dump.cnf . I think there may be another typo somewhere, too, as it seems to think that lts-file is the user you're passing. I'm wondering if you haven't accidentally put only a single - in front of defaults-file. > > > My log shows: > > mysqldump: Got error: 1045: Access denied for user > 'lts-file=c:\sql\dump.cnf'@'localhost' (using password: NO) when > trying to connect > > It looks like credentials are not filled in!? > > Regards, > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: my.cnf authencication
- Original Message - > From: "Harrie Robins" > Subject: my.cnf authencication > > mysqldump --defaults-file dbase > c:\sql\dbase.sql 2>> c:\log.tct Might just be a typo in your mail, but you'll need to actually pass the defaults-file, too: --defaults-file=c:\sql\dump.cnf . I think there may be another typo somewhere, too, as it seems to think that lts-file is the user you're passing. I'm wondering if you haven't accidentally put only a single - in front of defaults-file. > > > My log shows: > > mysqldump: Got error: 1045: Access denied for user > 'lts-file=c:\sql\dump.cnf'@'localhost' (using password: NO) when trying to > connect > > It looks like credentials are not filled in!? > > Regards, > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
my.cnf authencication
I'm running a mysqldump in windows and I'm mailing 2>> output, right now I get this annoying 'insecure' error that pollutes my log. So I figured I use --defaults-file and set: In c:\sql\dump.cnf [mysqldump] user = myuser password = pass my line looks like this mysqldump --defaults-file dbase > c:\sql\dbase.sql 2>> c:\log.tct My log shows: mysqldump: Got error: 1045: Access denied for user 'lts-file=c:\sql\dump.cnf'@'localhost' (using password: NO) when trying to connect It looks like credentials are not filled in!? Regards, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
