• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

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
0
gdrnec
Asked:
gdrnec
  • 2
1 Solution
 
ralmadaCommented:
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
0
 
gdrnecAuthor Commented:
I have just solved it.

Turns out that a simple set of parenthesis fixes the issue:

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)
0
 
gdrnecAuthor Commented:
I solved it myself after having tried everything else.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now