I have created stored procedure in MySQL in Window-7. it run successfully on windows. But when I switch to Ubuntu it gives error in the stored procedure. On windows I am using SQLyog for creating stored procedure. On Ubuntu, I run the SQL script and call that stored procedure but it gives error. Below is my stored procedure.
DELIMITER $$
USE `adserver`$$
DROP PROCEDURE IF EXISTS `getDaypartTimeDetail`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getDaypartTimeDetail`
(currentDate DATE,noOfdays INT,cityId BIGINT)
BEGIN
DECLARE i INT;
DECLARE dateCnt INT;
SET dateCnt = 0;
DROP TEMPORARY TABLE IF EXISTS OnlyDate;
DROP TEMPORARY TABLE IF EXISTS AdvScheduleData;
CREATE TEMPORARY TABLE OnlyDate(dday DATE);
CREATE TEMPORARY TABLE AdvScheduleData(dday BIGINT,daypartId INT,totalFile BIGINT,totalDur BIGINT);
/* Generate Dates */
WHILE(dateCnt < noOfdays) DO
SET i = 1;
INSERT INTO OnlyDate(dday) VALUES (DATE_ADD(currentDate, INTERVAL dateCnt DAY));
SET dateCnt = dateCnt + 1;
END WHILE;
/* Insert all dayparts for all dates */
INSERT INTO AdvScheduleData (dday, daypartID) SELECT (UNIX_TIMESTAMP(dday)*1000), id FROM OnlyDate, daypart;
/* Update total files and duration */
UPDATE AdvScheduleData SET
TotalFile = (SELECT COUNT(advt_id)
FROM adv_schedule AdvSch
INNER JOIN advertisement Adv ON Adv.id = AdvSch.advt_id
WHERE AdvScheduleData.dday BETWEEN AdvSch.start_date AND AdvSch.end_date
AND AdvSch.status = 2
AND AdvSch.active = 1
AND AdvSch.id IN (SELECT schedule_id FROM schedule_daypart
WHERE daypart_id = AdvScheduleData.daypartId )
AND AdvSch.id IN (SELECT schedule_id FROM schedule_cities WHERE city_id = cityId)
AND Adv.is_active = 1
AND Adv.is_deleted = 0
AND Adv.status = 2
AND Adv.expiry_date >= AdvScheduleData.dday),
totalDur = (SELECT SUM(Adv.duration)
FROM adv_schedule AdvSch
INNER JOIN advertisement Adv ON Adv.id = ADVSCH.advt_id
WHERE AdvScheduleData.dday BETWEEN AdvSch.start_date AND AdvSch.end_date
AND AdvSch.status = 2
AND AdvSch.active = 1
AND AdvSch.id IN (SELECT schedule_id FROM schedule_daypart
WHERE daypart_id = AdvScheduleData.daypartId )
AND AdvSch.id IN (SELECT schedule_id FROM schedule_cities WHERE city_id = cityId)
AND Adv.is_active = 1
AND Adv.is_deleted = 0
AND Adv.status = 2
AND Adv.expiry_date >= AdvScheduleData.dday);
SELECT * FROM AdvScheduleData;
END$$
DELIMITER ;
The output I get in Ubuntu is
mysql> call getDaypartTimeDetail('2012-08-13',5,30534); ERROR 1054 (42S22): Unknown column 'ADVSCH.advt_id' in 'on clause'
