MySQL Forums
Forum List  »  Stored Procedures

alternate of temporary table?
Posted by: Aamir Sohail
Date: March 28, 2014 06:07AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
alternate of temporary table?
2489
March 28, 2014 06:07AM
2534
March 30, 2014 10:45PM
1105
March 31, 2014 02:18AM
1031
March 31, 2014 09:46AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.