Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #New procedure
- DELIMITER $$
- DROP PROCEDURE IF EXISTS createSelectView$$
- CREATE PROCEDURE createSelectView(
- IN customObjectName VARCHAR(255)
- )
- BEGIN
- DECLARE done INT DEFAULT FALSE;
- DECLARE columName VARCHAR(255);
- DECLARE selectColumns TEXT;
- DECLARE idTemp INT;
- DECLARE finalQuery LONGTEXT;
- DECLARE subQuery TEXT;
- DECLARE columnDataTypes TEXT;
- DECLARE columnNames TEXT;
- DECLARE entityIdName VARCHAR(255);
- DECLARE tempVar LONGTEXT;
- DECLARE cur1 CURSOR FOR SELECT DISTINCT UDFsName from CustomEntityDisplayValue where custObjName = customObjectName; #subquery
- DECLARE cur2 CURSOR FOR SELECT DISTINCT custEntId FROM CustomEntityDisplayValue where custObjName = customObjectName ; #subquery
- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
- SET entityIdName = 'entity_id';
- SET selectColumns= '';
- SET columnDataTypes= '';
- SET columnNames= '';
- OPEN cur1;
- read_loop: LOOP
- FETCH cur1 INTO columName;
- IF done THEN LEAVE read_loop; END IF;
- IF (LENGTH(columName) < 64) THEN
- SET columnDataTypes = CONCAT(columnDataTypes,', \'',columName, '\' TEXT');
- SET columnNames = CONCAT(columnNames,', \'',columName,'\'');
- SET selectColumns = CONCAT(selectColumns, ', max((case when UDFsName = \'', columName, '\' then UDVCustEntValue end )) as \'', columName, '\'');
- END IF;
- END LOOP;
- CLOSE cur1;
- SET selectColumns = CONCAT('max(UDFUpdatedAt) AS ', 'updatedAt',' ', selectColumns);
- SET selectColumns = CONCAT('max(UDFCreatedAt) AS ', 'createdAt,',' ', selectColumns);
- SET selectColumns = CONCAT('max(custEntId) AS ', 'entity_id,',' ', selectColumns);
- SET finalQuery= '';
- SET done = FALSE;
- SET subQuery = 'select * from CustomEntityDisplayValue '; #subquery
- SET columnDataTypes = CONCAT('( ', entityIdName, ' INT ', columnDataTypes, ')');
- SET columnNames = CONCAT(entityIdName, ' ', columnNames);
- OPEN cur2;
- read_loop: LOOP
- FETCH cur2 INTO idTemp;
- IF done THEN LEAVE read_loop; END IF;
- SET finalQuery = CONCAT(finalQuery, ' UNION SELECT ', selectColumns, ' from CustomEntityDisplayValue where custEntId = ', idTemp);
- END LOOP;
- CLOSE cur2;
- SET finalQuery = SUBSTR(finalQuery, 7);
- #select finalQuery;
- select LENGTH(finalQuery);
- SET @s = CONCAT('CREATE OR REPLACE VIEW ', customObjectName, ' AS ', finalQuery, ' ');
- PREPARE stmt FROM @s; EXECUTE stmt;
- END$$
- DELIMITER ;
- #call createSelectView('clients');
- #ALTER TABLE UDFs MODIFY display VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement