Re: Function
On 12/12/2014 2:38 PM, Alexander Syvak wrote: Hello! How is actually a function done internally in MySQL after CREATE FUNCTION statement? Why can't there be a dynamic SQL inside a function? Sorry for the delay. The answer is embedded in this description of what is or is not allowed within a function: from: http://dev.mysql.com/doc/refman/5.6/en/create-procedure.html Stored functions may not contain statements that perform explicit or implicit commit or rollback. Support for these statements is not required by the SQL standard, which states that each DBMS vendor may decide whether to permit them. Since we cannot easily restrict the types of commands generated by dynamic SQL within a function, we simply disallowed those as part of the design. This and several other restrictions for functions are also listed here http://dev.mysql.com/doc/refman/5.6/en/stored-program-restrictions.html The gist of all of these restrictions is that a FUNCTION shall create the least side effects possible while generating the result value. -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Function
Hello! How is actually a function done internally in MySQL after CREATE FUNCTION statement? Why can't there be a dynamic SQL inside a function?
Calling function, that operates on another database
Hi all. I have standard select statement and on one column I want to run function, that will connect to another database (same server). Is this possible? High level example: SELECT db1.clients.client_id, getTurnover(db1.clients.client_id) FROM db1.clients; AND getTurnover($id) body would be something like: SELECT SUM(db2.turnover.amount) FROM db2.turnover WHERE db2.turnover.client_id = $id; So for some data, I need to make lookup to another database table. Is this even possible? Cheers, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Calling function, that operates on another database
Chris, take a look at Federated tables https://dev.mysql.com/doc/refman/5.5/en/federated-storage-engine.html No, it is not as easy as Oracle's dblinks. David. David Lerer | Director, Database Administration | Interactive | 605 Third Avenue, 12th Floor, New York, NY 10158 Direct: (646) 487-6522 | Fax: (646) 487-1569 | dle...@univision.net | www.univision.net -Original Message- From: bars0.bars0.bars0 [mailto:bars0.bars0.ba...@gmail.com] Sent: Tuesday, April 08, 2014 4:16 PM To: mysql@lists.mysql.com Subject: Calling function, that operates on another database Hi all. I have standard select statement and on one column I want to run function, that will connect to another database (same server). Is this possible? High level example: SELECT db1.clients.client_id, getTurnover(db1.clients.client_id) FROM db1.clients; AND getTurnover($id) body would be something like: SELECT SUM(db2.turnover.amount) FROM db2.turnover WHERE db2.turnover.client_id = $id; So for some data, I need to make lookup to another database table. Is this even possible? Cheers, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql The information contained in this e-mail and any attached documents may be privileged, confidential and protected from disclosure. If you are not the intended recipient you may not read, copy, distribute or use this information. If you have received this communication in error, please notify the sender immediately by replying to this message and then delete it from your system.
Re: Calling function, that operates on another database
On Tue, Apr 8, 2014 at 4:15 PM, bars0.bars0.bars0 bars0.bars0.ba...@gmail.com wrote: Hi all. I have standard select statement and on one column I want to run function, that will connect to another database (same server). Is this possible? High level example: SELECT db1.clients.client_id, getTurnover(db1.clients.client_id) FROM db1.clients; AND getTurnover($id) body would be something like: SELECT SUM(db2.turnover.amount) FROM db2.turnover WHERE db2.turnover.client_id = $id; So for some data, I need to make lookup to another database table. Is this even possible? Yes, using just the syntax you have: db.table -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: function INTERVAL in view
2012/09/17 13:11 -0500, Peter Brawley Looks like a bug. Report it? It was reported: Bug #45346 VIEW containing INTERVAL(...) can be created but does not work Submitted: 5 Jun 2009 10:00 Modified: 5 Jun 2009 10:16 Severity:S3 (Non-critical) Version:6.0, 5.4, 5.1 OS:Any Seems it arouses little interest. I ask meself whether to add my complaint to it. Turns out that for my problem it is enough to add 24 and divide by 20 and round down--but that is much moare opaque than INTERVAL(Mean, 6, 18, 36, 72). Actually, I hav to include 0, too: the yet opaquer function FLOOR(.4 + SQRT(Mean/5)). Ugh. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
function INTERVAL in view
My MySQL is of version 5.5.8-log. I find I cannot save a query with INTERVAL in a view: redundant round brackets are added. If the query is SELECT INTERVAL(1, 2, 3, 4) within the frm file there is the expression interval((1, 2, 3, 4)) which is wrong. What is known about this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: function INTERVAL in view
n 2012-09-17 12:58 PM, h...@tbbs.net wrote: My MySQL is of version 5.5.8-log. I find I cannot save a query with INTERVAL in a view: redundant round brackets are added. If the query is SELECT INTERVAL(1, 2, 3, 4) within the frm file there is the expression interval((1, 2, 3, 4)) which is wrong. What is known about this? Looks like a bug. Report it? PB www.artfulsoftware.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: function INTERVAL in view
INTERVAL is a keyword. This is probably the root of the hiccup. Is that your Stored Function? Or UDF? -Original Message- From: Peter Brawley [mailto:peter.braw...@earthlink.net] Sent: Monday, September 17, 2012 11:12 AM To: mysql@lists.mysql.com Subject: Re: function INTERVAL in view n 2012-09-17 12:58 PM, h...@tbbs.net wrote: My MySQL is of version 5.5.8-log. I find I cannot save a query with INTERVAL in a view: redundant round brackets are added. If the query is SELECT INTERVAL(1, 2, 3, 4) within the frm file there is the expression interval((1, 2, 3, 4)) which is wrong. What is known about this? Looks like a bug. Report it? PB www.artfulsoftware.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: function INTERVAL in view
-Original Message- From: Rick James [mailto:rja...@yahoo-inc.com] Sent: September 17, 2012 3:04 PM To: peter.braw...@earthlink.net; mysql@lists.mysql.com Subject: RE: function INTERVAL in view INTERVAL is a keyword. This is probably the root of the hiccup. Is that your Stored Function? Or UDF? on 2012-09-17 12:58 PM, h...@tbbs.net wrote: My MySQL is of version 5.5.8-log. I find I cannot save a query with INTERVAL in a view: redundant round brackets are added. If the query is SELECT INTERVAL(1, 2, 3, 4) within the frm file there is the expression interval((1, 2, 3, 4)) which is wrong. What is known about this? It is also a function: http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html#function_in terval as for why it does that? I have no idea. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: This partition function is not allowed
- Original Message - From: Adarsh Sharma adarsh.sha...@orkash.com Is it possible I upgrade to higher version 5.5 with existing data Yes, look for the upgrade notes in the online documentation. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
cannot pass time to function
I'm trying to create a function that formats a time in a standard way
('%H:%i'). But all I can seem to get back is null.
DROP TABLE IF EXISTS bogus_table;
CREATE TABLE IF NOT EXISTS bogus_table (
btime TIME
);
INSERT INTO bogus_table VALUES ('12:34:56');
DROP FUNCTION IF EXISTS bogus ;
DELIMITER $$
CREATE FUNCTION bogus (
btime TIME
)
RETURNS VARCHAR(10)
BEGIN
DECLARE ctime VARCHAR(10) DEFAULT '';
SET ctime=DATE_FORMAT(btime,'%H:%i');
RETURN ctime;
END $$
DELIMITER ;
SELECT btime,
bogus(btime) AS btime1,
DATE_FORMAT(btime,'%H:%i') AS btime2
FROM bogus_table;
+--+++
| btime| btime1 | btime2 |
+--+++
| 12:34:56 | NULL | 12:34 |
+--+++
1 row in set, 1 warning (0.00 sec)
mysql show warnings;
+-+--+--+
| Level | Code | Message |
+-+--+--+
| Warning | 1292 | Incorrect datetime value: '12:34:56' |
+-+--+--+
1 row in set (0.00 sec)
mysql
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: cannot pass time to function
On 1/27/2012 12:09 PM, John Heim wrote:
I'm trying to create a function that formats a time in a standard way
('%H:%i'). But all I can seem to get back is null.
DROP TABLE IF EXISTS bogus_table;
CREATE TABLE IF NOT EXISTS bogus_table (
btime TIME
);
INSERT INTO bogus_table VALUES ('12:34:56');
DROP FUNCTION IF EXISTS bogus ;
DELIMITER $$
CREATE FUNCTION bogus (
btime TIME
)
RETURNS VARCHAR(10)
BEGIN
DECLARE ctime VARCHAR(10) DEFAULT '';
SET ctime=DATE_FORMAT(btime,'%H:%i');
RETURN ctime;
END $$
DELIMITER ;
SELECT btime,
bogus(btime) AS btime1,
DATE_FORMAT(btime,'%H:%i') AS btime2
FROM bogus_table;
+--+++
| btime| btime1 | btime2 |
+--+++
| 12:34:56 | NULL | 12:34 |
+--+++
1 row in set, 1 warning (0.00 sec)
In 5.0 use TIME_FORMAT(). In 5.5 and 5.6, DATE_FORMAT() accepts time
values without dates.
PB
-
mysql show warnings;
+-+--+--+
| Level | Code | Message |
+-+--+--+
| Warning | 1292 | Incorrect datetime value: '12:34:56' |
+-+--+--+
1 row in set (0.00 sec)
mysql
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
This partition function is not allowed
Dear all,
Today i am creating a partition table in my mysql server
5.1.34-community-log .
create table sample( a integer, dt_stamp timestamp not null default
current timestamp, content varchar)engine= innodb
PARTITION BY RANGE ( UNIX_TIMESTAMP(dt_stamp) ) (
- PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-02-01
00:00:00') ),
- PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-03-01
00:00:00') ),
- PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-10-01
00:00:00') ),
- PARTITION p9 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-11-01
00:00:00') ),
- PARTITION p10 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-12-01
00:00:00') ),
- PARTITION p11 VALUES LESS THAN (MAXVALUE)
- );
ERROR 1564 (HY000): This partition function is not allowed
After some Rn D on the error I found my mysql version does not support
partitioning on timestamp column.
So i need to upgrade mysql server.
But my my.cnf requirements are :-
innodb_data_file_path |
/hdd2-1/innodb_data1/ibdata1:250G;/hdd3-1/innodb_data2/ibdata2:250G;/hdd4-1/innodb_data3/ibdata3:1G:autoextend
It takes near about 5 -6 hours to create these files start my server.
Is it possible I upgrade to higher version 5.5 with existing data dirs
and existing innodb tables.
Is there any link that shows how to upgrade mysql in
[root@s6-mysd-1 ~]# cat /etc/issue
Caos NSA: Node - Server - Appliance (release 1.0/Cato) \
Thanks in advance.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
optimizer in function
Does the optimizer look into function called from query? In my queries the expression (SELECT hwyl FROM Stock) / (SELECT regularPayment FROM Stock), where Stock is a one-record table, often is repeated. The optimizer sees that, and makes the ratio a constant, and I can afford to be clear. If that expression were within a function called from the same spot, would the optimizer look into the function and see the same effectiv constant? or is it better to make it an argument to the function? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: optimizer in function
I think the only clues the optimizer consults with regard to UDFs is the
'characteristic' provided at the time you create the routine.
from http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html
*
*
*characteristic*:
COMMENT '*string*'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
- michael dykman
2011/10/8 Halász Sándor h...@tbbs.net
Does the optimizer look into function called from query?
In my queries the expression (SELECT hwyl FROM Stock) / (SELECT
regularPayment FROM Stock), where Stock is a one-record table, often is
repeated. The optimizer sees that, and makes the ratio a constant, and I can
afford to be clear. If that expression were within a function called from
the same spot, would the optimizer look into the function and see the same
effectiv constant? or is it better to make it an argument to the function?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
Re: temp table and view/function/procedure dilemma
Hi, You can always create any table from procedures. However, it seems to me that flexviews can solve all of your problems, take a look at it. It will provide you incrementally refreshable materialized views. Peter Boros On 04/05/2011 08:15 PM, Bgs wrote: Hi all, I have a problem here and looking for a solution. I have a temporary table which is a smaller table generated from a rather big one. The full table is too big to make real gimmicks on it, so I do need the temp table. Later I need to do several queries on the temp table. So my initial setup and needs are: - temporary table to work from - result sets derived from parametric queries - mysql views cannot work from temporary tables so I have to drop a view+select/where approach. - functions cannot return result sets - procedures can do everything but I found no way to handle the result set within mysql (officially not supported) Any ideas how to solve this? Thanks in advance Bgs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: temp table and view/function/procedure dilemma
On 04/06/2011 09:13 PM, Sándor Halász wrote: I have a temporary table which is a smaller table generated from a rather big one. The full table is too big to make real gimmicks on it, so I do need the temp table. Later I do several queries on the temp table. So my initial setup and needs are: - temporary table to work from - result sets derived from parametric queries - mysql views cannot work from temporary tables so I have to drop a view+select/where approach. - functions cannot return result sets - procedures can do everything but I found no way to handle the result set within mysql (officially not supported) Any ideas how to solve this? Why not fake the temporariness, by DROPping the table as needful? I 'DROP'ed that approach for a couple of reasons: - While trying to minimize the load on the big table there is a real chance of concurrent jobs. Overall I find two temp table generation (loads) better than dropping each other's tables or locking other jobs. You also have to keep track of the fake-temp table's age. - A regular update of the fake-temp table would solve the above, but would produce too much load at the expected freshness. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: temp table and view/function/procedure dilemma
Hi, On 04/07/2011 08:06 AM, petya wrote: Hi, You can always create any table from procedures. However, it seems to me that flexviews can solve all of your problems, take a look at it. It will provide you incrementally refreshable materialized views. How do you create a table from a procedure output? I found it stated everywhere that it's not supported. About flexviews: Looks like a solution indeed. I will look into it! Thanks Bgs Peter Boros On 04/05/2011 08:15 PM, Bgs wrote: Hi all, I have a problem here and looking for a solution. I have a temporary table which is a smaller table generated from a rather big one. The full table is too big to make real gimmicks on it, so I do need the temp table. Later I need to do several queries on the temp table. So my initial setup and needs are: - temporary table to work from - result sets derived from parametric queries - mysql views cannot work from temporary tables so I have to drop a view+select/where approach. - functions cannot return result sets - procedures can do everything but I found no way to handle the result set within mysql (officially not supported) Any ideas how to solve this? Thanks in advance Bgs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: temp table and view/function/procedure dilemma
2011/04/05 20:15 +0200, Bgs I have a temporary table which is a smaller table generated from a rather big one. The full table is too big to make real gimmicks on it, so I do need the temp table. Later I do several queries on the temp table. So my initial setup and needs are: - temporary table to work from - result sets derived from parametric queries - mysql views cannot work from temporary tables so I have to drop a view+select/where approach. - functions cannot return result sets - procedures can do everything but I found no way to handle the result set within mysql (officially not supported) Any ideas how to solve this? Why not fake the temporariness, by DROPping the table as needful? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
temp table and view/function/procedure dilemma
Hi all, I have a problem here and looking for a solution. I have a temporary table which is a smaller table generated from a rather big one. The full table is too big to make real gimmicks on it, so I do need the temp table. Later I need to do several queries on the temp table. So my initial setup and needs are: - temporary table to work from - result sets derived from parametric queries - mysql views cannot work from temporary tables so I have to drop a view+select/where approach. - functions cannot return result sets - procedures can do everything but I found no way to handle the result set within mysql (officially not supported) Any ideas how to solve this? Thanks in advance Bgs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Increase for 1 using REPLACE function
I have to increase 'no_of_visits' for 1. Using UPDATE function is easy: update visits set no_of_visits=no_of_visits+1 where visitor_id=123 but, how it should be (if possible at all) if I want to use REPLACE function? I tried something similar replace into visitors values ($visitor_id, (no_of_visits+1)) but it doesn't work?!? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Increase for 1 using REPLACE function
2011/03/18 17:24 -0500, Afan Pasalic I have to increase 'no_of_visits' for 1. Using UPDATE function is easy: update visits set no_of_visits=no_of_visits+1 where visitor_id=123 but, how it should be (if possible at all) if I want to use REPLACE function? I tried something similar replace into visitors values ($visitor_id, (no_of_visits+1)) but it doesn't work?!? Of course; the MySQL REPLACE command is not meant for that. It is simply the same as INSERT unless the table has a key, also given in the transaction. UPDATE is the right command for this. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: function to limit value of integer
Hi Travis, This is very helpful thank you. However, is there a way to make it not be less than a 1. As it's written below someone with one job gets a zero and someone with no jobs gets a NULL. It would be great if someone with 1 job got a 1 and someone with zero jobs got a 0. Thanks again, Richard 2011/2/10 Travis Ard travis_...@hotmail.com Maybe some sort of logarithmic expression? select no_of_jobs, 10 * log(10, no_of_jobs) as job_weight from data; Of course, you'd have to tweak your coefficients to match the weighting system you want to use. -Travis -Original Message- From: Richard Reina [mailto:gatorre...@gmail.com] Sent: Thursday, February 10, 2011 3:07 PM To: mysql@lists.mysql.com Subject: function to limit value of integer Is there a function that can limit the value of an integer in a MySQL query? I am trying to write a query that scores someones experience. However, number of jobs can become overweighted in the the query below. If someone has done 10 jobs vs. 1 that's a big difference in experience. But someone who's done 100 vs. someone who's done 50 the difference in experience is not so great as they are both near the top of the learning curve. In essence number of jobs becomes less and less of a contributor as it increases. Is there a way to limit it's value as it increases? SELECT years_srvd + no_of_jobs AS EXPERIENCE Thanks, Richard
Re: function to limit value of integer
How about the square root of the number of jobs, or some other root if you want another coefficient? That doesn't have the limiting behaviour a logarithmic function offers, though. On Fri, Feb 11, 2011 at 2:08 PM, Richard Reina gatorre...@gmail.com wrote: Hi Travis, This is very helpful thank you. However, is there a way to make it not be less than a 1. As it's written below someone with one job gets a zero and someone with no jobs gets a NULL. It would be great if someone with 1 job got a 1 and someone with zero jobs got a 0. Thanks again, Richard 2011/2/10 Travis Ard travis_...@hotmail.com Maybe some sort of logarithmic expression? select no_of_jobs, 10 * log(10, no_of_jobs) as job_weight from data; Of course, you'd have to tweak your coefficients to match the weighting system you want to use. -Travis -Original Message- From: Richard Reina [mailto:gatorre...@gmail.com] Sent: Thursday, February 10, 2011 3:07 PM To: mysql@lists.mysql.com Subject: function to limit value of integer Is there a function that can limit the value of an integer in a MySQL query? I am trying to write a query that scores someones experience. However, number of jobs can become overweighted in the the query below. If someone has done 10 jobs vs. 1 that's a big difference in experience. But someone who's done 100 vs. someone who's done 50 the difference in experience is not so great as they are both near the top of the learning curve. In essence number of jobs becomes less and less of a contributor as it increases. Is there a way to limit it's value as it increases? SELECT years_srvd + no_of_jobs AS EXPERIENCE Thanks, Richard -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: function to limit value of integer
log(2, no_of_jobs + 1) will give 0 for 0 jobs, 1 for 1 job, 1.58 for 2 etc. etc. On 11 Feb 2011, at 14:04, Johan De Meersman wrote: How about the square root of the number of jobs, or some other root if you want another coefficient? That doesn't have the limiting behaviour a logarithmic function offers, though. On Fri, Feb 11, 2011 at 2:08 PM, Richard Reina gatorre...@gmail.com wrote: Hi Travis, This is very helpful thank you. However, is there a way to make it not be less than a 1. As it's written below someone with one job gets a zero and someone with no jobs gets a NULL. It would be great if someone with 1 job got a 1 and someone with zero jobs got a 0. Thanks again, Richard 2011/2/10 Travis Ard travis_...@hotmail.com Maybe some sort of logarithmic expression? select no_of_jobs, 10 * log(10, no_of_jobs) as job_weight from data; Of course, you'd have to tweak your coefficients to match the weighting system you want to use. -Travis -Original Message- From: Richard Reina [mailto:gatorre...@gmail.com] Sent: Thursday, February 10, 2011 3:07 PM To: mysql@lists.mysql.com Subject: function to limit value of integer Is there a function that can limit the value of an integer in a MySQL query? I am trying to write a query that scores someones experience. However, number of jobs can become overweighted in the the query below. If someone has done 10 jobs vs. 1 that's a big difference in experience. But someone who's done 100 vs. someone who's done 50 the difference in experience is not so great as they are both near the top of the learning curve. In essence number of jobs becomes less and less of a contributor as it increases. Is there a way to limit it's value as it increases? SELECT years_srvd + no_of_jobs AS EXPERIENCE Thanks, Richard -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- TTFN. Philip Riebold, p.rieb...@ucl.ac.uk /\ Media Services\ / University College London X ASCII Ribbon Campaign Windeyer Building, 46 Cleveland Street/ \ Against HTML Mail London, W1T 4JF +44 (0)20 7679 9259 (direct), 09259 (internal) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
function to limit value of integer
Is there a function that can limit the value of an integer in a MySQL query? I am trying to write a query that scores someones experience. However, number of jobs can become overweighted in the the query below. If someone has done 10 jobs vs. 1 that's a big difference in experience. But someone who's done 100 vs. someone who's done 50 the difference in experience is not so great as they are both near the top of the learning curve. In essence number of jobs becomes less and less of a contributor as it increases. Is there a way to limit it's value as it increases? SELECT years_srvd + no_of_jobs AS EXPERIENCE Thanks, Richard
RE: function to limit value of integer
Maybe some sort of logarithmic expression? select no_of_jobs, 10 * log(10, no_of_jobs) as job_weight from data; Of course, you'd have to tweak your coefficients to match the weighting system you want to use. -Travis -Original Message- From: Richard Reina [mailto:gatorre...@gmail.com] Sent: Thursday, February 10, 2011 3:07 PM To: mysql@lists.mysql.com Subject: function to limit value of integer Is there a function that can limit the value of an integer in a MySQL query? I am trying to write a query that scores someones experience. However, number of jobs can become overweighted in the the query below. If someone has done 10 jobs vs. 1 that's a big difference in experience. But someone who's done 100 vs. someone who's done 50 the difference in experience is not so great as they are both near the top of the learning curve. In essence number of jobs becomes less and less of a contributor as it increases. Is there a way to limit it's value as it increases? SELECT years_srvd + no_of_jobs AS EXPERIENCE Thanks, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Function Question
My issue is actually in Excel. I'm hoping someone could help me... I need to total the values in column B for Emily. Is there a way other than =SUM (B1+B2+B4+B7)? -- Emily | 1 - Emily | 5 - Greg | 2 - Bob | 7 - Emily | 4 - Jenn | 2 - Greg | 1 - Emily | 7 - Bob | 3 - Emily | 3 - Nick Moreno|Communications Project Specialist|Home Federal Savings Bank 1016 Civic Center Drive NW|Rochester MN, 55901|Work 651-405-2010|Cell 612-987-0584 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Function Question
Have you tried: select UserName, Sum(ColB) from Table group by UserName; or select UserName, Sum(ColB) from Table group by UserName where UserName=Emily; Mike At 11:43 AM 1/12/2011, Nicholas Moreno wrote: My issue is actually in Excel. I'm hoping someone could help me... I need to total the values in column B for Emily. Is there a way other than =SUM (B1+B2+B4+B7)? -- Emily | 1 - Emily | 5 - Greg | 2 - Bob | 7 - Emily | 4 - Jenn | 2 - Greg | 1 - Emily | 7 - Bob | 3 - Emily | 3 - Nick Moreno|Communications Project Specialist|Home Federal Savings Bank 1016 Civic Center Drive NW|Rochester MN, 55901|Work 651-405-2010|Cell 612-987-0584 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure.
On 1/4/2011 23:23, James Dekker wrote: Peter, Thanks for the response! Unfortunately, that worked but a new error arose: check the manual that corresponds to your MySQL server version for the right syntax to use near 'ID = (select max(CONVERT(id_field, signed)) from my_table_t)' at line 1 Is there a better way to generate incremented sequence IDs? Can this be done in a stored function? Is there a particular reason why you cannot use an auto_increment column to atomically create your sequence number? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure.
Because some sequence tables contain one to many cardinality and MySQL tables can only have one auto_increment column... Is there a way to do what I am trying to do (obtain max sequence id, set it to its corresponding table, and then increment by one) in a stored function? Happy programming, James On Jan 5, 2011, at 10:01 AM, Shawn Green (MySQL) wrote: On 1/4/2011 23:23, James Dekker wrote: Peter, Thanks for the response! Unfortunately, that worked but a new error arose: check the manual that corresponds to your MySQL server version for the right syntax to use near 'ID = (select max(CONVERT(id_field, signed)) from my_table_t)' at line 1 Is there a better way to generate incremented sequence IDs? Can this be done in a stored function? Is there a particular reason why you cannot use an auto_increment column to atomically create your sequence number? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure.
On 1/5/2011 13:31, James Dekker wrote: Because some sequence tables contain one to many cardinality and MySQL tables can only have one auto_increment column... Is there a way to do what I am trying to do (obtain max sequence id, set it to its corresponding table, and then increment by one) in a stored function? Maybe some variation of this will help? http://stackoverflow.com/questions/805808/emulating-a-transaction-safe-sequence-in-mysql I don't understand the need for a SEQUENCE. In my history, if there is some kind of object identifier you want to use, then an auto_increment field on the row that defines the object itself is sufficient. Then all child elements of that object can include the autogenerated ID value from their parent object as you create them alongside of any unique identifiers they may require. http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id Have you also explored the use of auto_increment columns as part of a multiple-column index on MyISAM tables as described here? http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure.
Hello there,
I am using MySQL 5 on OS X - Snow Leopard...
Have working code in place which obtains the highest sequence number ID from a
sequence table and then increments and assigns it to its corresponding table:
The original code's purpose is to dynamically increments a specific table's
last sequence id and set its corresponding table's id to that new value.
Notes:
-
(1) Original Code Snippet (which is working):
-- Get last sequence number.
replace into my_sequence_id_s set id = (select max(CONVERT(sequence_id,
signed)) from my_table_t);
-- Increments the number.
insert into my_sequence_id_s set id = null;
-- Saves the number as a variable
set @dynamicId = last_insert_id();
-- Print
select @dynamicId;
-
(2) Refactoring:
DROP PROCEDURE IF EXISTS generate_dynamic_id#
CREATE PROCEDURE generate_dynamic_id(IN _sequence_table varchar(40),IN
_actual_table varchar(40), IN _id_field VARCHAR(40), OUT dynamic_id varchar(40))
BEGIN
-- Get Last Sequence Number
set @getLastSequenceNumberSQL = concat('REPLACE INTO ', _sequence_table, 'SET
ID = (select max(CONVERT(',_id_field,', signed)) from ', _actual_table, ');');
prepare lastRecordStmt from @getLastSequenceNumberSQL;
execute lastRecordStmt;
deallocate prepare lastRecordStmt;
-- Increments the number.
set @createNewSequenceNumberSQL = concat('insert into ', _sequence_table ,'
set id = null;');
prepare newSequenceNumberStmt from @createNewSequenceNumberSQL;
execute newSequenceNumberStmt;
deallocate prepare newSequenceNumberStmt;
-- Set the number as a dynamic variable.
set @dynamic_id = last_insert_id();
END;
#
-
(3) Here's the calling function (which fails):
-- Get dynamically incremented id
generate_dynamic_id('my_sequence_id_s', 'my_table_t', 'table_id', @dynamicId);
Error:
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your
SQL syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near 'generate_dynamic_id('my_sequence_id_s',
'my_table_t', 'table_id', @dyn' at line 1.
-
For some odd reason, dynamic function calls are not allowed in Stored Functions
or Triggers, so that's why a Stored Procedure was used.
As you can see, I am setting up varchars at the parameters and then trying to
concatenate them as strings and run them inside prepared statements.
Any help would be greatly appreciated...
-James
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure.
generate_dynamic_id('my_sequence_id_s', 'my_table_t', 'table_id', @dynamicId);
Should be:
CALL generate_dynamic_id('my_sequence_id_s', 'my_table_t', 'table_id',
@dynamicId);
PB
On 1/4/2011 9:28 PM, James Dekker wrote:
Hello there,
I am using MySQL 5 on OS X - Snow Leopard...
Have working code in place which obtains the highest sequence number ID from a
sequence table and then increments and assigns it to its corresponding table:
The original code's purpose is to dynamically increments a specific table's
last sequence id and set its corresponding table's id to that new value.
Notes:
-
(1) Original Code Snippet (which is working):
-- Get last sequence number.
replace into my_sequence_id_s set id = (select max(CONVERT(sequence_id,
signed)) from my_table_t);
-- Increments the number.
insert into my_sequence_id_s set id = null;
-- Saves the number as a variable
set @dynamicId = last_insert_id();
-- Print
select @dynamicId;
-
(2) Refactoring:
DROP PROCEDURE IF EXISTS generate_dynamic_id#
CREATE PROCEDURE generate_dynamic_id(IN _sequence_table varchar(40),IN
_actual_table varchar(40), IN _id_field VARCHAR(40), OUT dynamic_id varchar(40))
BEGIN
-- Get Last Sequence Number
set @getLastSequenceNumberSQL = concat('REPLACE INTO ', _sequence_table,
'SET ID = (select max(CONVERT(',_id_field,', signed)) from ', _actual_table,
');');
prepare lastRecordStmt from @getLastSequenceNumberSQL;
execute lastRecordStmt;
deallocate prepare lastRecordStmt;
-- Increments the number.
set @createNewSequenceNumberSQL = concat('insert into ', _sequence_table ,'
set id = null;');
prepare newSequenceNumberStmt from @createNewSequenceNumberSQL;
execute newSequenceNumberStmt;
deallocate prepare newSequenceNumberStmt;
-- Set the number as a dynamic variable.
set @dynamic_id = last_insert_id();
END;
#
-
(3) Here's the calling function (which fails):
-- Get dynamically incremented id
generate_dynamic_id('my_sequence_id_s', 'my_table_t', 'table_id', @dynamicId);
Error:
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your
SQL syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near 'generate_dynamic_id('my_sequence_id_s',
'my_table_t', 'table_id', @dyn' at line 1.
-
For some odd reason, dynamic function calls are not allowed in Stored Functions
or Triggers, so that's why a Stored Procedure was used.
As you can see, I am setting up varchars at the parameters and then trying to
concatenate them as strings and run them inside prepared statements.
Any help would be greatly appreciated...
-James
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Trying to Generate Dynamic Sequence IDs as Stored Function or Stored Procedure.
Peter,
Thanks for the response!
Unfortunately, that worked but a new error arose:
check the manual that corresponds to your MySQL server version for the right
syntax to use near 'ID = (select max(CONVERT(id_field, signed)) from
my_table_t)' at line 1
Is there a better way to generate incremented sequence IDs?
Can this be done in a stored function?
Happy programming,
James
On Jan 4, 2011, at 7:45 PM, Peter Brawley wrote:
generate_dynamic_id('my_sequence_id_s', 'my_table_t', 'table_id',
@dynamicId);
Should be:
CALL generate_dynamic_id('my_sequence_id_s', 'my_table_t', 'table_id',
@dynamicId);
PB
On 1/4/2011 9:28 PM, James Dekker wrote:
Hello there,
I am using MySQL 5 on OS X - Snow Leopard...
Have working code in place which obtains the highest sequence number ID from
a sequence table and then increments and assigns it to its corresponding
table:
The original code's purpose is to dynamically increments a specific table's
last sequence id and set its corresponding table's id to that new value.
Notes:
-
(1) Original Code Snippet (which is working):
-- Get last sequence number.
replace into my_sequence_id_s set id = (select max(CONVERT(sequence_id,
signed)) from my_table_t);
-- Increments the number.
insert into my_sequence_id_s set id = null;
-- Saves the number as a variable
set @dynamicId = last_insert_id();
-- Print
select @dynamicId;
-
(2) Refactoring:
DROP PROCEDURE IF EXISTS generate_dynamic_id#
CREATE PROCEDURE generate_dynamic_id(IN _sequence_table varchar(40),IN
_actual_table varchar(40), IN _id_field VARCHAR(40), OUT dynamic_id
varchar(40))
BEGIN
-- Get Last Sequence Number
set @getLastSequenceNumberSQL = concat('REPLACE INTO ', _sequence_table,
'SET ID = (select max(CONVERT(',_id_field,', signed)) from ', _actual_table,
');');
prepare lastRecordStmt from @getLastSequenceNumberSQL;
execute lastRecordStmt;
deallocate prepare lastRecordStmt;
-- Increments the number.
set @createNewSequenceNumberSQL = concat('insert into ', _sequence_table
,' set id = null;');
prepare newSequenceNumberStmt from @createNewSequenceNumberSQL;
execute newSequenceNumberStmt;
deallocate prepare newSequenceNumberStmt;
-- Set the number as a dynamic variable.
set @dynamic_id = last_insert_id();
END;
#
-
(3) Here's the calling function (which fails):
-- Get dynamically incremented id
generate_dynamic_id('my_sequence_id_s', 'my_table_t', 'table_id',
@dynamicId);
Error:
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in
your SQL syntax; check the manual that corresponds to your MySQL server
version
for the right syntax to use near 'generate_dynamic_id('my_sequence_id_s',
'my_table_t', 'table_id', @dyn' at line 1.
-
For some odd reason, dynamic function calls are not allowed in Stored
Functions or Triggers, so that's why a Stored Procedure was used.
As you can see, I am setting up varchars at the parameters and then trying
to concatenate them as strings and run them inside prepared statements.
Any help would be greatly appreciated...
-James
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=james.dek...@gmail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
using a count function
This is probably pretty obvious to everyone except me. I have a couple of columns, DateOfInterview and DateOfBirth in a table named Demographics For a monthly report I have a script where the operator enters the start and end dates of the reporting period. I need a query result with single line of three columns, each with a count of the number of interviews for that reporting period: || Under 18 ||19-65 || over 65 || ||5|| 19|| 23 || I've made three queries to select the counts for each age range, then used them to form another query I thought would give me an acceptable output. This gives me multiple lines, all with the same numbers: SELECT Count([Under 18 count].[Under 18]) AS [CountOfUnder 18], Count ([19 to 65 count].[19 to 65]) AS [CountOf19 to 65], Count([Over 65 count].Over65) AS CountOfOver65 FROM [Under 18 count], [19 to 65 count], [Over 65 count], Demographics WHERE (((Demographics.[Date of Interview]) Between [Report Start Date] And [Report End Date])); || Under 18 ||19-65 || over 65 || ||5|| 19|| 23 || ||5|| 19|| 23 || ||5|| 19|| 23 || Like I said, this should be pretty obvious to everyone but me. chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: using a count function
Chris, You are using Count when you should be using Sum. Here is a solution you can try: SELECT SUM(IF(DATEDIFF(dateofinterview,dateofbirth)/365.25 BETWEEN 0 AND 18.999, 1,0)) AS 18 and Under, SUM(IF(DATEDIFF(dateofinterview,dateofbirth)/365.25 BETWEEN 19 AND 65.999, 1,0)) AS 19-65, SUM(IF(DATEDIFF(dateofinterview,dateofbirth)/365.25 = 66, 1,0)) AS Over 65 FROM demographics; I noticed from your title you say Under 18 and then 19-65. I assume you are including 18 year olds for Under 18? Mike At 03:41 PM 5/29/2010, Chris Elhardt wrote: This is probably pretty obvious to everyone except me. I have a couple of columns, DateOfInterview and DateOfBirth in a table named Demographics For a monthly report I have a script where the operator enters the start and end dates of the reporting period. I need a query result with single line of three columns, each with a count of the number of interviews for that reporting period: || Under 18 ||19-65 || over 65 || ||5|| 19|| 23 || I've made three queries to select the counts for each age range, then used them to form another query I thought would give me an acceptable output. This gives me multiple lines, all with the same numbers: SELECT Count([Under 18 count].[Under 18]) AS [CountOfUnder 18], Count ([19 to 65 count].[19 to 65]) AS [CountOf19 to 65], Count([Over 65 count].Over65) AS CountOfOver65 FROM [Under 18 count], [19 to 65 count], [Over 65 count], Demographics WHERE (((Demographics.[Date of Interview]) Between [Report Start Date] And [Report End Date])); || Under 18 ||19-65 || over 65 || ||5|| 19|| 23 || ||5|| 19|| 23 || ||5|| 19|| 23 || Like I said, this should be pretty obvious to everyone but me. chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: C API Function for count(*)
You might get closer to what you want if you put your command in a text file and run it from the command line. On a Mac OS X, I put a similar command: select count(*) from testTable; into a small text file: testCount.txt and ran this command from the Terminal: mysql -u username -ppassword /Users/myname/Documents/testCount.txt The result was: COUNT(*) 12 without the decorations. Bob On May 14, 2010, at 11:35 PM, Dan Nelson wrote: In the last episode (May 14), Tim Johnson said: I have MySQL version 5.0.84 on linux slackware 13.0 32-bit. I am working with a relatively new API written in a programming language with a small user base (newlisp). The newlisp API imports a number of C API functions from the system MySQL shared object. If I were to issue a count(*) query from my monitor interface: Example: mysql select count(*) from clients; +--+ | count(*) | +--+ | 16 | +--+ If select count(*) from clients is issued from the newlisp API, is there a a C API function that would return '16'? You can't do it with one function call, but you can do it, since the MySQL cli was able to print 16 in your example above, and it was written in C. Take a look at mysql_store_result(), mysql_num_fields(), mysql_field_count(), mysql_fetch_row(), and mysql_fetch_lengths(). There's a simple code fragment to print a resultset on this page: http://dev.mysql.com/doc/refman/5.1/en/mysql-fetch-row.html -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=bobc...@earthlink.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: C API Function for count(*)
* Dan Nelson dnel...@allantgroup.com [100514 21:38]: You can't do it with one function call, but you can do it, since the MySQL cli was able to print 16 in your example above, and it was written in C. Take a look at mysql_store_result(), mysql_num_fields(), mysql_field_count(), mysql_fetch_row(), and mysql_fetch_lengths(). There's a simple code fragment to print a resultset on this page: http://dev.mysql.com/doc/refman/5.1/en/mysql-fetch-row.html Thanks Dan. I can use the C code there to model the code for the API. cheers -- Tim tim at johnsons-web.com or akwebsoft.com http://www.akwebsoft.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: C API Function for count(*)
* Bob Cole bobc...@earthlink.net [100515 06:58]: You might get closer to what you want if you put your command in a text file and run it from the command line. On a Mac OS X, I put a similar command: select count(*) from testTable; into a small text file: testCount.txt and ran this command from the Terminal: mysql -u username -ppassword /Users/myname/Documents/testCount.txt The result was: COUNT(*) 12 Hi Bob: That's a good trick. It doesn't fit the API that I am trying to enhance, but it could be a good workaround by 'echo'ing to a tmpfile. Thanks. -- Tim tim at johnsons-web.com or akwebsoft.com http://www.akwebsoft.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
C API Function for count(*)
I have MySQL version 5.0.84 on linux slackware 13.0 32-bit. I am working with a relatively new API written in a programming language with a small user base (newlisp). The newlisp API imports a number of C API functions from the system MySQL shared object. If I were to issue a count(*) query from my monitor interface: Example: mysql select count(*) from clients; +--+ | count(*) | +--+ | 16 | +--+ If select count(*) from clients is issued from the newlisp API, is there a a C API function that would return '16'? I have reviewed http://dev.mysql.com/doc/refman/5.1/en/c-api-functions.html and haven't been enlightened so far. I believe that I could parse the results as a string, but if I could access count(*) as a C function, it would be more efficient. I have a background in C. thanks -- Tim tim at johnsons-web.com or akwebsoft.com http://www.akwebsoft.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: C API Function for count(*)
In the last episode (May 14), Tim Johnson said: I have MySQL version 5.0.84 on linux slackware 13.0 32-bit. I am working with a relatively new API written in a programming language with a small user base (newlisp). The newlisp API imports a number of C API functions from the system MySQL shared object. If I were to issue a count(*) query from my monitor interface: Example: mysql select count(*) from clients; +--+ | count(*) | +--+ | 16 | +--+ If select count(*) from clients is issued from the newlisp API, is there a a C API function that would return '16'? You can't do it with one function call, but you can do it, since the MySQL cli was able to print 16 in your example above, and it was written in C. Take a look at mysql_store_result(), mysql_num_fields(), mysql_field_count(), mysql_fetch_row(), and mysql_fetch_lengths(). There's a simple code fragment to print a resultset on this page: http://dev.mysql.com/doc/refman/5.1/en/mysql-fetch-row.html -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
What returns from distance function?
Distance between two points. but in which type? and how can i convert this value to meter?
Re: What returns from distance function?
I used below query from http://lists.mysql.com/mysql/219805
SET @center = GeomFromText( 'POINT(39.78824896727801 30.50930339115439)' )
;# MySQL returned an empty result set (i.e. zero rows).
SET @radius = 0.005;# MySQL returned an empty result set (i.e. zero rows).
SET @bbox = GeomFromText( CONCAT( 'POLYGON((', X( @center ) - @radius , ' ',
Y( @center ) - @radius , ',', X( @center ) + @radius , ' ', Y( @center ) -
@radius , ',', X( @center ) + @radius , ' ', Y( @center ) + @radius , ',',
X( @center ) - @radius , ' ', Y( @center ) + @radius , ',', X( @center ) -
@radius , ' ', Y( @center ) - @radius , '))' ) ) ;# MySQL returned an empty
result set (i.e. zero rows).
SELECT astext( point ) , Distance( @center , point ) AS dist
FROM psn.psn_place
WHERE MBRContains( @bbox , point )
ORDER BY dist
LIMIT 10;
I don't know type of 'dist' in this result, I need 'dist' column in meter
format.
+--+-+
| astext( point ) | dist|
+--+-+
| POINT(39.7872360228843 30.5097413063049) | 0.00110355155014048 |
| POINT(39.7872648779901 30.5084055662155) | 0.00133211161219657 |
| POINT(39.7871288466708 30.5080997943878) | 0.00164417619226759 |
+--+-+
On Sun, Apr 18, 2010 at 4:25 PM, Onur UZUN onuruzu...@gmail.com wrote:
Distance between two points. but in which type? and how can i convert this
value to meter?
Stored Procedure/Function Question
Hi there, I have a WEIRD question, that I can't find an answer too... Here is my stored function: DELIMITER $$ USE `mydatabase`$$ DROP FUNCTION IF EXISTS `SPLIT_STR`$$ CREATE definer=`thisus...@`%` FUNCTION `SPLIT_STR`( X VARCHAR(255), delim VARCHAR(12), pos INT ) RETURNS VARCHAR(255) CHARSET latin1 DETERMINISTIC RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(X, delim, pos), LENGTH(SUBSTRING_INDEX(X, delim, pos -1)) + 1), delim, '')$$ DELIMITER ; Basically, as it sits, only the user 'thisuser' at any location can use this function, but I want to be able to allow ALL the users of this database access to it, as well, if I were to change this function, i have to go in, and manage every user that would be attached to it, to allow to use it again. I've tried '%'@'%', and I get the error that this user does not exist. Any help? Is it possible? Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Datediff function
John Meyer wrote: I'm trying to pull up a list of users who haven't tweeted in 7 or more days, and I'm trying to use this statement: SELECT USER_NAME, MAX(TWEET_CREATEDAT) FROM USERS NATURAL JOIN TWEETS WHERE DATEDIFF(NOW(),MAX(TWEET_CREATEDAT)) 7 But it says invalid group function. How should I reword this query? Have you tried this? SELECT USER_NAME , MAX(TWEET_CREATEDATE) as latest_tweet FROM USERS NATURAL JOIN TWEETS GROUP BY USERS.USER_ID HAVING DATEDIFF(NOW(),latest_tweet) 7; OR you could build a distinct list (temporary table) of all users who *have* tweeted in the last 7 days and LEFT JOIN the USERS table to that to figure out who isn't on the list. By moving the evaluation to the HAVING clause (which is evaluated after the GROUP BY) you get to filter on the results of the GROUPing operations. The conditions of the WHERE clause are applied before any GROUPing happens. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Datediff function
-Original Message- From: Gavin Towey [mailto:gto...@ffn.com] Sent: Wednesday, September 16, 2009 7:34 PM To: John Meyer; mysql@lists.mysql.com Subject: RE: Datediff function Hi John, You can't use aggregate function in the WHERE clause, because they aren't evaluated until after the WHERE clause is applied. [JS] You can use the HAVING clause, although it is probably inefficient. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com Wouldn't it be much easier to simply keep a last_tweet_date field updated somewhere then simply do SELECT USER_NAME FROM USERS WHERE last_tweet_date NOW()-INTERVAL 7 DAY; ? Regards, Gavin Towey -Original Message- From: John Meyer [mailto:johnme...@pueblocomputing.com] Sent: Wednesday, September 16, 2009 12:52 PM To: mysql@lists.mysql.com Subject: Datediff function I'm trying to pull up a list of users who haven't tweeted in 7 or more days, and I'm trying to use this statement: SELECT USER_NAME, MAX(TWEET_CREATEDAT) FROM USERS NATURAL JOIN TWEETS WHERE DATEDIFF(NOW(),MAX(TWEET_CREATEDAT)) 7 GROUP BY USERS.USER_ID But it says invalid group function. How should I reword this query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Datediff function
Hi John, If judicious transformation of data makes it easier to do the queries you want, then you should consider it. ETL isn't a common acronym in the database world just because we like three letters =) Though it depends on how often you're doing this, if it's one-off then it's probably not worth it, though I was making the assumption you're probably going to be using that query frequently. Regards, Gavin Towey -Original Message- From: John Meyer [mailto:johnme...@pueblocomputing.com] Sent: Wednesday, September 16, 2009 4:51 PM To: Gavin Towey Cc: mysql@lists.mysql.com Subject: Re: Datediff function Gavin Towey wrote: Hi John, You can't use aggregate function in the WHERE clause, because they aren't evaluated until after the WHERE clause is applied. Wouldn't it be much easier to simply keep a last_tweet_date field updated somewhere then simply do SELECT USER_NAME FROM USERS WHERE last_tweet_date NOW()-INTERVAL 7 DAY; ? Regards, Gavin Towey I don't know if that would be so simple. I'd have to run programming logic when I fetch the information off the twitter server. I just hoped that there was a way to do it through SQL. The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Datediff function
I'm trying to pull up a list of users who haven't tweeted in 7 or more days, and I'm trying to use this statement: SELECT USER_NAME, MAX(TWEET_CREATEDAT) FROM USERS NATURAL JOIN TWEETS WHERE DATEDIFF(NOW(),MAX(TWEET_CREATEDAT)) 7 GROUP BY USERS.USER_ID But it says invalid group function. How should I reword this query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Datediff function
Hi John, You can't use aggregate function in the WHERE clause, because they aren't evaluated until after the WHERE clause is applied. Wouldn't it be much easier to simply keep a last_tweet_date field updated somewhere then simply do SELECT USER_NAME FROM USERS WHERE last_tweet_date NOW()-INTERVAL 7 DAY; ? Regards, Gavin Towey -Original Message- From: John Meyer [mailto:johnme...@pueblocomputing.com] Sent: Wednesday, September 16, 2009 12:52 PM To: mysql@lists.mysql.com Subject: Datediff function I'm trying to pull up a list of users who haven't tweeted in 7 or more days, and I'm trying to use this statement: SELECT USER_NAME, MAX(TWEET_CREATEDAT) FROM USERS NATURAL JOIN TWEETS WHERE DATEDIFF(NOW(),MAX(TWEET_CREATEDAT)) 7 GROUP BY USERS.USER_ID But it says invalid group function. How should I reword this query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Datediff function
Gavin Towey wrote: Hi John, You can't use aggregate function in the WHERE clause, because they aren't evaluated until after the WHERE clause is applied. Wouldn't it be much easier to simply keep a last_tweet_date field updated somewhere then simply do SELECT USER_NAME FROM USERS WHERE last_tweet_date NOW()-INTERVAL 7 DAY; ? Regards, Gavin Towey I don't know if that would be so simple. I'd have to run programming logic when I fetch the information off the twitter server. I just hoped that there was a way to do it through SQL. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Write IF condition in Query , and mysql Search function
bharani kumar wrote:
Hi All ,
Am working on the autosuggestion program
SELECT $field2,$field1 FROM $tblname where locate('$q', $field2) 0 OR
locate('$q', $field1) 0 order by locate('$q', $field2),
$field2 limit . ($pagesize * $page) . , . $pagesize
This is my query ,
Example in my Database table .. am having values somthing like
Bombay(BOB),Delhi(DI),Gujarath(GA),Rajasthan(RA),Baroda(BD)
My present act like ...
If user enter thed
then It show the baroda(BD) as first row and Delhi as second result
So can u please guide me here
Also for clear vision please go this link
http://ukatn.com/index_autosuggest.php
Select the Postal code in the Taxi From combo...
Then enter the l as keyword . then u will find the first are start with A
not an L ,
So my expectation is ,
Assume if user enter the L then i want the first result as start with L row
as out ... and if they enter LI then result must lilliput somthing like
that
Am fething columns are postcodename and postcodeCODE ..
IIn the search, the first preference must be field *postcodename *if no
keyword match in the first column then go second column *postcodeCODE *
.
Can u please tell me How to write query for this siutaion
Thanks
The query is hard to write because you are not leveraging the design
qualities of a Relational Database. MySQL does not index every term of a
list of values that are stored within a single field. It indexes the
entire field.
MySQL (or any other RDBMS) can index a list of values stored
one-at-a-time in separate rows of data. This is called normalization
and it can not only improve your search performance but it can also
reduce your storage footprint.
I suggest you modify how your data is stored by implementing the
techniques of normalization. I think it will help get you started.
--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Write IF condition in Query , and mysql Search function
Hi All ,
Am working on the autosuggestion program
SELECT $field2,$field1 FROM $tblname where locate('$q', $field2) 0 OR
locate('$q', $field1) 0 order by locate('$q', $field2),
$field2 limit . ($pagesize * $page) . , . $pagesize
This is my query ,
Example in my Database table .. am having values somthing like
Bombay(BOB),Delhi(DI),Gujarath(GA),Rajasthan(RA),Baroda(BD)
My present act like ...
If user enter thed
then It show the baroda(BD) as first row and Delhi as second result
So can u please guide me here
Also for clear vision please go this link
http://ukatn.com/index_autosuggest.php
Select the Postal code in the Taxi From combo...
Then enter the l as keyword . then u will find the first are start with A
not an L ,
So my expectation is ,
Assume if user enter the L then i want the first result as start with L row
as out ... and if they enter LI then result must lilliput somthing like
that
Am fething columns are postcodename and postcodeCODE ..
IIn the search, the first preference must be field *postcodename *if no
keyword match in the first column then go second column *postcodeCODE *
.
Can u please tell me How to write query for this siutaion
Thanks
Does MySQL have the same function as the ORACLE TDE technique?
Hi. Here is the introduction. http://www.oracle.com/technology/oramag/oracle/05-sep/o55security.html I want to know whether MySQL has the same function as Oracle's? Any reply is appreciated. -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn
Create function ignore deps
I want to check all my functions and procs into my svn as individual sql files. When I use these to create my db, the person doing this may not realize the correct order to run these files and not have dependency challenges... How can I have procs that depend on functions, or vice versa, get successfully created without regard to correct order? Thanks Bryancan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Create function ignore deps
On Wed, May 27, 2009 at 11:24 AM, Cantwell, Bryan bcantw...@firescope.com wrote: I want to check all my functions and procs into my svn as individual sql files. When I use these to create my db, the person doing this may not realize the correct order to run these files and not have dependency challenges... How can I have procs that depend on functions, or vice versa, get successfully created without regard to correct order? Thanks Bryancan I prefixed the filenames of the various discrete files with numeric prefixes like so 10-create-customer.sql 20-finalize-transaction.sql ... and then wrote a shell script to fire them off in sort-order. I deliberately used the numbering convention from line-numbered basic to allow me to inject intervening files without having to renumber the set. -- - michael dykman - mdyk...@gmail.com - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Native Function
Hi All, I am using the mysql embedded library (libmysqld) in my application. I am using triggers and calling a C/C++ function in my application. This is how I convey configuration changes to my application. I think my only choice is to make a Native Function in the mysql source code since UDF is not avialable in libmysqld. It would be nice if there were a generic function for this purpose that would take two string arguments and return an integer. This way I can use it for anywhere I need to tie in triggers to my application. Is there an easier way to accomplish this. I mean invoking an application C/C++ function from mysql stored procedures? Thanks in andvance! Alex
Re: Native Function
Hello Martin, This sounds great! I am not sure if plugins are supported for the embedded mysql applications. Meaning that my application is linked with the libmysqld not libmysql. Do you know the answer? Thanks, Alex On Mon, May 18, 2009 at 10:17 AM, Martin Gainty mgai...@hotmail.com wrote: yes if the library is a plugin create the plugin http://dev.mysql.com/doc/refman/5.1/en/plugin-api.html also make sure the table mysql.plugin is created compile and run mysqld dynamically is a pre-requisite http://dev.mysql.com/doc/refman/5.1/en/plugin-writing.html once the dynamic lib is created follow these instructions to install the plugin http://dev.mysql.com/doc/refman/5.1-maria/en/install-plugin.html Martin Gainty __ Jogi és Bizalmassági kinyilatkoztatás/Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Ez az üzenet bizalmas. Ha nem ön az akinek szánva volt, akkor kérjük, hogy jelentse azt nekünk vissza. Semmiféle továbbítása vagy másolatának készítése nem megengedett. Ez az üzenet csak ismeret cserét szolgál és semmiféle jogi alkalmazhatósága sincs. Mivel az electronikus üzenetek könnyen megváltoztathatóak, ezért minket semmi felelöség nem terhelhet ezen üzenet tartalma miatt. Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. Date: Mon, 18 May 2009 09:33:15 -0400 Subject: Native Function From: alex.kat...@gmail.com To: mysql@lists.mysql.com Hi All, I am using the mysql embedded library (libmysqld) in my application. I am using triggers and calling a C/C++ function in my application. This is how I convey configuration changes to my application. I think my only choice is to make a Native Function in the mysql source code since UDF is not avialable in libmysqld. It would be nice if there were a generic function for this purpose that would take two string arguments and return an integer. This way I can use it for anywhere I need to tie in triggers to my application. Is there an easier way to accomplish this. I mean invoking an application C/C++ function from mysql stored procedures? Thanks in andvance! Alex -- Hotmail® has a new way to see what's up with your friends. Check it out.http://windowslive.com/Tutorial/Hotmail/WhatsNew?ocid=TXT_TAGLM_WL_HM_Tutorial_WhatsNew1_052009
RE: Native Function
good morning alex the libmysqld is a embedded server library http://dev.mysql.com/doc/refman/5.0/en/libmysqld.html instead of mysql calling your C/C++ function your C/C++ function is calling the mysql functions mysql_library_init() Should be called before any other MySQL function is called, preferably early in the main() function. mysql_library_end() Should be called before your program exits. mysql_thread_init() Should be called in each thread you create that accesses MySQL. mysql_thread_end() Should be called before calling pthread_exit() the answer is that you are linking to the server code and calling server functions with libmysqld.a (instead of linking in client code from libmysqlclient.a) HTH Martin Gainty there is also a way to accomplish this with an Oracle Function calling external methods but we should wait until after the merger for that implementation __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. Date: Mon, 18 May 2009 11:55:18 -0400 Subject: Re: Native Function From: alex.kat...@gmail.com To: mgai...@hotmail.com CC: mysql@lists.mysql.com Hello Martin, This sounds great! I am not sure if plugins are supported for the embedded mysql applications. Meaning that my application is linked with the libmysqld not libmysql. Do you know the answer? Thanks, Alex On Mon, May 18, 2009 at 10:17 AM, Martin Gainty mgai...@hotmail.com wrote: yes if the library is a plugin create the plugin http://dev.mysql.com/doc/refman/5.1/en/plugin-api.html also make sure the table mysql.plugin is created compile and run mysqld dynamically is a pre-requisite http://dev.mysql.com/doc/refman/5.1/en/plugin-writing.html once the dynamic lib is created follow these instructions to install the plugin http://dev.mysql.com/doc/refman/5.1-maria/en/install-plugin.html Martin Gainty __ Jogi és Bizalmassági kinyilatkoztatás/Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Ez az üzenet bizalmas. Ha nem ön az akinek szánva volt, akkor kérjük, hogy jelentse azt nekünk vissza. Semmiféle továbbítása vagy másolatának készítése nem megengedett. Ez az üzenet csak ismeret cserét szolgál és semmiféle jogi alkalmazhatósága sincs. Mivel az electronikus üzenetek könnyen megváltoztathatóak, ezért minket semmi felelöség nem terhelhet ezen üzenet tartalma miatt. Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. Date: Mon, 18 May 2009 09:33:15 -0400 Subject: Native Function From: alex.kat...@gmail.com To: mysql@lists.mysql.com Hi All, I am using the mysql embedded library (libmysqld) in my application. I am using triggers and calling a C/C++ function in my application. This is how I convey configuration changes to my application. I think my only choice is to make a Native Function in the mysql source code since UDF is not avialable in libmysqld. It would be nice if there were a generic function for this purpose that would take two string arguments
Re: Retrieving results of a stored function using MySql C API
Yeah, please show us the source code of yours. On Wed, Apr 15, 2009 at 4:10 PM, Venu Gopal neo.v...@gmail.com wrote: Hi guys, I am using stored procedures and stored functions for the first time. And currently stuck at a this point where. I am unable to retrieve results returned by stored function using MySql C API. Kindly let me know how to do so. In case you need details I'll share the source code. Cheers, Venu -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Retrieving results of a stored function using MySql C API
Hi guys, I am using stored procedures and stored functions for the first time. And currently stuck at a this point where. I am unable to retrieve results returned by stored function using MySql C API. Kindly let me know how to do so. In case you need details I'll share the source code. Cheers, Venu
Problem using deterministic stored function
Hi, I am using MySQL 5.0.51a. I've got a problem with a stored function. It reads as follows: CREATE FUNCTION `_contractRoot`(temp INT) RETURNS int(11) READS SQL DATA BEGIN DECLARE _parent_id INT; REPEAT SET _parent_id = temp; SELECT parent_id INTO temp FROM contract WHERE id = _parent_id; UNTIL temp IS NULL END REPEAT; RETURN _parent_id; END There is a table contract containing row groups that form a tree. In the table, the columns id, parent_id and number are defined. Each tree root has set the NULL value for the parent_id column, while the child rows have references to the ID of another row, and by following these references from any child, the root ID will be calculated by this function. Additionaly, there is a value set in the number column if parent_id IS NOT NULL, i.e. the root row has a value set, while the other rows have number = NULL. This maps each tree to a number. (There are 1.500 rows in the contract table.) Now, let's look at these queries: mysql SELECT _contractRoot(320); ++ | _contractRoot(320) | ++ |317 | ++ 1 row in set (0.00 sec) mysql SELECT number FROM contract WHERE id = _contractRoot(320); ++ | number | ++ | 93 | ++ 1 row in set (0.06 sec) As you see, _contractRoot(320) is run in a very short period of time, while the second statement seems to run the _contractRoot function for each line that is processed by the WHERE clause, and therefore takes longer. This was not the case before the upgrade to Debian Lenny. Before (MySQL 5.0.32), the second statement also took nearly 0.00 sec. So, is this a regression? I noticed that the problem can be fixed by specifying DETERMINISTIC as an additional keyword in the CREATE FUNCTION statement. In this case, the second statement also runs in 0.00 sec. But I'm not sure if DETERMINISTIC is legal here. As you see, the function reads data from the table, and it may be that these data changes. So the question is what DETERMINISTIC means! Two options: 1.) DETERMINISTIC means that the function does not depend on variable data and will _always_ return the same value. This corresponds to what the manual says: A procedure or function is considered “deterministic” if it always produces the same result for the same input parameters, and “not deterministic” otherwise. But I'm not sure if always can be construed that strictly here. It would be inconsistent with the READS SQL DATA characteristic. 2.) DETERMINISTIC means that the function does not use any non-constant input except data from the database tables (i.e. no CURRENT_DATE(), random numbers etc.). How would the caching mechanism work in this case? In the second case, I may declare my function DETERMINISTIC, in the first case I may not. Does anybody know what is right here? I did another observations that is closely related to this: Even with the DETERMINISTIC keyword, the following takes long: mysql SELECT id, _contractRoot(320) FROM contract; +--++ | id | _contractRoot(320) | +--++ ... | 1560 |317 | | 1561 |317 | +--++ 1477 rows in set (0.06 sec) The run time seems to be independent of the use of DETERMINISTIC, but to my understanding, there is no need to execute the function more often than when doing mysql SELECT number FROM contract WHERE id = _contractRoot(320); which returns after 0.00 sec. As I said, this behaviour happens despite of DETERMINISTIC. I'm not sure if this is a bug. Thanks, Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
about mysql_ping() function
Hi guys, I used mysql_ping() function in my program. But when I unplug the network wire between client and MySQL server, my program has no response as mysql_ping() can not return value and hanged there. Is this a bug of mysql_ping() ? or any other advice? thanks!!! *^_^* ___ 好玩贺卡等你发，邮箱贺卡全新上线！ http://card.mail.cn.yahoo.com/
Re: about mysql_ping() function
raid fifa wrote: Is this a bug of mysql_ping() ? or any other advice? Probably not. I think you'll find that it *does* time out, just after a longer period than you'd prefer. Timeouts of 30, 60 and 120 seconds are common in network code, because it's not possible to reliably determine that a link is down until then. Your OS might have a setting that makes it close all sockets using an interface that just lost its physical link to the network. This is often more trouble than it's worth, because the link might come back up before any of those sockets need to send data again. If you need immediate notification that the link to the DB is down, though, it might be what you want. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Function call reult in a WHERE-IN clause
I have a function that I built that returns a string that is really a comma separated list of values (below). I would like to use that returned value in an IN clause in sql. : select * from hosts where hostid in (getHosts(10014)); The function: CREATE FUNCTION getUserHosts(userID BIGINT(20) UNSIGNED) RETURNS varchar(4096) CHARSET latin1 BEGIN DECLARE hosts VARCHAR (4096); SELECT GROUP_CONCAT(DISTINCT h.hostid) INTO hosts FROM hosts h LEFT JOIN hosts_groups hg ON hg.hostid = h.hostid LEFT JOIN groups g ON g.groupid = hg.groupid LEFT JOIN rights r ON r.id = g.groupid AND r.type = 1 LEFT JOIN users_groups ug ON ug.usrgrpid = r.groupid LEFT JOIN nodes n ON getNodeFromID(h.hostid) = n.nodeid WHERE ug.userid = userID AND r.permission = 3 AND h.status 4 ORDER BY h.hostid; RETURN hosts; END -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Function call reult in a WHERE-IN clause
Perhaps pass in a separator string arg, default to '' and do: GROUP_CONCAT(DISTINCT h.hostid, separator) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Function returns null when running sql manually works
Below I have a function with a cursor. I have tested the cursor sql
manually and it is fine, I have put the variables into the sql inside
the cursor loop and it returns data too, BUT, executing this function
returns null even though I know the correct info is available. Am I
missing something obvious here?
CREATE TABLE `report_columns` (
`report_id` bigint(20) NOT NULL,
`report_column_id` bigint(20) NOT NULL,
`column_index` smallint(6) NOT NULL,
`column_name` varchar(128) NOT NULL,
`column_alias` varchar(128) NOT NULL,
`visible` char(1) NOT NULL,
`relationship_type_id` bigint(20) NOT NULL,
`relationship_ci` bigint(20) NOT NULL,
`relationship_index` bigint(20) NOT NULL,
`graph_value` tinyint(4) NOT NULL,
`graph_label` tinyint(4) NOT NULL,
PRIMARY KEY (`report_id`,`report_column_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `report_filters` (
`report_id` bigint(20) NOT NULL,
`report_column_id` bigint(20) NOT NULL,
`report_filter_id` bigint(20) NOT NULL default '0',
`filter_condition` varchar(128) default NULL,
`filter_value` varchar(128) default NULL,
`filter_special` varchar(128) default NULL,
`filter_operator` varchar(10) default NULL,
PRIMARY KEY (`report_id`,`report_column_id`,`report_filter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP FUNCTION IF EXISTS firescope.testing;
CREATE FUNCTION firescope.`testing`(RepID BIGINT(20) unsigned) RETURNS
varchar(255)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE filterSQL,filterTMP, colName varchar(255);
DECLARE colID, rID BIGINT(20) unsigned;
DECLARE cur1 CURSOR FOR SELECT report_column_id, column_name
FROM report_columns
WHERE report_id = RepID ORDER BY report_column_id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO colID, colName;
IF NOT done THEN
SELECT concat(' ' ,rvf.filter_operator,'
', colName,' ', (case rvf.filter_condition when 'not_equal' then ''
when 'greater_than' then '' else '=' end)
,' ' ,rvf.filter_value,'
') into filterTMP
FROM report_filters rvf
WHERE rvf.report_id = RepID
and rvf.report_column_id
= colID;
IF filterTMP is NOT null then
IF filterSQL is null
then
select
filterTMP into filterSQL;
ELSE
select
concat(filterSQL,filterTMP) into filterSQL;
END IF;
END IF;
SET filterTMP = null;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
return filterSQL;
END;
RE: Function returns null when running sql manually works
OK, I know WHY it is returning null, just not WHAT to do about it.
In the inside sql, there is not always a result. So, done becomes 1 and
the repeat exits.
How can I keep from this happening? How could I make another 'done' like
variable that would not get also set to 1 if the inner sql doesn't
return a record in this pass?
thx
-Original Message-
From: Cantwell, Bryan [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 13, 2008 4:17 PM
To: mysql@lists.mysql.com
Subject: Function returns null when running sql manually works
Below I have a function with a cursor. I have tested the cursor sql
manually and it is fine, I have put the variables into the sql inside
the cursor loop and it returns data too, BUT, executing this function
returns null even though I know the correct info is available. Am I
missing something obvious here?
CREATE TABLE `report_columns` (
`report_id` bigint(20) NOT NULL,
`report_column_id` bigint(20) NOT NULL,
`column_index` smallint(6) NOT NULL,
`column_name` varchar(128) NOT NULL,
`column_alias` varchar(128) NOT NULL,
`visible` char(1) NOT NULL,
`relationship_type_id` bigint(20) NOT NULL,
`relationship_ci` bigint(20) NOT NULL,
`relationship_index` bigint(20) NOT NULL,
`graph_value` tinyint(4) NOT NULL,
`graph_label` tinyint(4) NOT NULL,
PRIMARY KEY (`report_id`,`report_column_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `report_filters` (
`report_id` bigint(20) NOT NULL,
`report_column_id` bigint(20) NOT NULL,
`report_filter_id` bigint(20) NOT NULL default '0',
`filter_condition` varchar(128) default NULL,
`filter_value` varchar(128) default NULL,
`filter_special` varchar(128) default NULL,
`filter_operator` varchar(10) default NULL,
PRIMARY KEY (`report_id`,`report_column_id`,`report_filter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP FUNCTION IF EXISTS firescope.testing;
CREATE FUNCTION firescope.`testing`(RepID BIGINT(20) unsigned) RETURNS
varchar(255)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE filterSQL,filterTMP, colName varchar(255);
DECLARE colID, rID BIGINT(20) unsigned;
DECLARE cur1 CURSOR FOR SELECT report_column_id, column_name
FROM report_columns
WHERE report_id = RepID ORDER BY report_column_id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO colID, colName;
IF NOT done THEN
SELECT concat(' ' ,rvf.filter_operator,'
', colName,' ', (case rvf.filter_condition when 'not_equal' then ''
when 'greater_than' then '' else '=' end)
,' ' ,rvf.filter_value,'
') into filterTMP
FROM report_filters rvf
WHERE rvf.report_id = RepID
and rvf.report_column_id
= colID;
IF filterTMP is NOT null then
IF filterSQL is null
then
select
filterTMP into filterSQL;
ELSE
select
concat(filterSQL,filterTMP) into filterSQL;
END IF;
END IF;
SET filterTMP = null;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
return filterSQL;
END;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Function returns null when running sql manually works
I have the solution, had to add a begin/end inside the repeat to protect
my original done var and declare another inside the loop.
I'm glad we had this little talk =P
l8r
DELIMITER $$
DROP FUNCTION IF EXISTS `firescope`.`reportWhereClause`$$
CREATE [EMAIL PROTECTED] FUNCTION `reportWhereClause`(RepID
BIGINT(20) UNSIGNED) RETURNS varchar(255) CHARSET latin1
BEGIN
DECLARE doneINT DEFAULT 0;
DECLARE filterSQL, filterTMP, colName VARCHAR (255);
DECLARE colID, rID BIGINT (20) UNSIGNED;
DECLARE cur1 CURSOR FOR SELECT
report_column_id, column_name
FROM
report_columns
WHERE
report_id = RepID
ORDER BY
report_column_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO colID, colName;
IF NOT done THEN
BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN END;
SELECT CONCAT(' ', rvf.filter_operator, ' ', colName, ' ', (CASE
rvf.filter_condition WHEN 'not_equal' THEN '' WHEN 'greater_than' THEN
'' ELSE '=' END)
, ' ', , rvf.filter_value,, ' ')
INTO
filterTMP
FROM
report_filters rvf
WHERE
rvf.report_id = RepID
AND rvf.report_column_id = colID;
IF filterTMP IS NOT NULL THEN
IF filterSQL IS NULL THEN
SELECT
filterTMP
INTO
filterSQL;
ELSE
SELECT
CONCAT(filterSQL, filterTMP)
INTO
filterSQL;
END IF;
END IF;
SET filterTMP = NULL;
END;
END IF;
UNTIL done
END REPEAT;
CLOSE cur1;
RETURN filterSQL;
END$$
DELIMITER ;
-Original Message-
From: Cantwell, Bryan [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 13, 2008 5:13 PM
To: mysql@lists.mysql.com
Subject: RE: Function returns null when running sql manually works
OK, I know WHY it is returning null, just not WHAT to do about it.
In the inside sql, there is not always a result. So, done becomes 1 and
the repeat exits.
How can I keep from this happening? How could I make another 'done' like
variable that would not get also set to 1 if the inner sql doesn't
return a record in this pass?
thx
-Original Message-
From: Cantwell, Bryan [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 13, 2008 4:17 PM
To: mysql@lists.mysql.com
Subject: Function returns null when running sql manually works
Below I have a function with a cursor. I have tested the cursor sql
manually and it is fine, I have put the variables into the sql inside
the cursor loop and it returns data too, BUT, executing this function
returns null even though I know the correct info is available. Am I
missing something obvious here?
CREATE TABLE `report_columns` (
`report_id` bigint(20) NOT NULL,
`report_column_id` bigint(20) NOT NULL,
`column_index` smallint(6) NOT NULL,
`column_name` varchar(128) NOT NULL,
`column_alias` varchar(128) NOT NULL,
`visible` char(1) NOT NULL,
`relationship_type_id` bigint(20) NOT NULL,
`relationship_ci` bigint(20) NOT NULL,
`relationship_index` bigint(20) NOT NULL,
`graph_value` tinyint(4) NOT NULL,
`graph_label` tinyint(4) NOT NULL,
PRIMARY KEY (`report_id`,`report_column_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `report_filters` (
`report_id` bigint(20) NOT NULL,
`report_column_id` bigint(20) NOT NULL,
`report_filter_id` bigint(20) NOT NULL default '0',
`filter_condition` varchar(128) default NULL,
`filter_value` varchar(128) default NULL,
`filter_special` varchar(128) default NULL,
`filter_operator` varchar(10) default NULL,
PRIMARY KEY (`report_id`,`report_column_id`,`report_filter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP FUNCTION IF EXISTS firescope.testing;
CREATE FUNCTION firescope.`testing`(RepID BIGINT(20) unsigned) RETURNS
varchar(255)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE filterSQL,filterTMP, colName varchar(255);
DECLARE colID, rID BIGINT(20) unsigned;
DECLARE cur1 CURSOR FOR SELECT report_column_id, column_name
FROM report_columns
WHERE report_id = RepID ORDER BY report_column_id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO colID, colName;
IF NOT done THEN
SELECT concat(' ' ,rvf.filter_operator,'
', colName,' ', (case rvf.filter_condition when 'not_equal' then ''
when 'greater_than' then '' else '=' end)
,' ' ,rvf.filter_value,'
') into filterTMP
FROM report_filters rvf
WHERE rvf.report_id = RepID
and rvf.report_column_id
= colID;
IF filterTMP is NOT null
Question about Averaging IF() function results
Hello List, I have a question about trying to calculate an average across columns. I am trying to calculate the results of surveys where in the data I have individuals that have marked questions on the survey as N/A. in my survey I am using 1-6 as the evaluated answers and if the person marked NA the stored value is 7. Here is a table with some sample data of what I am using to test the calculation I am working on: ( actually this is simplified from the actual data but the results I get are still the same) CREATE TABLE `avgTest` ( `Course` varchar(8) default NULL, `Q1` int(11) default NULL, `Q2` int(11) default NULL, `Q3` int(11) default NULL, `Q4` int(11) default NULL, `Q5` int(11) default NULL ) Course|Q1|Q2|Q3|Q4|Q5 - HUM300 |6 | 6 | 7 | 6 |6 HUM301 |6 | 6 | 6 | 6 |6 HUM301 |7 | 7 | 7 | 7 |7 Here is the query that I am using to perform the calculations select course, avg(IF(avgTest.Q17,avgTest.Q1,Null)) as AvgOfQ1, avg(IF(avgTest.Q27,avgTest.Q2,Null)) as AvgOfQ2, avg(IF(avgTest.Q37,avgTest.Q3,Null)) as AvgOfQ3, avg(IF(avgTest.Q47,avgTest.Q4,Null)) as AvgOfQ4, avg(IF(avgTest.Q57,avgTest.Q5,Null)) as AvgOfQ5, (avg(IF(avgTest.Q17,avgTest.Q1,Null)) +avg(IF(avgTest.Q27,avgTest.Q2,Null)) +avg(IF(avgTest.Q37,avgTest.Q3,Null)) +avg(IF(avgTest.Q47,avgTest.Q4,Null)) +avg(IF(avgTest.Q57,avgTest.Q5,Null)))/5 as overallAvg from avgTest group by course; Here are the results that I get that are incorrect. Course|AvgOfQ1|AvgOfQ2|AvgOfQ3|AvgOfQ4| AvgOfQ5|overallAvg - -- HUM300 | 6.000 |6.000 | Null| 6.000 |6.000 | Null HUM301 | 6.000 |6.000 | 6.000 | 6.000 |6.000 | 6.000 Here are the results that I get that when I change using null in the query to a 0. Course|AvgOfQ1|AvgOfQ2|AvgOfQ3|AvgOfQ4| AvgOfQ5|overallAvg - -- HUM300 | 6.000 |6.000 | 0.000 | 6.000 |6.000 | 4.800 HUM301 | 6.000 |6.000 | 6.000 | 6.000 |6.000 | 6.000 Here are the results that I want to be getting from the query that I am working with. Course|AvgOfQ1|AvgOfQ2|AvgOfQ3|AvgOfQ4| AvgOfQ5|overallAvg - -- HUM300 | 6.000 |6.000 | Null| 6.000 |6.000 | 6.000 HUM301 | 6.000 |6.000 | 6.000 | 6.000 |6.000 | 6.000 I tried using the if function without a false answer and I am getting a syntax error when I do this. If it is possible for me to get this correct result in MySQL, can someone provide me with the correct query syntax to get these results? Thank you Eric H. Lommatsch Programmer 360 Business 2087 South Grant Street Denver, CO 80210 Tel 303-777-8939 Fax 303-778-0378 [EMAIL PROTECTED]
Re: Question about Averaging IF() function results
Eric, I'd replace (avg(IF(avgTest.Q17,avgTest.Q1,Null)) +avg(IF(avgTest.Q27,avgTest.Q2,Null)) +avg(IF(avgTest.Q37,avgTest.Q3,Null)) +avg(IF(avgTest.Q47,avgTest.Q4,Null)) +avg(IF(avgTest.Q57,avgTest.Q5,Null)))/5 as overallAvg from avgTest group by course; with ... (IF(avgTest.Q17,avgTest.Q1,0) + IF(avgTest.Q27,avgTest.Q2,0) + IF(avgTest.Q37,avgTest.Q3,0)+ (IF(avgTest.Q17,avgTest.Q1,0)+ IF (avgTest.Q27,avgTest.Q2,0)+ IF (avgTest.Q37,avgTest.Q3,0)+ IF(avgTest.Q47,avgTest.Q4,0) + IF(avgTest.Q57,avgTest.Q5,0)) / MAX(1,IF(avgTest.Q17,1,0) + IF(avgTest.Q27,1,0) + IF(avgTest.Q37,1,0) + IF(avgTest.Q47,1,0) + IF(avgTest.Q57,1,0)) PB Eric Lommatsch wrote: Hello List, I have a question about trying to calculate an average across columns. I am trying to calculate the results of surveys where in the data I have individuals that have marked questions on the survey as N/A. in my survey I am using 1-6 as the evaluated answers and if the person marked NA the stored value is 7. Here is a table with some sample data of what I am using to test the calculation I am working on: ( actually this is simplified from the actual data but the results I get are still the same) CREATE TABLE `avgTest` ( `Course` varchar(8) default NULL, `Q1` int(11) default NULL, `Q2` int(11) default NULL, `Q3` int(11) default NULL, `Q4` int(11) default NULL, `Q5` int(11) default NULL ) Course|Q1|Q2|Q3|Q4|Q5 - HUM300 |6 | 6 | 7 | 6 |6 HUM301 |6 | 6 | 6 | 6 |6 HUM301 |7 | 7 | 7 | 7 |7 Here is the query that I am using to perform the calculations select course, avg(IF(avgTest.Q17,avgTest.Q1,Null)) as AvgOfQ1, avg(IF(avgTest.Q27,avgTest.Q2,Null)) as AvgOfQ2, avg(IF(avgTest.Q37,avgTest.Q3,Null)) as AvgOfQ3, avg(IF(avgTest.Q47,avgTest.Q4,Null)) as AvgOfQ4, avg(IF(avgTest.Q57,avgTest.Q5,Null)) as AvgOfQ5, (avg(IF(avgTest.Q17,avgTest.Q1,Null)) +avg(IF(avgTest.Q27,avgTest.Q2,Null)) +avg(IF(avgTest.Q37,avgTest.Q3,Null)) +avg(IF(avgTest.Q47,avgTest.Q4,Null)) +avg(IF(avgTest.Q57,avgTest.Q5,Null)))/5 as overallAvg from avgTest group by course; Here are the results that I get that are incorrect. Course|AvgOfQ1|AvgOfQ2|AvgOfQ3|AvgOfQ4|AvgOfQ5| overallAvg --- HUM300 | 6.000 | 6.000 | Null| 6.000 |6.000 | Null HUM301 | 6.000 | 6.000 | 6.000 | 6.000 |6.000 | 6.000 Here are the results that I get that when I change using null in the query to a 0. Course|AvgOfQ1|AvgOfQ2|AvgOfQ3|AvgOfQ4|AvgOfQ5| overallAvg --- HUM300 | 6.000 | 6.000 | 0.000 | 6.000 |6.000 | 4.800 HUM301 | 6.000 | 6.000 | 6.000 | 6.000 |6.000 | 6.000 Here are the results that I want to be getting from the query that I am working with. Course|AvgOfQ1|AvgOfQ2|AvgOfQ3|AvgOfQ4|AvgOfQ5| overallAvg --- HUM300 | 6.000 | 6.000 | Null| 6.000 |6.000 | 6.000 HUM301 | 6.000 | 6.000 | 6.000 | 6.000 |6.000 | 6.000 I tried using the if function without a false answer and I am getting a syntax error when I do this. If it is possible for me to get this correct result in MySQL, can someone provide me with the correct query syntax to get these results? Thank you Eric H. Lommatsch Programmer 360 Business 2087 South Grant Street Denver, CO 80210 Tel 303-777-8939 Fax 303-778-0378 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.175 / Virus Database: 270.8.5/1764 - Release Date: 11/3/2008 7:46 AM
RE: Question about Averaging IF() function results
Hello Peter, Thanks for your suggestion, I think I have found another way to get the average that I need. If the formula I have come up with does not work I will try your formula. Thank you Eric H. Lommatsch Programmer 360 Business 2087 South Grant Street Denver, CO 80210 Tel 303-777-8939 Fax 303-778-0378 [EMAIL PROTECTED] From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 04, 2008 1:14 PM To: Eric Lommatsch Cc: mysql@lists.mysql.com Subject: Re: Question about Averaging IF() function results Eric, I'd replace (avg(IF(avgTest.Q17,avgTest.Q1,Null)) +avg(IF(avgTest.Q27,avgTest.Q2,Null)) +avg(IF(avgTest.Q37,avgTest.Q3,Null)) +avg(IF(avgTest.Q47,avgTest.Q4,Null)) +avg(IF(avgTest.Q57,avgTest.Q5,Null)))/5 as overallAvg from avgTest group by course; with ... (IF(avgTest.Q17,avgTest.Q1,0) + IF(avgTest.Q27,avgTest.Q2,0) + IF(avgTest.Q37,avgTest.Q3,0)+ (IF(avgTest.Q17,avgTest.Q1,0)+ IF (avgTest.Q27,avgTest.Q2,0)+ IF (avgTest.Q37,avgTest.Q3,0)+ IF(avgTest.Q47,avgTest.Q4,0) + IF(avgTest.Q57,avgTest.Q5,0)) / MAX(1,IF(avgTest.Q17,1,0) + IF(avgTest.Q27,1,0) + IF(avgTest.Q37,1,0) + IF(avgTest.Q47,1,0) + IF(avgTest.Q57,1,0)) PB Eric Lommatsch wrote: Hello List, I have a question about trying to calculate an average across columns. I am trying to calculate the results of surveys where in the data I have individuals that have marked questions on the survey as N/A. in my survey I am using 1-6 as the evaluated answers and if the person marked NA the stored value is 7. Here is a table with some sample data of what I am using to test the calculation I am working on: ( actually this is simplified from the actual data but the results I get are still the same) CREATE TABLE `avgTest` ( `Course` varchar(8) default NULL, `Q1` int(11) default NULL, `Q2` int(11) default NULL, `Q3` int(11) default NULL, `Q4` int(11) default NULL, `Q5` int(11) default NULL ) Course|Q1|Q2|Q3|Q4|Q5 - HUM300 |6 | 6 | 7 | 6 |6 HUM301 |6 | 6 | 6 | 6 |6 HUM301 |7 | 7 | 7 | 7 |7 Here is the query that I am using to perform the calculations select course, avg(IF(avgTest.Q17,avgTest.Q1,Null)) as AvgOfQ1, avg(IF(avgTest.Q27,avgTest.Q2,Null)) as AvgOfQ2, avg(IF(avgTest.Q37,avgTest.Q3,Null)) as AvgOfQ3, avg(IF(avgTest.Q47,avgTest.Q4,Null)) as AvgOfQ4, avg(IF(avgTest.Q57,avgTest.Q5,Null)) as AvgOfQ5, (avg(IF(avgTest.Q17,avgTest.Q1,Null)) +avg(IF(avgTest.Q27,avgTest.Q2,Null)) +avg(IF(avgTest.Q37,avgTest.Q3,Null)) +avg(IF(avgTest.Q47,avgTest.Q4,Null)) +avg(IF(avgTest.Q57,avgTest.Q5,Null)))/5 as overallAvg from avgTest group by course; Here are the results that I get that are incorrect. Course|AvgOfQ1|AvgOfQ2|AvgOfQ3| AvgOfQ4|AvgOfQ5|overallAvg - -- HUM300 | 6.000 |6.000 | Null| 6.000 |6.000 | Null HUM301 | 6.000 |6.000 | 6.000 | 6.000 |6.000 | 6.000 Here are the results that I get that when I change using null in the query to a 0. Course|AvgOfQ1|AvgOfQ2|AvgOfQ3| AvgOfQ4|AvgOfQ5|overallAvg - -- HUM300 | 6.000 |6.000 | 0.000 | 6.000 |6.000 | 4.800 HUM301 | 6.000 |6.000 | 6.000 | 6.000 |6.000 | 6.000 Here are the results that I want to be getting from the query that I am working with. Course|AvgOfQ1|AvgOfQ2|AvgOfQ3| AvgOfQ4|AvgOfQ5|overallAvg - -- HUM300 | 6.000 |6.000 | Null| 6.000 |6.000 | 6.000 HUM301 | 6.000
Re: MySQL 5.1 Function Creation
Make sure your log_bin_trust_function_creator is on. On Sun, Sep 28, 2008 at 3:04 AM, Jesse [EMAIL PROTECTED] wrote: I'm trying to use existing functions from a restored database from 5.0xx to 5.1, and get an error about the mysql.proc table is missing or corrupt. The mysql.proc table appears to be there, and does not appear to be corrupt. I did a grant select on mysql.proc to user, and that did not make any difference, as it has in the past. So, I decided that I'd delete the function from the database, and try to add it back in, and when I do, I get an error, Failed to CREATE FUNCTION. The code that I'm trying to execute is as follows: CREATE DEFINER = 'root'@'localhost' FUNCTION `Age`(dob DATE) RETURNS int(11) DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN DECLARE today DATE; SELECT CampStartDate INTO today FROM config; RETURN DATE_FORMAT(FROM_DAYS(TO_DAYS(today) - TO_DAYS(dob)), '%Y') + 0; END; Any ideas what's going on? Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
MySQL 5.1 Function Creation
I'm trying to use existing functions from a restored database from 5.0xx to 5.1, and get an error about the mysql.proc table is missing or corrupt. The mysql.proc table appears to be there, and does not appear to be corrupt. I did a grant select on mysql.proc to user, and that did not make any difference, as it has in the past. So, I decided that I'd delete the function from the database, and try to add it back in, and when I do, I get an error, Failed to CREATE FUNCTION. The code that I'm trying to execute is as follows: CREATE DEFINER = 'root'@'localhost' FUNCTION `Age`(dob DATE) RETURNS int(11) DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN DECLARE today DATE; SELECT CampStartDate INTO today FROM config; RETURN DATE_FORMAT(FROM_DAYS(TO_DAYS(today) - TO_DAYS(dob)), '%Y') + 0; END; Any ideas what's going on? Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is there a GROUP function that can help me with this?
Hi MySQL Users- I have a query problem I have been working on for quite some time and I am really at a loss to find a native function(s) to handle my task. I have this table: CREATE TABLE BookCategoryMetrics ( BookName VARCHAR(255), CategoryId VARCHAR(128), RatingSum DOUBLE, Cost DOUBLE, PRIMARY KEY (BookName,CategoryId) ); There is a 1:1 relationship between BookName and CategoryId. There are approximately 2 million unique values for BookName and 100 unique values for CategoryId. My goal is to create a report, that lists the Top 100 most expensive BookNames, for every CategoryId in this table. Obviously, I could write a wrapper script to loop through the CategoryId and pass them 1 at a time to this query to get the results, but this is obviously not the most efficient. SELECT BookName, CategoryId, SUM(Cost) as TotalCost FROM BookCategoryMetrics WHERE CategoryId = 100 GROUP BY BookName,CategoryId ORDER BY TotalCost DESC LIMIT 100; Is there even a way to do this with straight MySQL, or is this a candidate for some kind of stored procedure? Thank you for any guidance! David
Re: Is there a GROUP function that can help me with this?
David, My goal is to create a report, that lists the Top 100 most expensive BookNames, for every CategoryId in this table. I think you can map the example under Within-group quotas (Top N per group) at http://www.artfulsoftware.com/infotree/queries.php to your requirement. PB David Perron wrote: Hi MySQL Users- I have a query problem I have been working on for quite some time and I am really at a loss to find a native function(s) to handle my task. I have this table: CREATE TABLE BookCategoryMetrics ( BookName VARCHAR(255), CategoryId VARCHAR(128), RatingSum DOUBLE, Cost DOUBLE, PRIMARY KEY (BookName,CategoryId) ); There is a 1:1 relationship between BookName and CategoryId. There are approximately 2 million unique values for BookName and 100 unique values for CategoryId. My goal is to create a report, that lists the Top 100 most expensive BookNames, for every CategoryId in this table. Obviously, I could write a wrapper script to loop through the CategoryId and pass them 1 at a time to this query to get the results, but this is obviously not the most efficient. SELECT BookName, CategoryId, SUM(Cost) as TotalCost FROM BookCategoryMetrics WHERE CategoryId = 100 GROUP BY BookName,CategoryId ORDER BY TotalCost DESC LIMIT 100; Is there even a way to do this with straight MySQL, or is this a candidate for some kind of stored procedure? Thank you for any guidance! David No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.138 / Virus Database: 270.6.6/1623 - Release Date: 8/20/2008 8:12 AM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to Get file modified time and date of file by using builtin function or procedure in sql?
Hi All, how to Get file modified time and date of file by using builtin function or procedure in sql? Thanks, Siva -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to Get file modified time and date of file by using builtin function or procedure in sql?
which file are u talking about. regards anandkl On 7/24/08, Sivasakthi [EMAIL PROTECTED] wrote: Hi All, how to Get file modified time and date of file by using builtin function or procedure in sql? Thanks, Siva -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to Get file modified time and date of file by using builtin function or procedure in sql?
Ananda Kumar wrote: which file are u talking about. regards anandkl Ordinary temp file.. say example in C:\output.txt Actually i have given that file as input to some procedure, based on that modification time we have calculate some details. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to Get file modified time and date of file by using builtin function or procedure in sql?
you can do that using OS command, any specific reason u want to use stored proc to get this information. On 7/24/08, Sivasakthi [EMAIL PROTECTED] wrote: Ananda Kumar wrote: which file are u talking about. regards anandkl Ordinary temp file.. say example in C:\output.txt Actually i have given that file as input to some procedure, based on that modification time we have calculate some details.
Re: how to Get file modified time and date of file by using builtin function or procedure in sql?
Ananda Kumar wrote: you can do that using OS command, any specific reason u want to use stored proc to get this information. OS Command? could you explain with examples? because, I have more # of input files.. thats why iam going to procedure.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to Get file modified time and date of file by using builtin function or procedure in sql?
If windows u can use dir file_name Volume in drive C has no label. Volume Serial Number is 9822-5D20 Directory of C:\ 07/26/2005 02:26 AM 1,442 archival_s.pls 1 File(s) 1,442 bytes 0 Dir(s) 5,230,874,624 bytes free in unix ls -ltr file_name ls -ltr abc.txt -rw-r--r-- 1 root root 34296 Jul 23 15:07 abc.txt Is this what your looking at? On 7/24/08, Sivasakthi [EMAIL PROTECTED] wrote: Ananda Kumar wrote: you can do that using OS command, any specific reason u want to use stored proc to get this information. OS Command? could you explain with examples? because, I have more # of input files.. thats why iam going to procedure..
Re: how to Get file modified time and date of file by using builtin function or procedure in sql?
how to Get file modified time and date of file by using builtin function or procedure in sql? On Codd's rules, it oughtn't to be possible---it'd be a backdoor. If there is a need to know the datetime of the last mod to a table, that info ought to be in a column in a table. PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to Get file modified time and date of file by using builtin function or procedure in sql?
Sivasakthi wrote: Ananda Kumar wrote: which file are u talking about. regards anandkl Ordinary temp file.. say example in C:\output.txt Actually i have given that file as input to some procedure, based on that modification time we have calculate some details. It sounds like you are using a procedural language to call MySQL. Why don't you use that language to obtain the file info? -John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to Get file modified time and date of file by using builtin function or procedure in sql?
On Thu, Jul 24, 2008 at 1:17 AM, Sivasakthi [EMAIL PROTECTED] wrote: Hi All, how to Get file modified time and date of file by using builtin function or procedure in sql? In a related thread from earlier today you were advised that any interaction with the filesystem should be done via a programming language of your choice. Did you have any luck writing a program to do just that? Kevin. -- [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
nested function does not work
Is there are a reason why this wouldn't work? select upper( monarch.group_decode(lower(hg.alias)) ) from nagios.nagios_hostgroups hg; ++ | upper( monarch.group_decode(lower(hg.alias)) ) | ++ | database servers | mysql select lower( monarch.group_decode(hg.alias) ) from mysql nagios.nagios_hostgroups hg; +-+ | lower( monarch.group_decode(hg.alias) ) | +-+ | Database servers | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function Still Not Working
you not ADD Binary, you need to remove BINARY ... ;-) Sorry, I misunderstood. convert the string to latin1 or utf8 o.s.s. LOWER() (and UPPER()) are ineffective when applied to binary strings (BINARY, VARBINARY, BLOB). To perform lettercase conversion, convert the string to a non-binary string did you tried? Well, I thought I had tried this, but with all the other things that I'd tried, I guess I had everything mixed up. I started with a clean routine, used Convert(... using UTF8), and it works perfectly now. That's what it was. Thanks for the help! Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Function Still Not Working
-Original Message- From: Jesse [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2008 1:40 PM To: Martijn Tonies; MySQL List Subject: Re: Function Still Not Working Any difference in default collation? Not sure what that is. I'm using a visual tool (EMS) to create my function, and it doesn't offer that option. I could update it using the command prompt, however. I may try that later. I think what he means is... in one instance of the function, the data is collated as latin-iso-blahblah, perhaps, and a different collation (one without case sensitivity, eh?) in the other table... As I don't deal with letters/characters outside of the 'standard' Latin-iso-asdfasdf collation, I'm afraid there's not much else I can explain using my limited knowledge. Hopefully, though, that helped to give you an idea of what he was driving at. ;) Todd Boyd Web Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Function Still Not Working
Sorry for posting this again, but I got only one response last time, and I'm
still having the problem. I spent HOURS the other day manually going
through the data and Properizing these things by hand. I don't want to do
that again if I can avoid it. If anyone has any clues on this one, I would
appreciate it.
The only difference in this and what I have now is that someone suggested
changing it to Deterministic, which I did, and that didn't change the
output. I also changed SQL SECURITY DEFINER to SQL SECURITY INVOKER,
and that didn't make a difference either.
I have the following function on two servers:
CREATE FUNCTION `ProperCase`(cInput TEXT)
RETURNS text
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
Declare cReturn Text;
Set cReturn = CONCAT(UPPER(SUBSTRING(cInput,1,1)),LOWER(SUBSTRING(cInput
FROM 2)));
RETURN cReturn;
END;
It's a very simple function used to properize a string sent to it. When I do
a simple SELECT ProperCase('JESSE'); it returns JESSE on our server that
is running 5.0.17-nt-log. On another server that I've got, running
5.0.51a-community-nt, this function returns Jesse as it should.
The only difference that I can think of is the version. Is there a problem
with the older version that would cause this function not to work properly?
Thanks,
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function Still Not Working
Hey,
Sorry for posting this again, but I got only one response last time, and
I'm
still having the problem. I spent HOURS the other day manually going
through the data and Properizing these things by hand. I don't want to do
that again if I can avoid it. If anyone has any clues on this one, I
would
appreciate it.
The only difference in this and what I have now is that someone suggested
changing it to Deterministic, which I did, and that didn't change the
output. I also changed SQL SECURITY DEFINER to SQL SECURITY INVOKER,
and that didn't make a difference either.
I have the following function on two servers:
CREATE FUNCTION `ProperCase`(cInput TEXT)
RETURNS text
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
Declare cReturn Text;
Set cReturn =
CONCAT(UPPER(SUBSTRING(cInput,1,1)),LOWER(SUBSTRING(cInput
FROM 2)));
RETURN cReturn;
END;
It's a very simple function used to properize a string sent to it. When I
do
a simple SELECT ProperCase('JESSE'); it returns JESSE on our server that
is running 5.0.17-nt-log. On another server that I've got, running
5.0.51a-community-nt, this function returns Jesse as it should.
The only difference that I can think of is the version. Is there a problem
with the older version that would cause this function not to work
properly?
Any difference in default collation?
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function Still Not Working
Jesse schrieb:
Sorry for posting this again, but I got only one response last time, and
I'm still having the problem. I spent HOURS the other day manually
going through the data and Properizing these things by hand. I don't
want to do that again if I can avoid it. If anyone has any clues on
this one, I would appreciate it.
The only difference in this and what I have now is that someone
suggested changing it to Deterministic, which I did, and that didn't
change the output. I also changed SQL SECURITY DEFINER to SQL
SECURITY INVOKER, and that didn't make a difference either.
I have the following function on two servers:
CREATE FUNCTION `ProperCase`(cInput TEXT)
RETURNS text
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
Declare cReturn Text;
Set cReturn = CONCAT(UPPER(SUBSTRING(cInput,1,1)),LOWER(SUBSTRING(cInput
FROM 2)));
RETURN cReturn;
END;
It's a very simple function used to properize a string sent to it. When
I do
a simple SELECT ProperCase('JESSE'); it returns JESSE on our server that
is running 5.0.17-nt-log. On another server that I've got, running
5.0.51a-community-nt, this function returns Jesse as it should.
does it work outside the function?
did you tried SUBSTRING(cInput, 2)?
did you tried with converting?
from the manual: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
LOWER() (and UPPER()) are ineffective when applied to binary strings
(BINARY, VARBINARY, BLOB). To perform lettercase conversion, convert the
string to a non-binary string:
mysql SET @str = BINARY 'New York';
mysql SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Function Still Not Working
-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 12, 2008 6:23 AM
To: MySQL List
Subject: Re: Function Still Not Working
Hey,
Sorry for posting this again, but I got only one response last time,
and
I'm
still having the problem. I spent HOURS the other day manually
going
through the data and Properizing these things by hand. I don't want
to do
that again if I can avoid it. If anyone has any clues on this one,
I
would
appreciate it.
---8--- snip
It's a very simple function used to properize a string sent to it.
When I
do
a simple SELECT ProperCase('JESSE'); it returns JESSE on our
server
that
is running 5.0.17-nt-log. On another server that I've got, running
5.0.51a-community-nt, this function returns Jesse as it should.
The only difference that I can think of is the version. Is there a
problem
with the older version that would cause this function not to work
properly?
Any difference in default collation?
I am curious about that, as well. It brings to mind a discussion that
happened on this list last week (I believe) about case
sensitive/insensitive use of LIKE. I believe the synopsis was that
tables are either created as case-insensitive, or the search needs to be
specified as case sensitive (with BINARY).
Could this be a similar issue, perhaps? One table is specifically
case-insensitive with regard to the function, and the other is not?
Just spit-balling...
Todd Boyd
Web Programmer
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function Still Not Working
I am curious about that, as well. It brings to mind a discussion that happened on this list last week (I believe) about case sensitive/insensitive use of LIKE. I believe the synopsis was that tables are either created as case-insensitive, or the search needs to be specified as case sensitive (with BINARY). Could this be a similar issue, perhaps? One table is specifically case-insensitive with regard to the function, and the other is not? In this particular case, we're not dealing with any tables. I have also tried adding BINARY to the mix as well, and it didn't make any difference. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function Still Not Working
Any difference in default collation? Not sure what that is. I'm using a visual tool (EMS) to create my function, and it doesn't offer that option. I could update it using the command prompt, however. I may try that later. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function Still Not Working
does it work outside the function?
Yes, If I run:
select CONCAT(UPPER(SUBSTRING('JESSE',1,1)),LOWER(SUBSTRING('JESSE',2)));
replacing cInput with 'JESSE', it returns Jesse as it should.
did you tried SUBSTRING(cInput, 2)?
Tried replacing SUBSTRING(cInput FROM 2) with SUBSTRING(cInput, 2) and it
didn't make any difference.
did you tried with converting?
I have had issues with this in other areas before, but didn't think about it
this time. However, I tred CONVERT with UTF8 and latin1 as you suggested.
LOWER() (and UPPER()) are ineffective when applied to binary strings
(BINARY, VARBINARY, BLOB). To perform lettercase conversion, convert the
string to a non-binary string:
mysql SET @str = BINARY 'New York';
mysql SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));
I converted the function over to use a variable, and got the same results.
Here's the new function:
CREATE FUNCTION `ProperCase`(cInput TEXT)
RETURNS text
DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
COMMENT ''
BEGIN
Declare str Text;
Declare cReturn Text;
Set @str=BINARY cInput;
Set @cReturn =
CONCAT(UPPER(SUBSTRING(@str,1,1)),LOWER(SUBSTRING(@str,2)));
RETURN @cReturn;
END;
Still doesn't work. This is driving me NUTz 8-p
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Function Not Working
I have the following function on two servers:
CREATE FUNCTION `ProperCase`(cInput TEXT)
RETURNS text
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
Declare cReturn Text;
Set cReturn = CONCAT(UPPER(SUBSTRING(cInput,1,1)),LOWER(SUBSTRING(cInput
FROM 2)));
RETURN cReturn;
END;
It's a very simple function used to properize a string sent to it. When I do
a simple SELECT ProperCase('JESSE'); it returns JESSE on our server that
is running 5.0.17-nt-log. On another server that I've got, running
5.0.51a-community-nt, this function returns Jesse as it should.
The only difference that I can think of is the version. Is there a problem
with the older version that would cause this function not to work properly?
Thanks,
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Group by function and avg on char
Phil,
If in the 2nd query you want teams with the highest count per cpid found
in the first query, I think you can map the 'Avoiding repeat
aggregation' pattern
(http://www.artfulsoftware.com/infotree/queries.php) to your problem
PB
-
Phil wrote:
Hi all,
got a simple problem I'm trying to solve without success.
Given the following table
CREATE TABLE `scores` (
`proj` char(3) NOT NULL default '',
`id` int(11) NOT NULL default '0',
`score` double default NULL,
`cpid` char(32) default NULL,
`team` char(20) default NULL,
PRIMARY KEY (`proj`,`id`),
KEY `cpid` (`cpid`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
insert into scores values ('a',1,100,'aaa','X');
insert into scores values ('b',2,50,'aaa','X');
insert into scores values ('c',2,500,'aaa','Y');
I have the following sql to get the sum of scores for the cpid (cross
project id)
select cpid,sum(score) from scores group by cpid;
This is simple enough and works fine. However I also wish to select the team
given this case, I'd like to get 'X' as there are two instances of 'X' and
only one of 'Y'
Is this possible in the same sql statement, something like an AVG for a
string, or a median perhaps.
Regards
Phil
No virus found in this incoming message.
Checked by AVG.
Version: 7.5.519 / Virus Database: 269.22.1/1346 - Release Date: 3/27/2008 10:03 AM
Group by function and avg on char
Hi all,
got a simple problem I'm trying to solve without success.
Given the following table
CREATE TABLE `scores` (
`proj` char(3) NOT NULL default '',
`id` int(11) NOT NULL default '0',
`score` double default NULL,
`cpid` char(32) default NULL,
`team` char(20) default NULL,
PRIMARY KEY (`proj`,`id`),
KEY `cpid` (`cpid`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
insert into scores values ('a',1,100,'aaa','X');
insert into scores values ('b',2,50,'aaa','X');
insert into scores values ('c',2,500,'aaa','Y');
I have the following sql to get the sum of scores for the cpid (cross
project id)
select cpid,sum(score) from scores group by cpid;
This is simple enough and works fine. However I also wish to select the team
given this case, I'd like to get 'X' as there are two instances of 'X' and
only one of 'Y'
Is this possible in the same sql statement, something like an AVG for a
string, or a median perhaps.
Regards
Phil
--
Help build our city at http://free-dc.myminicity.com !
function/procedure error!
Hi everybody! I have a little problem with one function. What i want is to make a function which returns me numbers from 0 to the number that receives the function. Example: if the function receives the number 4, it will return 0,1,2,3 and 4. numbers 0 1 2 3 4 The problem is that it makes me a error. CREATE OR REPLACE FUNCTION `cantidad`(n integer) RETURNS INTEGER AS DECLARE i integer; BEGIN FOR i IN 0..n LOOP RETURN NEXT i; END LOOP; RETURN; END; Thank you very much -- View this message in context: http://www.nabble.com/function-procedure-error%21-tp15875760p15875760.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
