{"id":1178,"date":"2023-06-27T06:57:54","date_gmt":"2023-06-27T06:57:54","guid":{"rendered":"https:\/\/craftcookcode.com\/?p=1178"},"modified":"2023-06-27T07:00:19","modified_gmt":"2023-06-27T07:00:19","slug":"sql-quick-and-dirty-comparing-result-sets","status":"publish","type":"post","link":"https:\/\/craftcookcode.com\/?p=1178","title":{"rendered":"SQL: Quick and dirty comparing result sets"},"content":{"rendered":"<p>When doing regression testing &#8211; I need to prove that the changes that I have done to two queries returned the same result sets.<\/p>\n<p>If result sets are on the same database then you can do a MINUS operator.&nbsp;<\/p>\n<p>If not &#8211; it can be a pain to shift result sets from database to database; especially if they are large.<\/p>\n<p>For quick and dirty testing; I like using the tsql function CHECKSUM and pl sql function ORA_HASH.&nbsp; It can give you some quick results to test whether a column of data has a <em>high probability<\/em> of been the same as another column.<\/p>\n<p>To test a result set; I have the following excel formulas saved in my &#8220;Handy formulas&#8221; notebook:<\/p>\n<style>\/*! elementor - v3.13.3 - 28-05-2023 *\/<br \/>\n.elementor-heading-title{padding:0;margin:0;line-height:1}.elementor-widget-heading .elementor-heading-title[class*=elementor-size-]>a{color:inherit;font-size:inherit;line-height:inherit}.elementor-widget-heading .elementor-heading-title.elementor-size-small{font-size:15px}.elementor-widget-heading .elementor-heading-title.elementor-size-medium{font-size:19px}.elementor-widget-heading .elementor-heading-title.elementor-size-large{font-size:29px}.elementor-widget-heading .elementor-heading-title.elementor-size-xl{font-size:39px}.elementor-widget-heading .elementor-heading-title.elementor-size-xxl{font-size:59px}<\/style>\n<h6>TSQL<\/h6>\n<pre>=\",SUM(CAST(CHECKSUM(\"&amp;A1&amp;\") AS BIGINT)) AS \"&amp;A1<\/pre>\n<h6>PL\/SQL<\/h6>\n<pre>=\",SUM(ORA_HASH(\"&amp;A1&amp;\")) AS \"&amp;A1<\/pre>\n<p>With a list of column name you can then make an checksum sql statement from Excel<\/p>\n<style>\/*! elementor - v3.13.3 - 28-05-2023 *\/<br \/>\n.elementor-widget-image{text-align:center}.elementor-widget-image a{display:inline-block}.elementor-widget-image a img[src$=\".svg\"]{width:48px}.elementor-widget-image img{vertical-align:middle;display:inline-block}<\/style>\n<p>\t\t\t\t\t\t\t\t\t\t\t\t<img loading=\"lazy\" decoding=\"async\" width=\"740\" height=\"311\" src=\"http:\/\/craftcookcode.com\/wp-content\/uploads\/2023\/06\/code_sql_checksum-1024x430.png\" alt=\"\" loading=\"lazy\" srcset=\"https:\/\/craftcookcode.com\/wp-content\/uploads\/2023\/06\/code_sql_checksum-1024x430.png 1024w, https:\/\/craftcookcode.com\/wp-content\/uploads\/2023\/06\/code_sql_checksum-300x126.png 300w, https:\/\/craftcookcode.com\/wp-content\/uploads\/2023\/06\/code_sql_checksum-768x323.png 768w, https:\/\/craftcookcode.com\/wp-content\/uploads\/2023\/06\/code_sql_checksum-1536x646.png 1536w, https:\/\/craftcookcode.com\/wp-content\/uploads\/2023\/06\/code_sql_checksum-2048x861.png 2048w\" sizes=\"auto, (max-width: 740px) 100vw, 740px\"><\/p>\n<p>Running this query on both result sets; differences can be found by seeing if there is a difference in the retuned values<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"740\" height=\"189\" src=\"http:\/\/craftcookcode.com\/wp-content\/uploads\/2023\/06\/code_sql_checksum_results-1024x261.png\" alt=\"\" loading=\"lazy\" srcset=\"https:\/\/craftcookcode.com\/wp-content\/uploads\/2023\/06\/code_sql_checksum_results-1024x261.png 1024w, https:\/\/craftcookcode.com\/wp-content\/uploads\/2023\/06\/code_sql_checksum_results-300x77.png 300w, https:\/\/craftcookcode.com\/wp-content\/uploads\/2023\/06\/code_sql_checksum_results-768x196.png 768w, https:\/\/craftcookcode.com\/wp-content\/uploads\/2023\/06\/code_sql_checksum_results-1536x392.png 1536w, https:\/\/craftcookcode.com\/wp-content\/uploads\/2023\/06\/code_sql_checksum_results.png 1673w\" sizes=\"auto, (max-width: 740px) 100vw, 740px\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>When doing regression testing &#8211; I need to prove that the changes that I have done to two queries returned the same result sets. If result&#46;&#46;&#46;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[17],"tags":[53],"class_list":["post-1178","post","type-post","status-publish","format-standard","hentry","category-sql","tag-sql"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1178","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1178"}],"version-history":[{"count":5,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1178\/revisions"}],"predecessor-version":[{"id":1185,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1178\/revisions\/1185"}],"wp:attachment":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1178"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1178"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1178"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}