Fellow residents of the Stack Exchange,
I am currently trying to create a procedure that will reformat data in a very major way. I get a list of data on tickets (summary for random months). The input views always have the same columns, but since I have to display it with the months as columns (which are usually entered as a value in the Monat column on the view). Sooo, after some major research, trial and error and a lot of headaches I got to make it work. The procedure accepts a single ticket-number and 'returns' all stats on that single ticket.
I do this by:
- Iterate over each distinct month, building a CREATE TEMPORARY TABLE statement.
- Execute that statement, then deallocate the statement
- Iterate over each ticket-value, sorting them into the temporary table with a INSERT INTO ON DUPLICATE KEY UPDATE statement (which I build, execute and deallocate each iteration anew)
- Again Iterate over each distinct month, building a last update to fill the summary columns (I know, I could combine this into Step 1, but I tried to keep the steps as separate as I could, just to make it easier to read, I can optimize once I'm done making it work as I want).
- Select the temporary table, so it's being returned
- Clean up some loose ends, because I'm a habitually neat person.
The good thing: It Works! The bad thing: It works only for one input value. Whenever I change it to another ticket number, that would require different months for columns it fails with an "Error Code 1054, Unknown Column in field list", referring to an old column (month) the current query ought not have. I can run the procedure as many times as I want, as long as the columns of the temporary table are identical. This behavior resets, whenever I drop and recreate the procedure or create a new connection.
Obviously, I'm forgetting to do a cleaning step somewhere along the way, and me being fairly new to SQL in general and MySQL in particular probably didn't even know to look for it :(.
Help would be most appreciated, thanks, Fred
DELIMITER //
CREATE PROCEDURE proc_get_relevant_tickets(bID VARCHAR(10))
DETERMINISTIC
READS SQL DATA
BEGIN
# Declare storage vairables for withdrawing data from view
DECLARE ID, FiID, ZVB, ZNVB, ZGes, WVB, WNVB, WGes INTEGER;
DECLARE Mon VARCHAR(50);
DECLARE RowVerb, RowNVerb, RowGes, Counter INTEGER;
DECLARE verbucht, nichtverbucht, gesamt VARCHAR(50);
DECLARE currentticket INTEGER;
DECLARE statezges, statewges LONGTEXT;
# Declare runtime stuff
DECLARE done INT DEFAULT FALSE;
DECLARE declaration LONGTEXT;
DECLARE temptext VARCHAR(50);
DECLARE Months CURSOR FOR SELECT DISTINCT Monat FROM view_list_of_tickets t WHERE bID = t.ID;
DECLARE `Values` CURSOR FOR SELECT * FROM view_list_of_tickets t WHERE bID = t.ID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
# Clean up in case something interrupted last execution
DROP TEMPORARY TABLE IF EXISTS temp_storage_5437485;
# If there are any entries to work upon
IF (SELECT COUNT(t.ID) FROM view_list_of_tickets t WHERE bID = t.ID > 0)
THEN
# Create temporary table to put the values into
SET declaration = 'CREATE TEMPORARY TABLE `temp_storage_5437485` (ID INTEGER PRIMARY KEY, TicketNr INTEGER, Category VARCHAR(50), ';
SET done = FALSE;
OPEN Months;
read_loop: LOOP
FETCH Months INTO temptext;
IF done THEN
LEAVE read_loop;
END IF;
SET declaration = CONCAT(declaration, '`', temptext, ' Zeit` INTEGER DEFAULT 0, ');
SET declaration = CONCAT(declaration, '`', temptext, ' Wert` INTEGER DEFAULT 0, ');
END LOOP;
CLOSE Months;
SET declaration = CONCAT(declaration, '`Gesamt Zeit` INTEGER, `Gesamt Wert` INTEGER);');
SELECT declaration INTO @declaration;
PREPARE stmt FROM @declaration;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
# End of creating the storage container
# Cycle through values and input into temporary table
SET done = FALSE;
SET verbucht = 'Verbucht';
SET nichtverbucht = 'Nicht Verbucht';
SET gesamt = 'Gesamt';
SET currentticket = 0;
SET Counter = 0;
SET RowVerb = 1;
SET RowNVerb = 2;
SET RowGes = 3;
OPEN `Values`;
read_values: LOOP
FETCH `Values` INTO ID, FiID, ZVB, ZNVB, ZGes, WVB, WNVB, WGes, Mon;
IF done THEN
LEAVE read_values;
END IF;
# If a new ticket has been arrived at, increment the counters
IF currentticket > 0 AND ID <> currentticket THEN
SET currentticket = ID;
SET Counter = Counter + 1;
SET RowVerb = RowVerb + 3;
SET RowNVerb = RowNVerb + 3;
SET RowGes = RowGes + 3;
END IF;
IF currentticket = 0 AND ID <> currentticket THEN
SET currentticket = ID;
END IF;
# Insert first (Verbucht) row
SET declaration = CONCAT('INSERT INTO `temp_storage_5437485` (`ID`, `TicketNr`, `', Mon, ' Zeit`, `', Mon, ' Wert`) VALUES (');
SET declaration = CONCAT(declaration, RowVerb, ', ', ID, ', ', ZVB, ', ', WVB, ') ON DUPLICATE KEY UPDATE ');
SET declaration = CONCAT(declaration, '`', Mon, ' Zeit`=', ZVB, ', `', Mon, ' Wert`=', WVB, ';');
SELECT declaration INTO @declaration;
PREPARE stmt FROM @declaration;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
# Insert second (Nicht Verbucht) row
SET declaration = CONCAT('INSERT INTO `temp_storage_5437485` (`ID`, `TicketNr`, `', Mon, ' Zeit`, `', Mon, ' Wert`) VALUES (');
SET declaration = CONCAT(declaration, RowNVerb, ', ', ID, ', ', ZNVB, ', ', WNVB, ') ON DUPLICATE KEY UPDATE ');
SET declaration = CONCAT(declaration, '`', Mon, ' Zeit`=', ZNVB, ', `', Mon, ' Wert`=', WNVB, ';');
SELECT declaration INTO @declaration;
PREPARE stmt FROM @declaration;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
# Insert third (Gesamt) row
SET declaration = CONCAT('INSERT INTO `temp_storage_5437485` (`ID`, `TicketNr`, `', Mon, ' Zeit`, `', Mon, ' Wert`) VALUES (');
SET declaration = CONCAT(declaration, RowGes, ', ', ID, ', ', ZGes, ', ', WGes, ') ON DUPLICATE KEY UPDATE ');
SET declaration = CONCAT(declaration, '`', Mon, ' Zeit`=', ZGes, ', `', Mon, ' Wert`=', WGes, ';');
SELECT declaration INTO @declaration;
PREPARE stmt FROM @declaration;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
UPDATE temp_storage_5437485 SET Category = verbucht WHERE temp_storage_5437485.ID = RowVerb LIMIT 5;
UPDATE temp_storage_5437485 SET Category = nichtverbucht WHERE temp_storage_5437485.ID = RowNVerb LIMIT 5;
UPDATE temp_storage_5437485 SET Category = gesamt WHERE temp_storage_5437485.ID = RowGes LIMIT 5;
END LOOP;
CLOSE `Values`;
# End of cycling for values input
# Being calculating the total values
SET declaration = 'UPDATE temp_storage_5437485 SET `Gesamt Zeit` = (';
SET statezges = '';
SET statewges = '';
SET done = FALSE;
OPEN Months;
read_loop: LOOP
FETCH Months INTO temptext;
IF done THEN
LEAVE read_loop;
END IF;
# If not the first entry, add more
IF statezges <> '' THEN
SET statezges = CONCAT(statezges, ' + ');
END IF;
IF statewges <> '' THEN
SET statewges = CONCAT(statewges, ' + ');
END IF;
# Add column name
SET statezges = CONCAT(statezges, 'temp_storage_5437485.`', temptext, ' Zeit`');
SET statewges = CONCAT(statewges, 'temp_storage_5437485.`', temptext, ' Wert`');
END LOOP;
CLOSE Months;
SET declaration = CONCAT(declaration, statezges, '), `Gesamt Wert` = (', statewges, ') LIMIT 100000');
SELECT declaration INTO @declaration;
PREPARE stmt FROM @declaration;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
# End calculating the total values
SELECT * FROM temp_storage_5437485;
DROP TEMPORARY TABLE IF EXISTS temp_storage_5437485;
ELSE
SELECT 'is null';
END IF;
DROP TEMPORARY TABLE IF EXISTS temp_storage_5437485;
END //
DELIMITER ;
