I'm quite a beginner to MySQL and I am trying to execute different prepared statements based on the conditions in case when clause. The problem is to find the median of LAT_N column of the table STATION and I want to use Case when for even and odd number of LAT_N records.
This one is my prepared statement for odd case:
SET @foo = (SELECT ROUND((COUNT(*)-1)/2) FROM STATION);
PREPARE STMT1 FROM 'SELECT ROUND(LAT_N,4) FROM STATION ORDER BY LAT_N LIMIT ?,1';
EXECUTE STMT1 USING @foo;
And this is for the even:
SET @foo1 = (SELECT ROUND(COUNT(*)/2) FROM STATION);
SET @foo2 = (SELECT ROUND((COUNT(*)+2)/2) FROM STATION);
PREPARE STMT2 FROM 'SELECT ROUND((((SELECT ROUND(LAT_N,4) FROM STATION ORDER BY LAT_N LIMIT ?,1) + (SELECT ROUND(LAT_N,4) FROM STATION ORDER BY LAT_N LIMIT ?,1))/2),4)';
EXECUTE STMT2 USING @foo1, @foo2;
And my idea is a bit similar to this code:
SELECT CASE WHEN COUNT(*)%2=0 THEN EXECUTE STMT2 USING @foo1, @foo2
ELSE EXECUTE STMT1 USING @foo
FROM STATION
END;
But that code returns error 1064 (42000) so I wonder if there's any alternative for it. Thanks a lot
I've found the answer for this, which includes the use of procedure
DELIMITER $$
CREATE PROCEDURE latmed()
BEGIN
IF (SELECT COUNT(*) FROM STATION)%2=0 THEN
SET @foo1 = (SELECT ROUND(COUNT(*)/2) FROM STATION);
SET @foo2 = (SELECT ROUND((COUNT(*)+2)/2) FROM STATION);
PREPARE STMT2 FROM 'SELECT ROUND((((SELECT ROUND(LAT_N,4) FROM STATION ORDER BY LAT_N LIMIT ?,1) + (SELECT ROUND(LAT_N,4) FROM STATION ORDER BY LAT_N LIMIT ?,1))/2),4)';
EXECUTE STMT2 USING @foo1, @foo2;
ELSE
SET @foo = (SELECT ROUND((COUNT(*)-1)/2) FROM STATION);
PREPARE STMT1 FROM 'SELECT ROUND(LAT_N,4) FROM STATION ORDER BY LAT_N LIMIT ?,1';
EXECUTE STMT1 USING @foo;
END IF;
END$$
DELIMITER ;
CALL latmed()
I think it's a bit long so if anyone has a shorter code can answer my question. Thanks