{"id":1263,"date":"2015-10-30T13:48:58","date_gmt":"2015-10-30T18:48:58","guid":{"rendered":"http:\/\/osric.com\/chris\/accidental-developer\/?p=1263"},"modified":"2015-10-30T13:48:58","modified_gmt":"2015-10-30T18:48:58","slug":"ora-00904-wm_concat-invalid-identifier","status":"publish","type":"post","link":"https:\/\/osric.com\/chris\/accidental-developer\/2015\/10\/ora-00904-wm_concat-invalid-identifier\/","title":{"rendered":"ORA-00904: &#8220;WM_CONCAT&#8221;: invalid identifier"},"content":{"rendered":"<p>I&#8217;ve never run into <a href=\"http:\/\/psoug.org\/definition\/wm_concat.htm\">wm_concat<\/a> before when using Oracle, but I saw it in a statement similar to this earlier today:<\/p>\n<p><code>SELECT WM_CONCAT(id)<br \/>\nFROM chris.item<br \/>\nWHERE item_type LIKE '%floppy%';<\/code><\/p>\n<p>Of course, it didn&#8217;t work:<\/p>\n<p><code>ORA-00904: \"WM_CONCAT\": invalid identifier<\/code><\/p>\n<p>It looks like <code>WM_CONCAT<\/code> isn&#8217;t a supported command (<a href=\"https:\/\/community.oracle.com\/thread\/2333086\">Error when using wm_concat function<\/a>), and possibly the code I was debugging stopped working when the database was upgraded to 12c.<\/p>\n<p><code>WM_CONCAT<\/code> is intended to return the results as a comma-separated list. A strange idea, but it this particular case it almost made sense. A supported replacement that aggregates results into a single string is LISTAGG.<\/p>\n<p><code>SELECT LISTAGG(id, ',')<br \/>\nWITHIN GROUP (ORDER BY id)<br \/>\nFROM chris.item<br \/>\nWHERE item_type LIKE '%floppy%'<br \/>\nGROUP BY 1;<\/code><\/p>\n<p>In this case, I did not want the results separated into groups, so I used <code>GROUP BY 1<\/code> to put all results in the same group.<\/p>\n<p>There are a number of other <a href=\"https:\/\/oracle-base.com\/articles\/misc\/string-aggregation-techniques\">string aggregation techniques for Oracle<\/a>, if you ever find yourself with such a need.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;ve never run into wm_concat before when using Oracle, but I saw it in a statement similar to this earlier today: SELECT WM_CONCAT(id) FROM chris.item WHERE item_type LIKE &#8216;%floppy%&#8217;; Of course, it didn&#8217;t work: ORA-00904: &#8220;WM_CONCAT&#8221;: invalid identifier It looks like WM_CONCAT isn&#8217;t a supported command (Error when using wm_concat function), and possibly the code &hellip; <a href=\"https:\/\/osric.com\/chris\/accidental-developer\/2015\/10\/ora-00904-wm_concat-invalid-identifier\/\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">ORA-00904: &#8220;WM_CONCAT&#8221;: invalid identifier<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[282],"tags":[369,370,207,274],"class_list":["post-1263","post","type-post","status-publish","format-standard","hentry","category-oracle-2","tag-concat","tag-concatenate","tag-list","tag-oracle"],"_links":{"self":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts\/1263","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/comments?post=1263"}],"version-history":[{"count":4,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts\/1263\/revisions"}],"predecessor-version":[{"id":1270,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts\/1263\/revisions\/1270"}],"wp:attachment":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/media?parent=1263"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/categories?post=1263"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/tags?post=1263"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}