alternate of temporary table?
Hi,
I am trying to write a procedure like -
DELIMITER $$
USE `neighborly`$$
DROP PROCEDURE IF EXISTS `sorted_events`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sorted_events`(p_userid INT(10)UNSIGNED, p_latitude DOUBLE, p_longitude DOUBLE,l_limit INT(10), p_search_key VARCHAR(255))
BEGIN
DROP TABLE IF EXISTS sort_event;
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE v_eventid INT(10) UNSIGNED;
DECLARE v_starttime DATETIME;
DECLARE v_distance DOUBLE;
DECLARE v_total_avg DOUBLE;
DECLARE i INT;
DECLARE c_event CURSOR FOR
SELECT * FROM (SELECT e.id ,e.startdatetime,((SELECT IFNULL(( 3959 * ACOS( COS( RADIANS(p_latitude) ) * COS( RADIANS( latitude ) ) * COS( RADIANS( longitude ) - RADIANS(p_longitude) ) + SIN( RADIANS(p_latitude) ) * SIN( RADIANS( latitude ) ))),0) FROM address a WHERE latitude!='' AND longitude!='' AND e.event_address=id))
AS dest
FROM event e WHERE e.startdatetime>NOW() AND (e.description LIKE CONCAT('%', IFNULL(p_search_key,'%'), '%') OR e.organized_by IN (SELECT userid FROM users WHERE firstname LIKE CONCAT('%', IFNULL(p_search_key,'%'), '%')))
AND ((e.maximumAttendeesAllow > ((SELECT COUNT(*) AS cnt FROM event_interest WHERE eventid=e.id AND approvalStatus='Approve')+(SELECT COUNT(*) AS cnt FROM event_invite WHERE eventid=e.id AND acceptance='Accept')))OR (e.maximumAttendeesAllow = 0))
AND e.isEventDeleted=FALSE AND e.isNeighborlyInvited=TRUE AND e.organized_by!=p_userid AND EXISTS (SELECT id FROM address a WHERE latitude!='' AND longitude!='' AND id=e.event_address AND IFNULL(( 3959 * ACOS( COS( RADIANS(p_latitude) ) * COS( RADIANS( latitude ) ) * COS( RADIANS( longitude ) - RADIANS(p_longitude) ) + SIN( RADIANS(p_latitude) ) * SIN( RADIANS( latitude ) ))),0)<100) AND NOT EXISTS (SELECT eventid FROM event_interest WHERE eventid =e.id AND approvalStatus!='InterestExpressed' AND interested_user=p_userid) AND NOT EXISTS (SELECT eventid FROM event_invite WHERE eventid=e.id AND invited_user=p_userid )
) AS a
UNION
SELECT * FROM (SELECT e.id ,e.startdatetime ,((SELECT IFNULL(( 3959 * ACOS( COS( RADIANS(p_latitude) ) * COS( RADIANS( latitude ) ) * COS( RADIANS( longitude ) - RADIANS(p_longitude) ) + SIN( RADIANS(p_latitude) ) * SIN( RADIANS( latitude ) ))),0) FROM address a WHERE latitude!='' AND longitude!='' AND e.event_address=id))
AS dest
FROM event e WHERE e.startdatetime<NOW() AND (e.description LIKE CONCAT('%', IFNULL(p_search_key,'%'), '%') OR e.organized_by IN (SELECT userid FROM users WHERE firstname LIKE CONCAT('%', IFNULL(p_search_key,'%'), '%')))
AND ((e.maximumAttendeesAllow > ((SELECT COUNT(*) AS cnt FROM event_interest WHERE eventid=e.id AND approvalStatus='Approve')+(SELECT COUNT(*) AS cnt FROM event_invite WHERE eventid=e.id AND acceptance='Accept')))OR (e.maximumAttendeesAllow = 0)) AND e.isEventDeleted=FALSE AND e.isNeighborlyInvited=TRUE AND e.organized_by!=p_userid AND e.event_address IN (SELECT id FROM address a WHERE latitude!='' AND longitude!='' AND IFNULL(( 3959 * ACOS( COS( RADIANS(p_latitude) ) * COS( RADIANS( latitude ) ) * COS( RADIANS( longitude ) - RADIANS(p_longitude) ) + SIN( RADIANS(p_latitude) ) * SIN( RADIANS( latitude ) ))),0)<100) AND id NOT IN (SELECT eventid FROM event_interest WHERE approvalStatus!='InterestExpressed' AND interested_user=p_userid) AND id NOT IN (SELECT eventid FROM event_invite WHERE invited_user=p_userid )
)AS b;
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET v_finished = 1;
CREATE TABLE sort_event (eventid INT(10) UNSIGNED ,starttime DATETIME, final_distance DOUBLE) ENGINE = MEMORY ;
OPEN c_event;
event_loop: LOOP
FETCH c_event INTO v_eventid, v_starttime, v_distance;
IF v_finished = 1 THEN
LEAVE event_loop;
END IF;
SELECT ABS((IFNULL(SUM(sum_attendy.age),0) / SUM(sum_attendy.cnt)) - (SELECT IFNULL(YEAR(NOW())-YEAR(dob),0) AS age FROM user_detail WHERE userid = p_userid)) + v_distance AS tot_num
INTO v_total_avg
FROM ( SELECT IFNULL(SUM(YEAR(NOW())-YEAR(dob)),0) AS age, COUNT(*) AS cnt FROM user_detail WHERE userid IN
(SELECT interested_user FROM event_interest WHERE eventid=v_eventid AND approvalStatus='Approve')
UNION ALL
SELECT IFNULL(SUM(YEAR(NOW())-YEAR(dob)),0) AS age, COUNT(*) AS cnt FROM user_detail WHERE userid IN
(SELECT invited_user FROM event_invite WHERE eventid=v_eventid AND acceptance='Accept')
UNION ALL
SELECT IFNULL(YEAR(NOW())-YEAR(dob),0) AS age, COUNT(*) AS cnt FROM user_detail WHERE userid = p_userid)sum_attendy ;
INSERT INTO sort_event VALUES(v_eventid, v_starttime, v_total_avg);
END LOOP event_loop;
CLOSE c_event;
SET i = l_limit;
(SELECT eventid FROM sort_event WHERE starttime>NOW() GROUP BY starttime,final_distance)
UNION ALL
(SELECT eventid FROM sort_event WHERE starttime<NOW() GROUP BY starttime DESC,final_distance)
LIMIT i,15;
DROP TABLE IF EXISTS sort_event;
END$$
DELIMITER ;
In between this I am using a temporary table which is not actually TEMPORARY table, but I dont want to use this table. Is there any other way apart from tables in SP, that I can perform the same option. Ordering is very important (in last select with union).
So please let me know if I can store that intermediate data somewhere.