I need to get the result table with there fields - table_name, min_date, max_date
Here is my query, which I should execute for all tables
SELECT MIN(short_date) as FirstDuplicatedDate, MAX(short_date) as LastDuplicatedDate FROM (SELECT short_date, type, value, count(*) as cnt FROM testTable GROUP BY short_date HAVING COUNT(*) > 1) as Duplicates Then I found out how to get all table names I do it in this way
SELECT TABLE_NAME as name FROM `information_schema`.`TABLES` WHERE `TABLES`.`TABLE_SCHEMA` = 'test' AND `TABLES`.`TABLE_NAME` LIKE 'test%' But I don't know how to execute it for all table and fill in the result in a new table.
I tried to do it in this way
DECLARE @DB_Name varchar(50) DECLARE @Command varchar(100); DECLARE database_cursor CURSOR FOR SELECT name FROM (SELECT TABLE_NAME as name FROM `information_schema`.`TABLES` WHERE `TABLES`.`TABLE_SCHEMA` = 'test' AND `TABLES`.`TABLE_NAME` LIKE 'test%') as TableNames OPEN database_cursor FETCH NEXT FROM database_cursor INTO @DB_Name WHILE @@FETCH_STATUS = 0 BEGIN SELECT @Command = 'SELECT MIN(short_date) as FirstDuplicatedDate, MAX(short_date) as LastDuplicatedDate FROM (SELECT short_date, type, value, count(*) as cnt FROM ' + @DB_Name + ' WHERE type = ''test'' GROUP BY short_date, type, value HAVING COUNT(*) > 1) as Duplicates' EXEC sp_executesql @Command FETCH NEXT FROM database_cursor INTO @DB_Name END CLOSE database_cursor DEALLOCATE database_cursor But I got this error
Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @DB_Name varchar(50) DECLARE @Command varchar(100)' at line 1
UPD
CREATE PROCEDURE GetData() BEGIN DECLARE @DB_Name varchar(50), @Command varchar(100); DECLARE database_cursor CURSOR FOR SELECT name FROM (SELECT TABLE_NAME as name FROM `information_schema`.`TABLES` WHERE `TABLES`.`TABLE_SCHEMA` = 'test' AND `TABLES`.`TABLE_NAME` LIKE 'test%_') as TableNames OPEN database_cursor FETCH NEXT FROM database_cursor INTO @DB_Name WHILE @@FETCH_STATUS = 0 BEGIN SELECT @Command = 'SELECT MIN(short_date) as FirstDuplicatedDate, MAX(short_date) as LastDuplicatedDate FROM (SELECT short_date, type, value, count(*) as cnt FROM ' + @DB_Name + ' WHERE type = ''test'' GROUP BY short_date, type, value HAVING COUNT(*) > 1) as Duplicates' EXEC sp_executesql @Command FETCH NEXT FROM database_cursor INTO @DB_Name END; CLOSE database_cursor DEALLOCATE database_cursor END; CALL GetData() 3 Answers
Answers 1
Add DELIMITER $$ at the start; add DELIMITER ; after END.
Get rid of declaring Command. Instead use @command, which does not need to be declared.
Add SELECT @command; after the SELECT @command := ...; so that we can do some debugging.
The CLOSE and DEALLOCATE statements need ; to terminate them.
Test for running out of rows to FETCH.
You really need to look at a number of examples of Stored procedures, especially those with cursors.
Update
Ugh, I did not spot even half the syntax errors. This might work (I can't tell because I don't have your particular tables or columns.):
DROP PROCEDURE IF EXISTS so42856538; DELIMITER $$ CREATE PROCEDURE so42856538() LANGUAGE SQL MODIFIES SQL DATA SQL SECURITY INVOKER BEGIN DECLARE _TableName varchar(64); DECLARE _done INT DEFAULT FALSE; DECLARE database_cursor CURSOR FOR SELECT TABLE_NAME FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` = 'test' AND `TABLE_NAME` LIKE 'test%'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE; OPEN database_cursor; curs_loop: LOOP FETCH NEXT FROM database_cursor INTO _TableName; IF _done THEN LEAVE curs_loop; END IF; SET @Command := CONCAT( 'SELECT MIN(short_date) as FirstDuplicatedDate, MAX(short_date) as LastDuplicatedDate FROM ( SELECT short_date, type, value, count(*) as cnt FROM ', _TableName, ' WHERE type = "test" GROUP BY short_date, type, value HAVING COUNT(*) > 1 ) as Duplicates' ); SELECT _TableName, @command; -- Debugging (remove if it is clutter) PREPARE _sql FROM @command; EXECUTE _sql; DEALLOCATE PREPARE _sql; END LOOP; CLOSE database_cursor; END $$ DELIMITER ; CALL so42856538; Answers 2
right... you might want to try this.
remove this line and run the query again.
DECLARE @DB_Name varchar(50), @Command varchar(100); i think in mysql you can just use a variable without declaring it and then cast when necessary.
Answers 3
As far as my knowledge concern this error is due to ,DELIMETER and one more thing that you have to apply cursor properly to iterate whole rowset.Here in below code i had wrote down procedure with CURSOR.Make dynamic query and execute that dynamic query using
PREPARE stmt FROM @VAR_QRY; EXECUTE stmt; That dynamic query will returns exact output you want.Here i had assuming that you have basic knowledge about trigger,loop and Cursor
Try below code. Hope this will helps.
DROP PROCEDURE IF EXISTS ITERATEALLTABLE; DELIMITER $$ CREATE PROCEDURE ITERATEALLTABLE() BEGIN DECLARE VAR_TABLE varchar(100); DECLARE VAR_QRY varchar(100); DECLARE VAR_FINISHED INT(11) DEFAULT 0; DECLARE DATABASE_CURSOR CURSOR FOR SELECT name AS TableNames FROM ( SELECT TABLE_NAME as name FROM `information_schema`.`TABLES` WHERE `TABLES`.`TABLE_SCHEMA` = 'test' AND `TABLES`.`TABLE_NAME` LIKE 'test%' )Z ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET VAR_FINISHED = 1; OPEN DATABASE_CURSOR; GET_NEXTRECORD: LOOP FETCH DATABASE_CURSOR INTO VAR_TABLE; IF VAR_FINISHED = 1 THEN LEAVE GET_NEXTRECORD; END IF; SET @VAR_QRY = CONCAT("SELECT MIN(short_date) as FirstDuplicatedDate, MAX(short_date) as LastDuplicatedDate FROM (SELECT short_date, type, value, count(*) as cnt FROM " , VAR_TABLE , " WHERE type = 'test' GROUP BY short_date, type, value HAVING COUNT(*) > 1) as Duplicates"); PREPARE stmt FROM @VAR_QRY; EXECUTE stmt; END LOOP GET_NEXTRECORD; CLOSE DATABASE_CURSOR; END; CALL ITERATEALLTABLE; 
0 comments:
Post a Comment