asked on

Mysql Qury throws error in function

Hi experts,

I have written a query which works fine (below)

            select GROUP_CONCAT(concat('<option value="',OPTION_VALUE,'" ', IF(OPTION_VALUE = '13', 'selected="selected">',''),OPTION_NAME,'</option>') separator ' ')
            from assetoptions
       where SELECT_NAME = 'TransactionType'
            group by SELECT_NAME

However, what I need to make is a function from this query. Which should be very simple by replacing the character literals in the above sql with the parameters incoming to the function and return that value. My function is:

RETURN             select GROUP_CONCAT(concat('<option value="',OPTION_VALUE,'" ', IF(OPTION_VALUE = P_VALUE, 'selected="selected">',''),OPTION_NAME,'</option>') separator ' ')
            from assetoptions
       where SELECT_NAME = P_NAME
            group by SELECT_NAME;

I get an error when trying to create the function. Any ideas? are there functions that I am using that are not allowed in a mysql function?

Thanks in advance,

Geoff

ralmada

try

delimiter //

create function yourfunction(yourparam char(3))  --adjust name and datatype accordingly
returns char(30) --adjust accordingly
begin
RETURN             select GROUP_CONCAT(concat('<option value="',OPTION_VALUE,'" ', IF(OPTION_VALUE = P_VALUE, 'selected="selected">',''),OPTION_NAME,'</option>') separator ' ')
            from assetoptions
        where SELECT_NAME = P_NAME
            group by SELECT_NAME;
end//

delimiter ;

Open in new window

or assigning the result to a variable first

delimiter //

create function yourfunction(yourparam char(3))  --adjust name and datatype accordingly
returns char(30) --adjust accordingly
begin
declare retval char(30);
select GROUP_CONCAT(concat('<option value="',OPTION_VALUE,'" ', IF(OPTION_VALUE = P_VALUE, 'selected="selected">',''),OPTION_NAME,'</option>') separator ' ')
            from assetoptions into retval
        where SELECT_NAME = P_NAME
            group by SELECT_NAME;
return retval;
end//

Open in new window

http://forums.mysql.com/read.php?98,157522,157556#msg-157556

ASKER CERTIFIED SOLUTION

gdrnec

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gdrnec

ASKER

I solved it myself after having tried everything else.