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
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