Creating FUNCTIONS/PROCEEDURES

am 16.09.2006 23:00:58 von Cerebral Believer

Hi everybody,

I have the following statement:

SELECT SUM(`Quantity Ordered`) FROM `Sales Detail Information` WHERE
`Catalogue Number` = "FBDCD008";

I can run this as a query in the SQL window, and it works fine.

How could I turn this query into a FUNCTION or perhaps better still a
PROCEEDURE? I am not sure if FUNCTIONS can reference tables...

I am using phpadmin 2.7.0, and My SQL 5.0.15. I would like to get the
statement registered as a FUNCTION/PROCEEDURE so that I can so that I can
select it from the "Function" selector on the "Insert" sub-tab in
myphpadmin. I hope this will enable the column to always hold the current
value for Quantity Sold (the sum total of "Quantity Ordered"). This is the
only way I can think of achieving this goal, unless someone has another
idea?

Any thoughts on this?

Regards,
C.B.

Re: Creating FUNCTIONS/PROCEEDURES

am 16.09.2006 23:50:26 von nc

Cerebral Believer wrote:
>
> I have the following statement:
>
> SELECT SUM(`Quantity Ordered`)
> FROM `Sales Detail Information` WHERE
> `Catalogue Number` = "FBDCD008";
>
> I can run this as a query in the SQL window, and it works fine.
>
> How could I turn this query into a FUNCTION or perhaps
> better still a PROCEEDURE? I am not sure if FUNCTIONS
> can reference tables...

Read MySQL documentation:

http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

Cheers,
NC

Re: Creating FUNCTIONS/PROCEEDURES

am 16.09.2006 23:50:26 von nc

Re: Creating FUNCTIONS/PROCEEDURES

am 17.09.2006 15:37:00 von Cerebral Believer

"NC" wrote in message
news:1158443426.110452.211810@e3g2000cwe.googlegroups.com...
> Cerebral Believer wrote:
>>
>> I have the following statement:
>>
>> SELECT SUM(`Quantity Ordered`)
>> FROM `Sales Detail Information` WHERE
>> `Catalogue Number` = "FBDCD008";
>>
>> I can run this as a query in the SQL window, and it works fine.
>>
>> How could I turn this query into a FUNCTION or perhaps
>> better still a PROCEEDURE? I am not sure if FUNCTIONS
>> can reference tables...
>
> Read MySQL documentation:
>
> http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

Thanks, I keep getting Error #1064, but the same syntax works fine for a
simple query.

CREATE FUNCTION Sum_001R RETURNS INT
SELECT SUM(`Quantity Ordered`) FROM `Sales Detail Information`
WHERE`Catalogue Number` = "FBDCD008";

I am perplexed.

Regards,
C.B.

Re: Creating FUNCTIONS/PROCEEDURES

am 17.09.2006 15:37:00 von Cerebral Believer

Re: Creating FUNCTIONS/PROCEEDURES

am 17.09.2006 16:59:06 von Cerebral Believer

"Cerebral Believer" wrote in message
news:0ccPg.28491$SH2.23209@newsfe4-gui.ntli.net...
>
> "NC" wrote in message
> news:1158443426.110452.211810@e3g2000cwe.googlegroups.com...
>> Cerebral Believer wrote:
>>>
>>> I have the following statement:
>>>
>>> SELECT SUM(`Quantity Ordered`)
>>> FROM `Sales Detail Information` WHERE
>>> `Catalogue Number` = "FBDCD008";
>>>
>>> I can run this as a query in the SQL window, and it works fine.
>>>
>>> How could I turn this query into a FUNCTION or perhaps
>>> better still a PROCEEDURE? I am not sure if FUNCTIONS
>>> can reference tables...
>>
>> Read MySQL documentation:
>>
>> http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html
>
> Thanks, I keep getting Error #1064, but the same syntax works fine for a
> simple query.
>
> CREATE FUNCTION Sum_001R RETURNS INT
> SELECT SUM(`Quantity Ordered`) FROM `Sales Detail Information`
> WHERE`Catalogue Number` = "FBDCD008";
>
> I am perplexed.

OK,

I found the answer, again quite simple, but requiring back-tick quotes
(which I had not put round the proceedure name), which is not specified on
the MySQL website, neither in the reference material or the examples. So
this works:

CREATE PROCEDURE `008_sales`()
SELECT SUM(`Quantity Ordered`) FROM `Sales Detail Information` WHERE
`Catalogue Number` = "FBDCD008";

Anyone know how I can get a column to automatically run that procedure? Is
that even possible?

Regards,
C.B.

Re: Creating FUNCTIONS/PROCEEDURES

am 17.09.2006 16:59:06 von Cerebral Believer