Re-use a result field into a query
Hi guys. I´ve got a low-priority, non-important newbie question: Is it possible to refer into a query to a result field from the same query?. For example: a query which uses the field name of a result (alias) to create a new field: Select a.field1, (a.field2+a.field3) AS result1, (result1*100) AS result2 #Here is the question. FROM table1 AS a GROUP BY a.field1 I know I can get the desired result with the following query Select a.field1, (a.field2+a.field3) AS result1, ((a.field2+a.field3)*100) AS result2 FROM table1 AS a GROUP BY a.field1 but, sometimes I have quite big calculations into a query a I need to use them quite often. I used to do that in MSAccess (because it is possible ot save queries). Is it possible or there is a way to re-use results from a query in MySql?. Thanks guys and very best regards. PD: Anyway, I do love my transition from MSAccess to MySql. My system is: MySql 4.1.10 into a Debian Sarge Box.
Re: Re-use a result field into a query
From: Alvaro Cobo [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, June 12, 2005 8:17 AM Subject: Re-use a result field into a query Is it possible to refer into a query to a result field from the same query?. For example: a query which uses the field name of a result (alias) to create a new field: A quick look in the online manual at http://dev.mysql.com/doc/mysql/en/select.html tells us: A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses. (...)It is not allowable to use a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed. See Section A.5.4, Problems with Column Aliases. -- So, you can only refer to an alias in the GROUP BY, ORDER BY or HAVING section of a query. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to update with select a spatial value ?
From: Pete Lancashire
update fooloc set location = geomfromtext('point(lat lon)');
I have no experience with spatial tables, but from the manual I conclude
that either of these should do the job:
UPDATE `fooloc` SET `location` = GeoFromWKB(point(`lat`, `lon`));
or
UPDATE `fooloc` SET `location` = GeoFromText(CONCAT( 'POINT(', `lat`, '
', `lon`, ')'));
Since there is no WHERE clause, all records in the table will be updated.
Further reading:
http://dev.mysql.com/doc/mysql/en/gis-wkt-functions.html
http://dev.mysql.com/doc/mysql/en/gis-wkb-functions.html
http://dev.mysql.com/doc/mysql/en/gis-mysql-specific-functions.html
http://dev.mysql.com/doc/mysql/en/populating-spatial-columns.html
Regards, Jigal.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Openssl 0.9.8 and Mysql
Will MySQL 4.0 + bt OpenSSL 0.9.8 compliant? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
storing recurring dates
Hi, I'm doing an event project and some of the events will be reccuring. For example: Monday, Wednesday, Friday from 10-11:30 am starting June 1 with no end date Every third Monday at 3-4 pm starting July 1 and ending January 1 (last event is third Monday in December) Every other Friday starting at 1pm with no set end time, starting June 3 Is there a good way to store those in a mysql database? So far the only thing I can think of is that on entry, have a script figure out all of the dates, which is pretty easy in php. Then for the events with no end date set an arbitrary end date of 5 years in the future knowing that the technology will probably change by then and the app will need to be re-written. Is there maybe some way or combination with the php strtotime function? I know it can take something like Third Thursday of October and turn it into a unix time stamp. But my brain is just not working today. ;) Thanks, Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re-use a result field into a query
Well Alvaro, you can use variables to re-use resultas like that : mysql set @rs=0; Query OK, 0 rows affected (0.02 sec) mysql select 1, (@rs:=2+3) as result1, - (@rs*100) as result2 - from dual; +---+-+-+ | 1 | result1 | result2 | +---+-+-+ | 1 | 5 | 500 | +---+-+-+ 1 row in set (0.00 sec) Mathias Selon Jigal van Hemert [EMAIL PROTECTED]: From: Alvaro Cobo [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, June 12, 2005 8:17 AM Subject: Re-use a result field into a query Is it possible to refer into a query to a result field from the same query?. For example: a query which uses the field name of a result (alias) to create a new field: A quick look in the online manual at http://dev.mysql.com/doc/mysql/en/select.html tells us: A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses. (...)It is not allowable to use a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed. See Section A.5.4, Problems with Column Aliases. -- So, you can only refer to an alias in the GROUP BY, ORDER BY or HAVING section of a query. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: storing recurring dates
Bob, You should check out the Date::Manip module from CPAN, it will do what you need to do. Here is a snippet from the docs about recurrence: RECURRENCE A recurrence is simply a notation for defining when a recurring event occurs. For example, if an event occurs every other Friday or every 4 hours, this can be defined as a recurrence. With a recurrence and a starting and ending date, you can get a list of dates in that period when a recurring event occurs. This should get you going with all the options you need. Chris Hood -Original Message- From: Ramsey, Robert L [mailto:[EMAIL PROTECTED] Sent: Sunday, June 12, 2005 8:51 AM To: mysql@lists.mysql.com Subject: storing recurring dates Hi, I'm doing an event project and some of the events will be reccuring. For example: Monday, Wednesday, Friday from 10-11:30 am starting June 1 with no end date Every third Monday at 3-4 pm starting July 1 and ending January 1 (last event is third Monday in December) Every other Friday starting at 1pm with no set end time, starting June 3 Is there a good way to store those in a mysql database? So far the only thing I can think of is that on entry, have a script figure out all of the dates, which is pretty easy in php. Then for the events with no end date set an arbitrary end date of 5 years in the future knowing that the technology will probably change by then and the app will need to be re-written. Is there maybe some way or combination with the php strtotime function? I know it can take something like Third Thursday of October and turn it into a unix time stamp. But my brain is just not working today. ;) Thanks, Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: storing recurring dates
hi,
have you tried to hack something with week, month and weekofyear ?
there is an interesting url at
http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
be aware that the week begins on sunday.
mysql select weekofyear('2005-01-02');
+--+
| weekofyear('2005-01-02') |
+--+
| 53 |
+--+
1 row in set (0.00 sec)
mysql select weekofyear('2005-01-03');
+--+
| weekofyear('2005-01-03') |
+--+
|1 |
+--+
you can then construct a week-based calendar for the events.
hope that helps.
Mathias
Selon [EMAIL PROTECTED]:
Bob,
You should check out the Date::Manip module from CPAN, it will do what you
need to do. Here is a snippet from the docs about recurrence:
RECURRENCE
A recurrence is simply a notation for defining when a recurring event
occurs. For example, if an event occurs every other Friday or every 4
hours, this can be defined as a recurrence. With a recurrence and a
starting and ending date, you can get a list of dates in that period when
a recurring event occurs.
This should get you going with all the options you need.
Chris Hood
-Original Message-
From: Ramsey, Robert L [mailto:[EMAIL PROTECTED]
Sent: Sunday, June 12, 2005 8:51 AM
To: mysql@lists.mysql.com
Subject: storing recurring dates
Hi,
I'm doing an event project and some of the events will be reccuring. For
example:
Monday, Wednesday, Friday from 10-11:30 am starting June 1 with no end
date
Every third Monday at 3-4 pm starting July 1 and ending January 1 (last
event is third Monday in December)
Every other Friday starting at 1pm with no set end time, starting June 3
Is there a good way to store those in a mysql database? So far the only
thing I can think of is that on entry, have a script figure out all of the
dates, which is pretty easy in php. Then for the events with no end date
set an arbitrary end date of 5 years in the future knowing that the
technology will probably change by then and the app will need to be
re-written.
Is there maybe some way or combination with the php strtotime function? I
know it can take something like Third Thursday of October and turn it
into a unix time stamp. But my brain is just not working today. ;)
Thanks,
Bob
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
(Fine) Tuning Server Parameters
Dear All, I am trying to tweak some server parameters to fine tune my MySQL (4.0.15) server on a linux box (with 6GB of ram) All my tables/databases use InnoDB. My question is : What is the InnoDB equivalent of the server parameters Key_read_request key_reads. I had a look at show innodb status to get an idea of these parameters but got lost in all the information provided by show innodb status, hence would appreciate any pointers! My entire my.cnf is as below for reference/additional comments that would be useful in fine-tuning server parameters. Thanks for your help in advance. Cheers Manoj -- [client] port=3306 socket=/tmp/mysql.sock [mysqld] user=mysql port=3306 key_buffer=256M table_cache=512 sort_buffer=8M join_buffer_size=8M read_buffer_size=8M read_rnd_buffer_size=6M max_connection=30 max_allowed_packet= 16M binlog_cache_size = 4M default-table-type=innodb log_slow_queries=/home/mysql/log/slow.query.log log_error=/home/mysql/log/mysqld.err.log log_long_format long_query_time = 10 query_cache_size = 256M query_cache_limit = 16M tmp_table_size = 400M thread_cache = 8 thread_concurrency = 8 # innodb_options innodb_data_home_dir=/usr/local/mysql/ibdata/ innodb_data_file_path=ibdata1:5G;ibdata2:1G:autoextend innodb_mirrored_log_groups=1 innodb_log_group_home_dir=/usr/local/mysql/ibdata/log innodb_log_arch_dir=ibdata/log innodb_log_files_in_group=2 innodb_log_file_size=1500M innodb_log_buffer_size=16M innodb_buffer_pool_size=1500M innodb_additional_mem_pool_size=4M innodb_flush_log_at_trx_commit=0 innodb_flush_method=O_DIRECT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (Fine) Tuning Server Parameters
hi, look at insert buffer and buffer pool. This is quite interesting : http://sunsite.mff.cuni.cz/MIRRORS/ftp.mysql.com/doc/en/InnoDB_Insert_buffering.html mathias Selon Manoj [EMAIL PROTECTED]: Dear All, I am trying to tweak some server parameters to fine tune my MySQL (4.0.15) server on a linux box (with 6GB of ram) All my tables/databases use InnoDB. My question is : What is the InnoDB equivalent of the server parameters Key_read_request key_reads. I had a look at show innodb status to get an idea of these parameters but got lost in all the information provided by show innodb status, hence would appreciate any pointers! My entire my.cnf is as below for reference/additional comments that would be useful in fine-tuning server parameters. Thanks for your help in advance. Cheers Manoj -- [client] port=3306 socket=/tmp/mysql.sock [mysqld] user=mysql port=3306 key_buffer=256M table_cache=512 sort_buffer=8M join_buffer_size=8M read_buffer_size=8M read_rnd_buffer_size=6M max_connection=30 max_allowed_packet= 16M binlog_cache_size = 4M default-table-type=innodb log_slow_queries=/home/mysql/log/slow.query.log log_error=/home/mysql/log/mysqld.err.log log_long_format long_query_time = 10 query_cache_size = 256M query_cache_limit = 16M tmp_table_size = 400M thread_cache = 8 thread_concurrency = 8 # innodb_options innodb_data_home_dir=/usr/local/mysql/ibdata/ innodb_data_file_path=ibdata1:5G;ibdata2:1G:autoextend innodb_mirrored_log_groups=1 innodb_log_group_home_dir=/usr/local/mysql/ibdata/log innodb_log_arch_dir=ibdata/log innodb_log_files_in_group=2 innodb_log_file_size=1500M innodb_log_buffer_size=16M innodb_buffer_pool_size=1500M innodb_additional_mem_pool_size=4M innodb_flush_log_at_trx_commit=0 innodb_flush_method=O_DIRECT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re-use a result field into a query
Thanks guys for your quick and clear help: It gives me elements to research. I think I'll try the variable approach, and the way in how to use it with PHP. Thanks again and best regards, Alvaro. - Original Message - From: [EMAIL PROTECTED] To: Jigal van Hemert [EMAIL PROTECTED] Cc: Alvaro Cobo [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Sunday, June 12, 2005 1:11 PM Subject: Re: Re-use a result field into a query Well Alvaro, you can use variables to re-use resultas like that : mysql set @rs=0; Query OK, 0 rows affected (0.02 sec) mysql select 1, (@rs:=2+3) as result1, - (@rs*100) as result2 - from dual; +---+-+-+ | 1 | result1 | result2 | +---+-+-+ | 1 | 5 | 500 | +---+-+-+ 1 row in set (0.00 sec) Mathias Selon Jigal van Hemert [EMAIL PROTECTED]: From: Alvaro Cobo [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, June 12, 2005 8:17 AM Subject: Re-use a result field into a query Is it possible to refer into a query to a result field from the same query?. For example: a query which uses the field name of a result (alias) to create a new field: A quick look in the online manual at http://dev.mysql.com/doc/mysql/en/select.html tells us: A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses. (...)It is not allowable to use a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed. See Section A.5.4, Problems with Column Aliases. -- So, you can only refer to an alias in the GROUP BY, ORDER BY or HAVING section of a query. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Regarding NOT NULL Option for Table Fields....
Hi friends,
I'm having one doubt on NOT NULL specification for
the table field properties. That is i have created on
table-mine which contains name(Not Null) and pwd(Not
Null). Now I intended to execute the following query.
insert into mine values('','')
This means that i'm trying to insert the null fields
to the table. but this query is successfully executed
and 1 row is inserted into table with empty values.
why it's happening and how can i resolve this problem.
Pls give me suggestions regarding this.
Thanks and Regards,
Ashok Kumar.P.S
__
Discover Yahoo!
Stay in touch with email, IM, photo sharing and more. Check it out!
http://discover.yahoo.com/stayintouch.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Regarding NOT NULL Option for Table Fields....
Ashok Kumar wrote:
Hi friends,
I'm having one doubt on NOT NULL specification for
the table field properties. That is i have created on
table-mine which contains name(Not Null) and pwd(Not
Null). Now I intended to execute the following query.
insert into mine values('','')
This means that i'm trying to insert the null fields
to the table. but this query is successfully executed
and 1 row is inserted into table with empty values.
why it's happening and how can i resolve this problem.
Pls give me suggestions regarding this.
Thanks and Regards,
Ashok Kumar.P.S
Because '' is an empty string, not NULL.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Regarding NOT NULL Option for Table Fields....
Hi,
'' or empty string is not a null in mysql. This is true for Oracle !
this simple test lets you understand :
* Without NULLs
mysql create table notnull (t varchar(10) NOT NULL);
Query OK, 0 rows affected (0.14 sec)
mysql insert into notnull values('test1');
Query OK, 1 row affected (0.01 sec)
mysql insert into notnull values('');
Query OK, 1 row affected (0.02 sec)
mysql insert into notnull values(NULL);
ERROR 1048 (23000): Column 't' cannot be null
mysql select * from notnull;
+---+
| t |
+---+
| test1 |
| |
+---+
2 rows in set (0.02 sec)
mysql select * from notnull where isnull(t);
Empty set (0.02 sec)
* With NULLs
mysql create table isnulle(a varchar(10));
Query OK, 0 rows affected (0.08 sec)
mysql insert into isnulle values(NULL);
Query OK, 1 row affected (0.03 sec)
mysql select * from isnulle where isnull(a);
+--+
| a|
+--+
| NULL |
+--+
1 row in set (0.00 sec)
Hope that helps.
Mathias
Selon Ashok Kumar [EMAIL PROTECTED]:
Hi friends,
I'm having one doubt on NOT NULL specification for
the table field properties. That is i have created on
table-mine which contains name(Not Null) and pwd(Not
Null). Now I intended to execute the following query.
insert into mine values('','')
This means that i'm trying to insert the null fields
to the table. but this query is successfully executed
and 1 row is inserted into table with empty values.
why it's happening and how can i resolve this problem.
Pls give me suggestions regarding this.
Thanks and Regards,
Ashok Kumar.P.S
__
Discover Yahoo!
Stay in touch with email, IM, photo sharing and more. Check it out!
http://discover.yahoo.com/stayintouch.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
