{"id":73301,"date":"2012-07-13T15:28:34","date_gmt":"2012-07-13T15:28:34","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/fun-with-obiee-sql\/"},"modified":"2021-07-14T13:07:55","modified_gmt":"2021-07-14T13:07:55","slug":"fun-with-obiee-sql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/fun-with-obiee-sql\/","title":{"rendered":"Fun with OBIEE SQL"},"content":{"rendered":"<p>I posted a couple of tweets yesterday about this crazy query I had to work with. I won&#8217;t Storify you to death, so I&#8217;ll sum it up quickly. 1269 lines (formatted, of course). 13 WITH clauses. 8 base tables. <\/p>\n<p>The error that was occurring was an Oracle one: ORA-00937, &#8220;not a single-group function.&#8221; <\/p>\n<p>That&#8217;s interesting because OBIEE, the BI Server, generates\/builds the SQL based on the logical model you&#8217;ve created. It&#8217;s doing it wrong, in this instance. I&#8217;m willing to wager that it&#8217;s a bug, in one way or another. <\/p>\n<p>Now, if the logical model doesn&#8217;t support this particular combination of columns, grain, or whatever, it will tell me so immediately. It will usually tell me while working in the RPD, so I won&#8217;t even get this far (presentation layer). <\/p>\n<p>Anyway, I found the offending piece of SQL, right at the bottom in the SELECT statement. There were 2 analytic functions, MIN and SUM and no GROUP BY. So I took the non-analytic columns, created a GROUP BY and added them in. It worked. <\/p>\n<p>That didn&#8217;t solve my problem though. Sure, I could run it in SQL Developer, but that doesn&#8217;t do the end-user much good. You can read what else I tried on my blog: <a href=\"http:\/\/www.oraclenerd.com\/2012\/07\/fun-with-obiee-sql.html\" target=\"_blank\">ORACLENERD<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>I posted a couple of tweets yesterday about this crazy query I had to work with. I won&#8217;t Storify you to death, so I&#8217;ll sum it up quickly. 1269 lines (formatted, of course). 13 WITH clauses. 8 base tables. The error that was occurring was an Oracle one: ORA-00937, &#8220;not a single-group function.&#8221; That&#8217;s interesting because OBIEE, the BI Server,&hellip;<\/p>\n","protected":false},"author":316193,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[],"coauthors":[],"class_list":["post-73301","post","type-post","status-publish","format-standard","hentry","category-oracle-databases"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73301","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/316193"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73301"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73301\/revisions"}],"predecessor-version":[{"id":91753,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73301\/revisions\/91753"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73301"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73301"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73301"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73301"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}