{"id":153,"date":"2015-04-21T13:00:05","date_gmt":"2015-04-21T17:00:05","guid":{"rendered":"http:\/\/salzlechner.com\/dev\/?p=153"},"modified":"2016-03-11T07:08:35","modified_gmt":"2016-03-11T12:08:35","slug":"sql-with-clause","status":"publish","type":"post","link":"http:\/\/salzlechner.com\/dev\/2015\/04\/21\/sql-with-clause\/","title":{"rendered":"SQL WITH Clause"},"content":{"rendered":"<p>Simple WITH clause<\/p>\n<p>WITH AS (subquery)<br \/>\nSELECT FROM [,table]<\/p>\n<p>WITH clause with multiple subqueries<\/p>\n<p>WITH AS (subquery)<br \/>\n, AS (subquery)<br \/>\nSELECT FROM , [, table]<br \/>\n[ WHERE ]<\/p>\n<p>for example find duplicate phone number rows in customer<\/p>\n<pre class=\"lang:tsql decode:true \">;WITH dupes AS \r\n (\r\n  SELECT DAY_PHONE\r\n  FROM cust\r\n  GROUP BY DAY_PHONE\r\n  HAVING COUNT(*) &gt; 1\r\n)\r\n\r\nSELECT c.CODE, c.org_name, c.last_name, c.first_name, c.e_mail, c.street, c.street_city, CONVERT(date, c.last) as LAST, c.DAY_PHONE\r\n  FROM dupes \r\n  INNER JOIN dbo.CUST AS c\r\n  ON dupes.DAY_PHONE = c.day_phone\r\n\r\n  order by dupes.DAY_PHONE\r\n<\/pre>\n<p>now more complex. the following statement converts the DAY_PHONE field to only numbers and only the first 10 digits and then checks for duplicate records<\/p>\n<p>OnlyNumbers is a stored procedure that\u00a0returns only the numbers from a string<\/p>\n<pre class=\"lang:tsql decode:true \">;WITH custs AS \r\n(\r\n  SELECT left(dbo.OnlyNumbers(day_phone),10) as PHONE\r\n  FROM dbo.CUST\r\n)\r\n, dupes AS \r\n (\r\n  SELECT PHONE\r\n  FROM custs\r\n  GROUP BY PHONE\r\n  HAVING COUNT(*) &gt; 1\r\n)\r\n\r\n\r\nSELECT c.CODE, c.org_name, c.last_name, c.first_name, c.e_mail, c.street, c.street_city, CONVERT(date, c.last) as LAST, c.DAY_PHONE, dupes.PHONE\r\n  FROM dupes \r\n  INNER JOIN dbo.CUST AS c\r\n  ON dupes.PHONE = left(dbo.OnlyNumbers(c.day_phone),10)\r\n  where c.cust_grp_num &lt; 2\r\n  and c.shiponly = ''\r\n\r\n  order by dupes.phone\r\n  \r\n<\/pre>\n<p>next same as above but check multiple fields for duplicates<\/p>\n<pre class=\"lang:default decode:true \">-- first preselect customers\r\n-- then get duplicate rows only\r\n-- then show all rows with these dupe values\r\n;WITH custs AS \r\n(\r\n  SELECT left(dbo.OnlyNumbers(day_phone),10) as PHONE, LAST_NAME, ORG_NAME\r\n  FROM dbo.CUST\r\n  where cust.cust_grp_num &lt; 2 and cust.shiponly = ''\r\n)\r\n, dupes AS \r\n (\r\n  SELECT PHONE, LAST_NAME , ORG_NAME\r\n  FROM custs\r\n  GROUP BY PHONE, LAST_NAME, ORG_NAME\r\n  HAVING COUNT(*) &gt; 1\r\n)\r\n\r\n\r\nSELECT c.CODE, c.org_name, c.last_name, c.first_name, c.e_mail, c.street, c.street_city, CONVERT(date, c.last) as LAST, c.DAY_PHONE, dupes.PHONE\r\n  FROM dupes \r\n  INNER JOIN dbo.CUST AS c\r\n  ON dupes.PHONE = left(dbo.OnlyNumbers(c.day_phone),10)\r\n  and dupes.LAST_NAME = c.last_name\r\n  and dupes.ORG_NAME = c.ORG_NAME\r\n\r\n  order by dupes.phone\r\n<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A quick overview of the SQL WITH clause<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","ngg_post_thumbnail":0,"footnotes":""},"categories":[24],"tags":[],"class_list":["post-153","post","type-post","status-publish","format-standard","hentry","category-sql"],"_links":{"self":[{"href":"http:\/\/salzlechner.com\/dev\/wp-json\/wp\/v2\/posts\/153","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/salzlechner.com\/dev\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/salzlechner.com\/dev\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/salzlechner.com\/dev\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/salzlechner.com\/dev\/wp-json\/wp\/v2\/comments?post=153"}],"version-history":[{"count":4,"href":"http:\/\/salzlechner.com\/dev\/wp-json\/wp\/v2\/posts\/153\/revisions"}],"predecessor-version":[{"id":280,"href":"http:\/\/salzlechner.com\/dev\/wp-json\/wp\/v2\/posts\/153\/revisions\/280"}],"wp:attachment":[{"href":"http:\/\/salzlechner.com\/dev\/wp-json\/wp\/v2\/media?parent=153"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/salzlechner.com\/dev\/wp-json\/wp\/v2\/categories?post=153"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/salzlechner.com\/dev\/wp-json\/wp\/v2\/tags?post=153"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}