I am (a newbie) having trouble creating a stored function to return a
date from a table ('reporting_days') a given number of days after a
specified start date. This is the start of a more complex function
that will allow me to add various date values to a start date subject
to business rules held in the reporting_days table, which is why I'm
using a decimal as input then rounding to an int.

Here is the SQL I've tried:

delimiter //
CREATE FUNCTION Add_Working_Days(Units_to_Add Decimal(10, 2),
Start_Date Date)
RETURNS date

BEGIN
DECLARE whole_units int;

SET whole_units = ROUND(Units_to_Add, 0);

RETURN SELECT Max(T1.Reporting_Day) FROM (
SELECT * FROM reporting_days WHERE (Day_Type ='') AND
(reporting_day >= Start_Date) Limit whole_units) as T1;

END//

delimiter ;

I've also tried assigning the return value to a parameter and (using
'SET varReturn = SELECT . . .) and still get a syntax error with the
SELECT part of the function. When I run the following against the
database I get the expected result:

SELECT Max(T1.Reporting_Day)
FROM (SELECT * FROM reporting_days
WHERE (Day_Type ='') AND (reporting_day >= '2010-01-01') Limit 20) as
T1

So I suspect there is something wrong with the way I'm trying to
access the result of the query SQL rather than with the query SQL
itself.

Can anybody point out where I'm going wrong, and also, if there is a
better way to return a scalar value from a query?

Thanks in advance



Martin

On 2010-12-16 06:46, MartinH wrote:
[...]
I think you will get the wrong result here. You return the latest day
from n random days after start_date. You need to order the result set
for this to work.

/Lennart

On Dec 15, 8:13*pm, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
Agreed, I will need to order the results from the subquery, but would
this be causing the syntax error?

On 2010-12-16 07:21, MartinH wrote:
[...]
Probably not, I was just pointing out that you are likely to get wrong
results from your function. If you post a create table statement for
working days, and some insert statements with sample data, I may be able
to help out.


/Lennart

MartinH <mgheal (AT) gmail (DOT) com> wrote:

....

The first way does not work because a function cannot return a result
set. A result set (even with only one row) is not a scalar.

Your second thought is much better: you have to put the result into a
variable and then RETURN that. You just use the wrong syntax.

http://dev.mysql.com/doc/refman/5.1/...statement.html


XL

On Dec 15, 11:55*pm, Axel Schwenke <axel.schwe... (AT) gmx (DOT) de> wrote:
Thanks for the guidance - I've managed to narrow down the syntax error
to the use of the whole_units parameter as the value for the LIMIT on
the sub query results. When I hard code a numeric value it works, but
not with the parameter.

I have found in the mySQL documentation that:

The LIMIT clause can be used to constrain the number of rows returned
by the SELECT statement. LIMIT takes one or two numeric arguments,
which must both be nonnegative integer constants (except when using
prepared statements).

As I have already specified the whole_units parameter as an INT I'm
not sure what to try next?

Any suggestions would be appreciated.


Martin

El 16/12/2010 11:43, MartinH escribió/wrote:
A result set with one column and one row is still a result set.


Just follow Axel's suggestion. You need to declare a local variable:

DECLARE whatever DATE;

.... feed it:

SET whatever = (
SELECT Max(T1.Reporting_Day) FROM
(
SELECT *
FROM reporting_days
WHERE (Day_Type ='')
AND reporting_day>= Start_Date
Limit whole_units
) as T1
);

... or:

SELECT Max(T1.Reporting_Day) INTO whatever FROM
(
SELECT *
FROM reporting_days
WHERE (Day_Type ='')
AND reporting_day>= Start_Date
Limit whole_units
) as T1;

... and return the variable:

RETURN whatever;



--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

On 2010-12-16 11:43, MartinH wrote:
[...]
One way is to use a cursor inside the function. Example:

delimiter //
CREATE FUNCTION Add_Working_Days(Units_to_Add Decimal(10, 2),
Start_Date Date)
RETURNS date

BEGIN
DECLARE whole_units int;
DECLARE res DATE;
DECLARE cur1 CURSOR FOR SELECT * FROM reporting_days
WHERE reporting_day >= Start_Date
ORDER BY reporting_day;

SET whole_units = ROUND(Units_to_Add, 0);

OPEN cur1;
WHILE whole_units >= 0 DO
FETCH cur1 into res;
SET whole_units = whole_units - 1;
END WHILE;

RETURN res;

END//

delimiter ;


/Lennart

On Dec 16, 1:42*am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
Hi Lennart,

I have used your code which creates the function, but then when I try
and use it like this:

SELECT Add_Working_Days_Cursor(10.23, '2010-12-01')

I get an "#1328 - Incorrect number of FETCH variables" error. Any
suggestions?

Thanks



Martin

On 2010-12-16 21:50, MartinH wrote:
[...]
It works for me (but then again, I have neither your table definition
nor some sample data to try with)

mysql> SELECT Add_Working_Days(1.23, '2010-01-01');
+--------------------------------------+
1 row in set (0.01 sec)

My code was just an example so you will have to add some error handling,
for example a continue handler for not found condition. NULL will
probably be appropriate to return in that case.


/Lennart