portlight
asked on
Query acts like it is not able to select from the temp table
Query acts like it is not able to select from the temp table as it give a field not found error. What is wrong with this code?
DELIMITER //
/* Author: Jessie Brown
Date: 8/17/13
Notes: Template Creation
Comments: Comments created in the header area will NOT be stored in the database write
Comments created AFTer the begin statement will be retained within the database write
*/
-- checks to see if stored procedure exists
-- if exists the procedure is dropped and recreated
DROP PROCEDURE IF EXISTS `reportFreeCoolingNew`//
-- Creates new procedure
CREATE PROCEDURE reportFreeCoolingNew(
IN fromDate VARCHAR (50),
IN toDate VARCHAR (50),
IN timeZone VARCHAR (50)
)
BEGIN
DECLARE startDate VARCHAR (50);
DECLARE endDate VARCHAR (50);
DECLARE mylogID INT;
DECLARE myItemId varchar (50);
DECLARE myItemId2 varchar (50);
DECLARE xHours varchar (50);
SET startDate = FROM_UNIXTIME(fromDate/1000);
SET endDate = FROM_UNIXTIME(toDate/1000);
SET mylogID = (SELECT logID FROM logs WHERE details LIKE 'FCT%' LIMIT 1);
SET myItemID = (SELECT i.itemId FROM items i WHERE i.name LIKE '%KW PRE%');
SET myItemID2 = (SELECT i.itemId FROM items i WHERE i.name LIKE '%KW STA%');
SET @sql = NULL;
SET @sql = CONCAT(
'CREATE TEMPORARY TABLE tempTable3 AS (SELECT
@row := @row + 1 AS rownum,
a.logId,
L1.recordId,
L2.recordId AS next_recordId,
L1.completed,
L2.completed AS next_completed,
L1.activityId AS activityJoin,
L2.activityId AS next_activityId,
IF(L1.activityId = L2.activityId, 1, NULL) AS isError,
CASE
when a.name LIKE ''%Enable%'' THEN time_to_sec(timediff(L2.completed, L1.completed)) / 3600
ELSE NULL
END AS coolingHours,
TO_SECONDS(L2.completed) - TO_SECONDS(L1.completed) AS timeInSeconds,
((SUBSTR(l.details, INSTR(l.details, '':'') + 1))) AS charge,
time_to_sec(timediff(''', endDate, ''', ''', startDate, ''')) / 3600 AS totalTimeRange,
CONVERT_TZ(''', startDate, ''', ''UTC'', ''US/Pacific'') AS startingDate,
CONVERT_TZ(''', endDate, ''', ''UTC'', ''US/Pacific'') AS endingDate,
((L1.item', myItemID, ' - L1.item', myItemID2, ') *
(TIME_TO_SEC(TIMEDIFF(L2.completed, L1.completed)))) / 3600 AS kwDifference,
((L1.item', myItemID, ' - L1.item', myItemID2, ') * (SUBSTR(l.details, INSTR(l.details, '':'') + 1))) AS cost,
((((L1.item', myItemID, ' - L1.item', myItemID2, ') * (SUBSTR(l.details, INSTR(l.details,'':'') + 1)))
* (TIME_TO_SEC(TIMEDIFF(L2.completed, L1.completed)) / 3600))) AS costT,
DATABASE() AS databaseName, i.itemId ,l.details
FROM
(SELECT @row:=0)R,
(SELECT T1.completed,
(SELECT MIN(completed)
FROM log',mylogID, '
WHERE completed > T1.completed) AS next_completed
FROM log',mylogID, ' T1
ORDER BY T1.completed
)TimeOrder
LEFT JOIN log', mylogID, ' L1 ON (L1.completed = TimeOrder.completed)
LEFT JOIN log', mylogID, ' L2 ON (L2.completed = TimeOrder.next_completed)
LEFT JOIN activities a ON L1.activityId = a.activityId
LEFT JOIN logs l ON a.logId = l.logId
inner join items i ON l.logId = i.logId AND i.name LIKE ''%KW PRE%''
)');
SET @sqlTemp = NULL;
SET @sqlTemp = CONCAT(
'SELECT
rowNum,l.logId,l.completed,l.next_completed,
l.first_completed,isError,charge,totalTimeRange,startingDate,endingDate,coolingHours,
databaseName,i.name,l.itemId, kwDifference, cost, costT,l.details,timeInSeconds
FROM tempTable3 l
inner join items i ON l.logId = i.logId AND i.name LIKE ''%KW PRE%''
WHERE l.itemId = ''',myItemId,'''
AND completed BETWEEN ''', startDate, ''' AND ''', endDate, '''
ORDER BY completed');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
PREPARE stmtTemp FROM @sqlTemp;
EXECUTE stmtTemp;
DEALLOCATE PREPARE stmtTemp;
DROP TEMPORARY TABLE tempTable3;
END //
DELIMITER ;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Love it when there is a simple solution! Thanks for the points and thanks for using EE, ~Ray
ASKER