I'm using MySQL 5.0.45, and the section from the manual PDF called
cursors for examples of how to work with cursors (section 18.2.9.
Cursors in the reference manual PDF for MySQL 5).

I need to obtain the most recent date of the five dates returned by
the following SQL:

(SELECT price_date,etf_id FROM etfprices WHERE etf_id = 1 ORDER BY
price_date ASC LIMIT 1)
UNION
(SELECT price_date,etf_id FROM etfprices WHERE etf_id = 400 ORDER BY
price_date ASC LIMIT 1)
UNION
(SELECT price_date,etf_id FROM etfprices WHERE etf_id = 402 ORDER BY
price_date ASC LIMIT 1)
UNION
(SELECT price_date,etf_id FROM etfprices WHERE etf_id = 403 ORDER BY
price_date ASC LIMIT 1)
UNION
(SELECT price_date,etf_id FROM etfprices WHERE etf_id = 397 ORDER BY
price_date ASC LIMIT 1);

Of course, in my function the five integer literals are replaced by
function arguments. This select statement returns the dates I require
in a matter of milliseconds. Precisely five records. So far so
good. Now I nned the most recent of them and return that value from a
function.

I tried the appended function, which mysql parses down to the end of
the loop, and then dies with an obscure error. The error message I
get is:
ERROR 1064 (42000) at line 5: 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 'done END REPEAT;
RETURN rv;
END' at line 29

I don't see an obvious difference between how I ended by loop and how
the example did so. I know I didn't close the cursor, but the manual
says "If not closed explicitly, a cursor is closed at the end of the
compound statement in which it was declared." I assume this means
that in my case, it will be closed automagically once the function
returns.

What is the error I missed? Is there an easier or better way to get
the result I need?

Thanks

Ted

=========== first attempt at a function to get the most recent
date================================
delimiter //

DROP FUNCTION IF EXISTS get_portfolio_start_date5;
CREATE FUNCTION get_portfolio_start_date5(e_id1 INTEGER, e_id2
INTEGER, e_id3 INTEGER, e_id4 INTEGER, e_id5 INTEGER) RETURNS DATE
BEGIN
DECLARE rv DATE DEFAULT null;
DECLARE done INT DEFAULT 0;
DECLARE d DATE;
DECLARE eid INTEGER;

DECLARE cur CURSOR FOR
(SELECT price_date,etf_id FROM etfprices WHERE etf_id = e_id1
ORDER BY price_date ASC LIMIT 1)
UNION
(SELECT price_date,etf_id FROM etfprices WHERE etf_id = e_id2
ORDER BY price_date ASC LIMIT 1)
UNION
(SELECT price_date,etf_id FROM etfprices WHERE etf_id = e_id3
ORDER BY price_date ASC LIMIT 1)
UNION
(SELECT price_date,etf_id FROM etfprices WHERE etf_id = e_id4
ORDER BY price_date ASC LIMIT 1)
UNION
(SELECT price_date,etf_id FROM etfprices WHERE etf_id = e_id5
ORDER BY price_date ASC LIMIT 1);

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

OPEN cur;
REPEAT
FETCH cur INTO d,eid;
IF (rv IS NULL) THEN
SET rv = d;
ELSE IF NOT done THEN
IF (rv < d) THEN
SET rv = d;
END IF;
END IF;
UNTIL done END REPEAT;

RETURN rv;
END
//

delimiter ;

On Mon, 31 Dec 2007 18:56:07 +0100, Ted <r.ted.byers@rogers.com> wrote:

> I'm using MySQL 5.0.45, and the section from the manual PDF called
> cursors for examples of how to work with cursors (section 18.2.9.
> Cursors in the reference manual PDF for MySQL 5).
>
> I need to obtain the most recent date of the five dates returned by
> the following SQL:
>
> (SELECT price_date,etf_id FROM etfprices WHERE etf_id = 1 ORDER BY
> price_date ASC LIMIT 1)
> UNION
> (SELECT price_date,etf_id FROM etfprices WHERE etf_id = 400 ORDER BY
> price_date ASC LIMIT 1)
> UNION
> (SELECT price_date,etf_id FROM etfprices WHERE etf_id = 402 ORDER BY
> price_date ASC LIMIT 1)
> UNION
> (SELECT price_date,etf_id FROM etfprices WHERE etf_id = 403 ORDER BY
> price_date ASC LIMIT 1)
> UNION
> (SELECT price_date,etf_id FROM etfprices WHERE etf_id = 397 ORDER BY
> price_date ASC LIMIT 1);

So, you want the _first_ price of every record, where the id is one of
several literals:

SELECT o.price_date, o.etf_id
FROM etfprices o
LEFT JOIN etfprices j
ON j.price_date < o.price_date
AND j.etf_id = o.etf_id
WHERE ertf_id IN(1,400,402,403,397)
AND j.price_date IS NULL

> Of course, in my function the five integer literals are replaced by
> function arguments. This select statement returns the dates I require
> in a matter of milliseconds. Precisely five records. So far so
> good. Now I nned the most recent of them and return that value from a
> function.

Never had a need for a function or a cursor, but would this do:

SELECT o.price_date, o.etf_id
FROM etfprices o
LEFT JOIN etfprices j
ON j.price_date < o.price_date
AND j.etf_id = o.etf_id
WHERE o.ertf_id IN(1,400,402,403,397)
AND j.price_date IS NULL
ORDER BY o.price_date DESC
LIMIT 1
--
Rik Wasmus