[<<][sql][>>][..]
Sat Mar 30 11:29:32 EDT 2013

Function based on table

Given a currency conversion table, I want to make a function:

  (date, currencty) -> rate 
or

  (date, currency, amount) -> amount

where e.g. the conversion table contains monthly data, so the input
date can be rounded from that.

After some struggle with SQL syntax, this is where I got at.  The main
remark is that to convert a set to a value, use "SELECT INTO".  It
will generate a warning when the set is not a singleton.

DROP FUNCTION IF EXISTS cconv;
DELIMITER |
CREATE FUNCTION cconv(date_in DATE)
RETURNS DECIMAL(10,6)
BEGIN
        DECLARE rv DECIMAL(10,6) default 1;
        SELECT rate INTO rv FROM exchange
        WHERE   year(date)=year(date_in) and
                month(date)=month(date_in) and
                den="EUR";
        RETURN rv;
END
|
DELIMITER ;


[1] http://stackoverflow.com/questions/8073404/function-complain-about-not-allowed-to-return-a-result-set-from-a-function-whe



[Reply][About]
[<<][sql][>>][..]