Bug #62438 Temporary Table in Routine
Description:
I create routine to display data where total field in the result table is not fixed, depend on period that will be processed.
I know that temporary table is still exist as long as user is still connect to the server. I try to execute the routine with vstartdate= '2011-01-01' and vendate='2011-03-01'. I execute using MySqlWorkBench version 5.2.34.
There is no problem when I execute the routine. The problem are when I execute the routine for the second and so on.
Result.
1. If I just run the application without modify the date parameters. It is OK.
2. If I run the application with modify vendDate paramter, greater than than March then, it still show 3 months columns. There aren't' column for month 4 and so on.
3. If I rub the application with modify vendDate parameter become '2011-02-01' then there is an error message.
Error Code: 1054. Unknown column 'dbname.vtblreportData.Mar_2011' in 'field list'.
How to repeat:
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetReportDataByGroupId`(
vmsrGroupId INT,
vkpiList VARCHAR(100),
vreportType VARCHAR(20),
vaverageType VARCHAR(20),
vtagList VARCHAR(100),
vstartDate DATETIME,
vendDate DATETIME,
vvariance VARCHAR(20))
BEGIN
DROP TEMPORARY TABLE IF EXISTS vtblreportData;
CREATE TEMPORARY TABLE IF NOT EXISTS vtblreportData
(
GroupingId INT,
KpiId INT,
KpiName NVARCHAR(50),
Margin VARCHAR(10),
DataType INT,
DataType_Tx VARCHAR(50),
KpiFormatId INT,
KpiDecimal INT,
Primary Key (GroupingId, KpiID, DataType)
);
SET vcursorDate = vstartDate;
WHILE Period_Diff(Date_Format(vendDate, '%Y%m'),
Date_Format(vcursorDate, '%Y%m')) <> -1
DO
CASE
WHEN MONTH(vcursorDate) = 1 THEN set vmonthYearField = 'Jan';
WHEN MONTH(vcursorDate) = 2 THEN set vmonthYearField ='Feb';
WHEN MONTH(vcursorDate) = 3 THEN set vmonthYearField ='Mar';
WHEN MONTH(vcursorDate) = 4 THEN set vmonthYearField ='Apr';
WHEN MONTH(vcursorDate) = 5 THEN set vmonthYearField ='May';
WHEN MONTH(vcursorDate) = 6 THEN set vmonthYearField ='Jun';
WHEN MONTH(vcursorDate) = 7 THEN set vmonthYearField ='Jul';
WHEN MONTH(vcursorDate) = 8 THEN set vmonthYearField ='Aug';
WHEN MONTH(vcursorDate) = 9 THEN set vmonthYearField ='Sep';
WHEN MONTH(vcursorDate) = 10 THEN set vmonthYearField = 'Oct';
WHEN MONTH(vcursorDate) = 11 THEN set vmonthYearField = 'Nov';
WHEN MONTH(vcursorDate) = 12 THEN set vmonthYearField = 'Dec';
ELSE set vmonthYearField ='';
END Case;
#Testing
SET vmonthYearField = concat(vmonthYearField, '_', cast(YEAR(vcursorDate) as char(4)) );
SET @sql = concat('ALTER TABLE vtblreportData ADD ', vmonthYearField, ' DECIMAL(18, 6)');
SET vmonthYearFieldTx = concat(vmonthYearField, '_Tx');
SET @sql = Concat(@sql, ', ADD ', vmonthYearFieldTx, ' VARCHAR(50)');
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
SET vcursorDate = DATE_ADD(vcursorDate, INTERVAL 1 MONTH);
END WHILE;
#select 'After adding field';
ALTER TABLE vtblreportData ADD Total DECIMAL(18, 6), ADD Total_Tx VARCHAR(100);
....
<< Input Data to the Table >>
....
select * from vtblreportData
END
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
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.
Description: I create routine to display data where total field in the result table is not fixed, depend on period that will be processed. I know that temporary table is still exist as long as user is still connect to the server. I try to execute the routine with vstartdate= '2011-01-01' and vendate='2011-03-01'. I execute using MySqlWorkBench version 5.2.34. There is no problem when I execute the routine. The problem are when I execute the routine for the second and so on. Result. 1. If I just run the application without modify the date parameters. It is OK. 2. If I run the application with modify vendDate paramter, greater than than March then, it still show 3 months columns. There aren't' column for month 4 and so on. 3. If I rub the application with modify vendDate parameter become '2011-02-01' then there is an error message. Error Code: 1054. Unknown column 'dbname.vtblreportData.Mar_2011' in 'field list'. How to repeat: CREATE DEFINER=`root`@`localhost` PROCEDURE `GetReportDataByGroupId`( vmsrGroupId INT, vkpiList VARCHAR(100), vreportType VARCHAR(20), vaverageType VARCHAR(20), vtagList VARCHAR(100), vstartDate DATETIME, vendDate DATETIME, vvariance VARCHAR(20)) BEGIN DROP TEMPORARY TABLE IF EXISTS vtblreportData; CREATE TEMPORARY TABLE IF NOT EXISTS vtblreportData ( GroupingId INT, KpiId INT, KpiName NVARCHAR(50), Margin VARCHAR(10), DataType INT, DataType_Tx VARCHAR(50), KpiFormatId INT, KpiDecimal INT, Primary Key (GroupingId, KpiID, DataType) ); SET vcursorDate = vstartDate; WHILE Period_Diff(Date_Format(vendDate, '%Y%m'), Date_Format(vcursorDate, '%Y%m')) <> -1 DO CASE WHEN MONTH(vcursorDate) = 1 THEN set vmonthYearField = 'Jan'; WHEN MONTH(vcursorDate) = 2 THEN set vmonthYearField ='Feb'; WHEN MONTH(vcursorDate) = 3 THEN set vmonthYearField ='Mar'; WHEN MONTH(vcursorDate) = 4 THEN set vmonthYearField ='Apr'; WHEN MONTH(vcursorDate) = 5 THEN set vmonthYearField ='May'; WHEN MONTH(vcursorDate) = 6 THEN set vmonthYearField ='Jun'; WHEN MONTH(vcursorDate) = 7 THEN set vmonthYearField ='Jul'; WHEN MONTH(vcursorDate) = 8 THEN set vmonthYearField ='Aug'; WHEN MONTH(vcursorDate) = 9 THEN set vmonthYearField ='Sep'; WHEN MONTH(vcursorDate) = 10 THEN set vmonthYearField = 'Oct'; WHEN MONTH(vcursorDate) = 11 THEN set vmonthYearField = 'Nov'; WHEN MONTH(vcursorDate) = 12 THEN set vmonthYearField = 'Dec'; ELSE set vmonthYearField =''; END Case; #Testing SET vmonthYearField = concat(vmonthYearField, '_', cast(YEAR(vcursorDate) as char(4)) ); SET @sql = concat('ALTER TABLE vtblreportData ADD ', vmonthYearField, ' DECIMAL(18, 6)'); SET vmonthYearFieldTx = concat(vmonthYearField, '_Tx'); SET @sql = Concat(@sql, ', ADD ', vmonthYearFieldTx, ' VARCHAR(50)'); PREPARE stmt1 FROM @sql; EXECUTE stmt1; DEALLOCATE PREPARE stmt1; SET vcursorDate = DATE_ADD(vcursorDate, INTERVAL 1 MONTH); END WHILE; #select 'After adding field'; ALTER TABLE vtblreportData ADD Total DECIMAL(18, 6), ADD Total_Tx VARCHAR(100); .... << Input Data to the Table >> .... select * from vtblreportData END