LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Programming (http://www.linuxquestions.org/questions/programming-9/)
-   -   mySQL Function (http://www.linuxquestions.org/questions/programming-9/mysql-function-495112/)

j1lted 10-24-2006 09:07 AM

mySQL Function
 
Hi,

I'm trying to migrate the following MS SQL function to mySQL.

CREATE FUNCTION getRoadFundLicense
(
@fueltype char,
@co2 int,
@months int
)
RETURNS int
BEGIN
declare @result int
set @result = 0

if @co2 <> null
begin
if (@months = 12)
begin
if @co2 >= 0 and @co2 <=100
begin
-- band A
set @result = 55
end
else if @co2 >= 101 and @co2 <=120
begin
-- band B
if @fueltype = 'D' set @result = 50
else if @fueltype = 'P' set @result =40
else set @result = 30
end
else if @co2 >= 121 and @co2 <=150
begin
-- band C
if @fueltype = 'D' set @result = 110
else if @fueltype = 'P' set @result =100
else set @result = 90
end
else if @co2 >= 151 and @co2 <=165
begin
-- band D
if @fueltype = 'D' set @result = 135
else if @fueltype = 'P' set @result =125
else set @result = 115
end
else if @co2 >= 166 and @co2 <=185
begin
-- band E
if @fueltype = 'D' set @result = 160
else if @fueltype = 'P' set @result =150
else set @result = 140
end
else if @co2 >= 186 and @co2 <= 225
begin
-- band F
if @fueltype = 'D' set @result = 195
else if @fueltype = 'P' set @result = 190
else set @result = 180
end
else if @co2 >= 226
begin
-- band G
if @fueltype = 'D' set @result = 215
else if @fueltype = 'P' set @result = 210
else set @result = 200
end
end
if (@months = 6) begin
if @co2 >= 0 and @co2 <=100
begin
if @fueltype = 'D' set @result = 0
else if @fueltype = 'P' set @result = 0
else set @result = 0
end
else if @co2 >= 101 and @co2 <=120
begin
-- band B
if @fueltype = 'D' set @result = 25
else if @fueltype = 'P' set @result = 20
else set @result = 15
end
else if @co2 >= 121 and @co2 <=150
begin
-- band C
if @fueltype = 'D' set @result = 60.50
else if @fueltype = 'P' set @result = 55
else set @result = 49.50
end
else if @co2 >= 151 and @co2 <=165
begin
-- band D
if @fueltype = 'D' set @result = 74.25
else if @fueltype = 'P' set @result = 68.75
else set @result = 63.25
end
else if @co2 >= 166 and @co2 <=185
begin
-- band E
if @fueltype = 'D' set @result = 88.00
else if @fueltype = 'P' set @result = 82.50
else set @result = 77.00
end
else if @co2 >= 186 and @co2 <= 225
begin
-- band F
if @fueltype = 'D' set @result = 107.25
else if @fueltype = 'P' set @result = 104.50
else set @result = 99.00
end
else if @co2 >= 226
begin
-- band G
if @fueltype = 'D' set @result = 118.25
else if @fueltype = 'P' set @result = 115.50
else set @result = 110.00
end

end
end

return @result
END

However, upon trying to create the function I recieve the following error.

ERROR 1064 (42000): 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 '@fueltype char,
@co2 int,
@months int
)
RETURNS int
BEGIN
declare @result ' at line 3
mysql>

Any help would be much appreciated.

senyahnoj 10-24-2006 11:02 AM

Don't think that's how MySQL user defined functions work. You need to create a separate object file in something like C, compile it and then use the MySQL CREATE FUNCTION syntax to link to it:

http://dev.mysql.com/doc/refman/5.0/...functions.html

j1lted 10-24-2006 11:16 AM

Should be able to create it as a stored procedure / function.

dev.mysql.com/doc/refman/5.0/en/stored-procedures.html

Tried swapping

CREATE FUNCTION getRoadFundLicense

for

CREATE PROCEDURE getRoadFundLicense

but to no avail.

paulsm4 10-24-2006 11:32 AM

Check you version of mySQL: stored procedures are one of many features that are new to MySQL 5.0, and were *not* available in earlier versions.

j1lted 10-25-2006 07:50 AM

Answer
 
Using the following very useful document http://dev.mysql.com/tech-resources/...procedures.pdf I managed to rewrite MS SQL function.

Working copy:

DELIMiTER //

drop PROCEDURE IF EXISTS getRoadFundLicense;//

CREATE PROCEDURE getRoadFundLicense
(
IN fueltype char(1),
IN co2 int,
IN months int,
OUT result double
)
BEGIN
set result = 0;
if months=12 then
-- band A
if co2 >= 0 and co2 <=100 then
set result = 0;
elseif co2 >= 101 and co2 <=120 then
-- band B
if fueltype = 'D' then set result = 50;
elseif fueltype = 'P' then set result = 40;
else set result = 30;
end if;
elseif co2 >= 121 and co2 <=150 then
-- band C
if fueltype = 'D' then set result = 110;
elseif fueltype = 'P' then set result = 100;
else set result = 90;
end if;
elseif co2 >= 151 and co2 <=165 then
-- band D
if fueltype = 'D' then set result = 135;
elseif fueltype = 'P' then set result = 125;
else set result = 115;
end if;
elseif co2 >= 166 and co2 <=185 then
-- band E
if fueltype = 'D' then set result = 160;
elseif fueltype = 'P' then set result = 150;
else set result = 140;
end if;
elseif co2 >= 186 and co2 <= 225 then
-- band F
if fueltype = 'D' then set result = 195;
elseif fueltype = 'P' then set result = 190;
else set result = 180;
end if;
elseif co2 >= 226 then
-- band G
if fueltype = 'D' then set result = 215;
elseif fueltype = 'P' then set result = 210;
else set result = 200;
end if;
end if;
elseif months=6 then
-- band A
if co2 >= 0 and co2 <=100 then
set result = 0;
elseif co2 >= 101 and co2 <=120 then
-- band B
if fueltype = 'D' then set result = 0;
elseif fueltype = 'P' then set result = 0;
else set result = 30;
end if;
elseif co2 >= 121 and co2 <=150 then
-- band C
if fueltype = 'D' then set result = 60.50;
elseif fueltype = 'P' then set result = 55;
else set result = 49.50;
end if;
elseif co2 >= 151 and co2 <=165 then
-- band D
if fueltype = 'D' then set result = 74.25;
elseif fueltype = 'P' then set result = 68.75;
else set result = 63.25;
end if;
elseif co2 >= 166 and co2 <=185 then
-- band E
if fueltype = 'D' then set result = 88;
elseif fueltype = 'P' then set result = 82.50;
else set result = 77.00;
end if;
elseif co2 >= 186 and co2 <= 225 then
-- band F
if fueltype = 'D' then set result = 107.25;
elseif fueltype = 'P' then set result = 104.50;
else set result = 99;
end if;
elseif co2 >= 226 then
-- band G
if fueltype = 'D' then set result = 99;
elseif fueltype = 'P' then set result = 115.50;
else set result = 110;
end if;
end if;
end if;
END;//
DELIMITER ;//



Use the following to return result:


call getRoadFundLicense('P',228,6,@result);
select @result;


All times are GMT -5. The time now is 03:01 AM.