Hi,
>From my experience with MySQL, i think you can create a function(UDF - you
can look in documentation at "9.2.2 Adding a New User-definable
Function".Also you can find samples in MySQL source distribution) to avoid
this conditional filtering and not to create/generate this SQL query.
In this moment(ver 4.x) if you wish to create Store Procedures and make it
to run with several tables , it's not so easy.
Anyway,you can use this method of filtering and with several tables and the
performance are very good.
Regards,
Gelu
_____________________________________________________
G.NET SOFTWARE COMPANY
Permanent e-mail address : [EMAIL PROTECTED]
[EMAIL PROTECTED]
----- Original Message -----
From: "Mehdi Roomi" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Saturday, September 07, 2002 7:15 PM
Subject: adding function, module to database
> Hi,
> suppose there is a database with lots of tables.
> I want to add a function to this database to return the following SQL
query:
>
> SELECT SUM(IF(HOUR(start)>'02:00:00' AND
> HOUR(stop)<='08:00:00',50*sessiontime,0)+IF(HOUR(start)>'08:00:00' AND
> HOUR(stop)<='14:00:00',100*sessiontime,0)+IF(HOUR(start)<'14:00:00' AND
> HOUR(stop)<='02:00:00',150*sessiontime,0)) from YOUR_TABLE WHERE
> username='mehdi' AND start BETWEEN '2002-08-12' AND '2002-08-16';
>
> is it possible to define some thing like functions (or something like
MS-SQL
> Server's Store Procedure) in mysql to do such a work ?
> if yes, please let me have a sample and let me know how to call such a
> fuction after creating it.
> p.s. a sample table that the above query defined for it is like:
> +--------+---------------------+----------------------+-----------+
> |username| start | stop |sessiontime|
> +--------+---------------------+----------------------+-----------+
> |mehdi | 2002-08-12 00:22:20 | 2002-08-12 00:24:20 | 120 |
> +--------+---------------------+----------------------+-----------+
> |mehdi | 2002-08-13 01:10:20 | 2002-08-13 01:40:20 | 1800 |
> +--------+---------------------+----------------------+-----------+
> |mehdi | 2002-08-14 08:30:20 | 2002-08-14 09:30:20 | 3600 |
> +--------+---------------------+----------------------+-----------+
> |mehdi | 2002-08-15 15:22:20 | 2002-08-15 17:22:20 | 7200 |
> +--------+---------------------+----------------------+-----------+
>
>
> Best Regards,
> M. Roomi
>
>
>
> _________________________________________________________________
> Send and receive Hotmail on your mobile device: http://mobile.msn.com
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
