Dong Shin 12.06.2012

  • forgot to include ‘DELIMITER $$’ for stored procedures this morning, will try again tomorrow
  • stored procedure to separate service project mgrs in the alerts table – need more tweaks to work with VSS
    • DELIMITER $$DROP PROCEDURE IF EXISTS explode_table $$
      CREATE PROCEDURE explode_table(bound VARCHAR(255))BEGIN

      DECLARE uid INT DEFAULT 0;
      DECLARE user TEXT;
      DECLARE occurance INT DEFAULT 0;
      DECLARE i INT DEFAULT 0;
      DECLARE splitted_user TEXT;
      DECLARE portfolio_admins TEXT;
      DECLARE portfolio_mgrs TEXT;
      DECLARE service_finance_pocs TEXT;
      DECLARE program TEXT;
      DECLARE MIPR TEXT;
      DECLARE alert TEXT;
      DECLARE status TEXT;
      DECLARE timestamp TIMESTAMP;
      DECLARE done INT DEFAULT 0;
      DECLARE cur1 CURSOR FOR SELECT alerts.uid, alerts.service_project_mgrs,
      alerts.portfolio_admins, alerts.portfolio_mgrs,
      alerts.service_finance_pocs, alerts.program,
      alerts.MIPR, alerts.alert, alerts.status, alerts.timestamp
      FROM alerts
      WHERE alerts.service_project_mgrs != ”;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

      DROP TEMPORARY TABLE IF EXISTS table2;
      CREATE TEMPORARY TABLE table2(
      `uid` INT NOT NULL,
      `user` VARCHAR(255) NOT NULL,
      `portfolio_admins` VARCHAR(255),
      `portfolio_mgrs` VARCHAR(255),
      `service_finance_pocs` VARCHAR(255),
      `program` VARCHAR(255),
      `MIPR` VARCHAR(255),
      `alert` VARCHAR(255),
      `status` VARCHAR(255),
      `timestamp` TIMESTAMP
      ) ENGINE=Memory;

      OPEN cur1;
      read_loop: LOOP
      FETCH cur1 INTO uid, user, portfolio_admins, portfolio_mgrs, service_finance_pocs, program, MIPR, alert, status, timestamp;
      IF done THEN
      LEAVE read_loop;
      END IF;

      SET occurance = (SELECT LENGTH(user)
      – LENGTH(REPLACE(user, bound, ”))
      +1);
      SET i=1;
      WHILE i <= occurance DO
      SET splitted_user = (SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(user, bound, i),
      LENGTH(SUBSTRING_INDEX(user, bound, i – 1)) + 1), ‘,’, ”));

      INSERT INTO table2 VALUES (uid, splitted_user, portfolio_admins, portfolio_mgrs, service_finance_pocs,
      program, MIPR, alert, status, timestamp);
      SET i = i + 1;

      END WHILE;
      END LOOP;

      SELECT * FROM table2;
      CLOSE cur1;
      END; $$