14,953
edits
SQL syntax debug: Difference between revisions
Jump to navigation
Jump to search
no edit summary
No edit summary |
|||
| Line 19: | Line 19: | ||
* Using the text editor [http://www.sublimetext.com/ Sublime], and enable the Menu option: {{ kbd | key = View > Syntax >SQL }}. The syntax will become colorful for easy to read! | * Using the text editor [http://www.sublimetext.com/ Sublime], and enable the Menu option: {{ kbd | key = View > Syntax >SQL }}. The syntax will become colorful for easy to read! | ||
* [http://www.dpriver.com/pp/sqlformat.htm Instant SQL Formatter] | * [http://www.dpriver.com/pp/sqlformat.htm Instant SQL Formatter] | ||
== Approach 4: Verify the row counts between source table with parsed table == | |||
<pre> | |||
SELECT @timer := CURRENT_TIMESTAMP(); | |||
SET @cnt_error := 0; | |||
SET @message := ''; | |||
-- un-expected condition | |||
SET @cnt_source := 31; | |||
SET @cnt_target := 25; | |||
-- SELECT @cnt_source <> @cnt_target; | |||
SELECT IF(@cnt_source <> @cnt_target, @cnt_error := @cnt_error + 1, ''); | |||
SELECT IF(@cnt_source <> @cnt_target, @message := CONCAT(@message, 'some problem!', '\r'), ''); | |||
-- expected condition | |||
SET @cnt_source := 11; | |||
SET @cnt_target := 11; | |||
SELECT IF(@cnt_source <> @cnt_target, @cnt_error := @cnt_error + 1, ''); | |||
SELECT IF(@cnt_source <> @cnt_target, @message := CONCAT(@message, 'some problem!', '\r'), ''); | |||
-- real data | |||
SET @cnt_source := (SELECT COUNT(*) FROM `source_table`); | |||
SET @cnt_target := (SELECT COUNT(*) FROM `parsed_table`); | |||
-- SELECT @cnt_source <> @cnt_target; | |||
SELECT IF(@cnt_source <> @cnt_target, @cnt_error := @cnt_error + 1, ''); | |||
SELECT IF(@cnt_source <> @cnt_target, @message := CONCAT(@message, 'some problem!', '\r'), ''); | |||
SELECT CURRENT_TIMESTAMP() AS 'end time', @timer AS 'start time', TIMEDIFF(CURRENT_TIMESTAMP(), @timer) AS 'time elapsed'; | |||
SELECT @cnt_error; | |||
SELECT @message; | |||
</pre> | |||
== Related articles == | == Related articles == | ||