LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 10-24-2006, 09:07 AM   #1
j1lted
LQ Newbie
 
Registered: Apr 2006
Location: Manchester
Distribution: Fedora
Posts: 4

Rep: Reputation: 0
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.
 
Old 10-24-2006, 11:02 AM   #2
senyahnoj
Member
 
Registered: Jul 2004
Location: Gloucestershire, UK
Distribution: Ubuntu, Debian & Gentoo
Posts: 74

Rep: Reputation: 16
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

Last edited by senyahnoj; 10-24-2006 at 11:12 AM.
 
Old 10-24-2006, 11:16 AM   #3
j1lted
LQ Newbie
 
Registered: Apr 2006
Location: Manchester
Distribution: Fedora
Posts: 4

Original Poster
Rep: Reputation: 0
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.
 
Old 10-24-2006, 11:32 AM   #4
paulsm4
Guru
 
Registered: Mar 2004
Distribution: SusE 8.2
Posts: 5,863
Blog Entries: 1

Rep: Reputation: Disabled
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.
 
Old 10-25-2006, 07:50 AM   #5
j1lted
LQ Newbie
 
Registered: Apr 2006
Location: Manchester
Distribution: Fedora
Posts: 4

Original Poster
Rep: Reputation: 0
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;
 
  


Reply

Tags
mysql


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Weird PHP error: Cannot create MySQL database from a function?!!? JockVSJock Programming 5 09-28-2006 08:46 PM
Call to undefined function php >> mysql open ticket imagineers7 Linux - Software 2 07-04-2006 10:36 PM
Strange problem with PHP5/MySQL update function gabebster Programming 9 04-06-2006 11:48 PM
Please Help, Linux mysql nfs : Bind on unix socket: Function not implemented nmotte Linux - Newbie 1 10-29-2005 12:52 PM
Need help with PHP and MySQL: Undefined function... Mega Man X Programming 12 09-15-2004 11:07 PM


All times are GMT -5. The time now is 02:10 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration