ORA 00937 not a single-group group function error

building semantic layer
1

Hi All

I am getting the ORA 00937 ERROR. The objects SQl i am using in the Universe is

Case when table_name.object_name = ‘x’ then
SUM(fact1.measure1)*count(distinct mnth_key)/sum(fact2.measure2) end

i am breaking my head to slove this. please help me in this regard.

Thanks
Raki

Raki123 (BOB member since 2007-05-15)

2

Sum has to be outside of the case statement, not inside it.

Dave Rathbun :us: (BOB member since 2002-06-06)

3

It looks that you want to use in the new object measures from 2 fact tables since I see fact1 and fact2 table names in the formula. So even if you wrap up the CASE statement with SUM(), you will probably not get what you need. But it depends on your data model. Try it and see.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

4

I agree with Marek, on the surface of it, it looks like something you will have to resolve at the report level…

Mak 1 :uk: (BOB member since 2005-01-06)

5

Hi Mak/Marek

Thanks for the reply.

But can I achieve the same with the help of views/derived tables in the Universe?

Thanks
Raki

Raki123 (BOB member since 2007-05-15)

6

Hi,

Normally, you don’t make a join between 2 fact tables - not in an universe, not in a derived table.

If some information (a measure) is in a fact table A and not in a fact table B where you need it for a calculation then probably the analysis and design of the fact table B was not done properly. Consider a redesign of the fact table B where the missing measure would be included into the table.

Marek Chladny :slovakia: (BOB member since 2003-11-27)