create function using CASE statement

[sneakyimp]

I'm working on a data migration script. It will move data from one database to another with a new, different format.

At one point, I need to insert about 6 records, which map some old scattered IDs onto some new contiguous ones. E.g., 1 becomes 1, 2 becomes 2....9 becomes 5, 10 becomes 6.

Since I have to run a number of insert queries that all use this mapping, i thought it wise to create a function which uses a case statement. This code is giving me an error:


CREATE FUNCTION DEPT_ID_TO_CAT_ID (deptID INT(2))
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE catID INT;
SET catID = CASE deptID
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
WHEN 9 THEN 5
WHEN 10 THEN 6
ELSE NULL
END;
RETURN catID;
END


The error I get is "#1064 - 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 'INT' at line 5 "

I'm using MySQL v. 5. Any help would be much appreciated. And let me know if this is a stupid way to go about this.

[Phill Pafford]

I see your CASE logic but I don't see you declaring the END CASE statement.
Not sure if this is correct


CREATE FUNCTION DEPT_ID_TO_CAT_ID (deptID INT(2))
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE catID INT;
SET catID = CASE deptID
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
WHEN 9 THEN 5
WHEN 10 THEN 6
ELSE NULL
END;
END CASE;

RETURN catID;
END


Link for more help here (http://dev.mysql.com/doc/refman/5.0/en/case-statement.html)

[sneakyimp]

I tried this, still getting an error.

CREATE FUNCTION DEPT_ID_TO_CAT_ID (deptID INT(2))
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE catID INT;
SET catID = CASE deptID
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
WHEN 9 THEN 5
WHEN 10 THEN 6
ELSE NULL
END CASE;

RETURN catID;
END


the error is #1064 - 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 'INT' at line 5

I've tried INT, INT(2), INTEGER...none of these seem to work.

[Phill Pafford]

Hmm, well give this a try


CREATE FUNCTION DEPT_ID_TO_CAT_ID (deptID INT())
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE catID INT DEFAULT NULL;
CASE deptID
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
WHEN 9 THEN 5
WHEN 10 THEN 6
ELSE NULL
END AS catID;

RETURN catID;
END


OR


CREATE FUNCTION DEPT_ID_TO_CAT_ID (deptID INT(2))
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE catID INT;
CASE deptID
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
WHEN 9 THEN 5
WHEN 10 THEN 6
ELSE NULL
END AS catID;

RETURN catID;
END

[sneakyimp]

The first gives this error:
#1064 - 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 '))
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE catID INT DEFAULT NULL' at line 1


The second gives this error:
Documentation
#1064 - 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 'INT' at line 5

[Phill Pafford]

ok one last idea


CREATE FUNCTION DEPT_ID_TO_CAT_ID (deptID INT())
RETURNS INT
DETERMINISTIC
BEGIN

CASE deptID
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
WHEN 9 THEN 5
WHEN 10 THEN 6
ELSE NULL
END AS catID;

RETURN catID;
END

[sneakyimp]

no dice:
#1064 - 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 '))
RETURNS INT
DETERMINISTIC
BEGIN

CASE deptID
WHEN ' at line 1

[Phill Pafford]

CREATE FUNCTION DEPT_ID_TO_CAT_ID (deptID INT)
RETURNS INT
DETERMINISTIC
BEGIN

CASE deptID
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
WHEN 9 THEN 5
WHEN 10 THEN 6
ELSE NULL
END AS catID;

RETURN catID;
END

[sneakyimp]

Well we seem to have gotten past line 5.

#1064 - 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 '1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
WHEN 9 T' at line 6

[Phill Pafford]

ok one more time, LOL


CREATE FUNCTION DEPT_ID_TO_CAT_ID (deptID INT(2))
RETURNS INT(2)
DETERMINISTIC
BEGIN

DECLARE catID INT(2) DEFAULT NULL;

CASE
WHEN deptID = 1 THEN SET catID = 1
WHEN deptID = 2 THEN SET catID = 2
WHEN deptID = 3 THEN SET catID = 3
WHEN deptID = 4 THEN SET catID = 4
WHEN deptID = 5 THEN SET catID = 5
WHEN deptID = 10 THEN SET catID = 6
ELSE SET catID = NULL
END CASE;

RETURN catID;
END


Just incase NULL is not allowed as an INT return


CREATE FUNCTION DEPT_ID_TO_CAT_ID (deptID INT(2))
RETURNS INT(2)
DETERMINISTIC
BEGIN

DECLARE catID INT(2) DEFAULT 0;

CASE
WHEN deptID = 1 THEN SET catID = 1
WHEN deptID = 2 THEN SET catID = 2
WHEN deptID = 3 THEN SET catID = 3
WHEN deptID = 4 THEN SET catID = 4
WHEN deptID = 5 THEN SET catID = 5
WHEN deptID = 10 THEN SET catID = 6
ELSE SET catID = 0
END CASE;

RETURN catID;
END

[sneakyimp]

Crikey! I appreciate your persistence. I can't help but wonder if the problem is my version of MySQL - v 5.0.41. The first of those queries returns this error:
#1064 - 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 'INT(2) DEFAULT NULL' at line 6

The second this error:
#1064 - 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 'INT(2) DEFAULT 0' at line 6

I truly appreciate the effort, but am thinking i should probably do some tutorials or buy a book or something. There's something really fish about this not working.