I posted a couple of tweets yesterday about this crazy query I had to work with. I won’t Storify you to death, so I’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, “not a single-group function.”

That’s interesting because OBIEE, the BI Server, generates/builds the SQL based on the logical model you’ve created. It’s doing it wrong, in this instance. I’m willing to wager that it’s a bug, in one way or another.

Now, if the logical model doesn’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’t even get this far (presentation layer).

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.

That didn’t solve my problem though. Sure, I could run it in SQL Developer, but that doesn’t do the end-user much good. You can read what else I tried on my blog: ORACLENERD