Error - not a single-group group function

building semantic layer
1

HI,

My measure definition needs to be as follows…

Decode (FLAG,‘Y’, SUM(amt), amt)

or

CASE FLAG
WHEN ‘Y’ then Sum(Amt)
else Amt
END

When I try placing these definitions in the object I get an error called “ORA-00937: not a single-group group function”

On Oracle the following query runs OK

Select Decode(FLAG,‘Y’,SUM(Amt), Amt)
FROM table
Group by FLAG,Amt;

I understand that both the FLAG and Amt need to be in the group by clause, but generally BO applies the group by on it’s own.

I need a work around to it.

Thanks in advance, :smiley:
Prateek

Anonymous (BOB member since 2002-06-06)

2

Neither of these is valid SQL.

A DECODE or CASE statement works only on a single row of the database. What would you be summing in this case.

What are you trying to solve? It looks like you’re trying to solve the problem of additive and non additive measures in a single object.

I think you’re going to need multiple data providers. What you’re trying to do right now simply will not work.

Steve Krandel :us: (BOB member since 2002-06-25)

3

THis is what is happening

I have some suppressed transactions which I do not want to include in my aggregation but the requirement is to display them

For example
MONTH----ACCT_KEY–FLAG—AMT
Jan ------------1---------Y--------10
Feb ------------1---------Y--------12
Feb ------------1---------N--------12

Thus If I just do a SUM(Amt) I will count the Amt twice in the month of Feb. Thus my total amt for Jan+Feb would be 34 which is wrong.

What I need is as follows…

I need to total amount as 22 and I need the Amt 12 to appear on another row in my record set in BO…

How can I get this functionality in one single object definition.

Anonymous (BOB member since 2002-06-06)

4

2 options:
Do this at the report level. It’s not possible at the universe level. Universes generate SQL. This is something SQL simply can’t do.

You’ll need a formula at the footer of the report. Something like:

=Sum(if = ‘N’ then 0 else )

2nd Option
Build a 2nd object at the universe level for the summing part.

sum(Decode (FLAG,‘Y’, amt, 0)

Use this object just for totalling purposes. Use the regular amt object, sum(amt), for the detail display.

Steve Krandel :us: (BOB member since 2002-06-25)

5

How about something like this

Decode(FLAG,‘Y’,SUM(amt),Sum(amt))

This will sum up my Y’s seperately and sum up my N’s seperately

Also, i won’t get a single-group error as only the FLAG will be needed in the group by clause…

This seems to work!!

Your thoughts on this…

Anonymous (BOB member since 2002-06-06)

6

No, it won’t do that. That is the same as:
If flag=y then sum(amt) else sum(amt). It does absolutely nothing.

You need to objects.

Decode(flag, ‘Y’, amt, 0) - This will sum the Ys

I prefer to have the sum around the whole statement. It just doesn’t work will inside the decode.

Steve Krandel :us: (BOB member since 2002-06-25)

7

This is the query I ran on Oracle

Select Acct_key,DECODE(FLAG,‘Y’,SUM(amt),SUm(amt))
from AA
group by acct_key,FLAG

Table AA contains data as I have mentioned in one of the posts above.

The result I obtained was as follows…

ACCT_KEY ------------- AMT
1 ------------------- 22 – This is the SUm of Y’s
1 ------------------- 12 – This is the Duplicate transaction for N

So the Decode seperates out the Y’s and N’s and sum’s them up individually…

Anonymous (BOB member since 2002-06-06)

8

Steve is right: you don’t need your decode. As you want to sum up your amt in any case you don’t need to put a decode.

I think just:

should be enough as you just want to seperate by flag.

Grodan :fr: (BOB member since 2004-12-29)

9

But how do I get BO to apply a group by on the FLAG even though I am not selecting it in the query!!

Anonymous (BOB member since 2002-06-06)

10

Select your FLAG object into your query results, then insert it into your report and in the table properties select to hide the FLAG column. Then you will still have your report splitted by flag without seeing it

Grodan :fr: (BOB member since 2004-12-29)

11

I know that – and that’s fine!!

All this is cool if I am making the reports. I cannot tell the business users to always use the FLAG object and then hide it to get correct results.

Anonymous (BOB member since 2002-06-06)

12

BO won’t generate this, will it?

I think you’re out of luck. You need 2 objects: Y’s and N’s.

You’re making 1 of 2 mistakes here. You’re either assuming your users are stupid and won’t understand this. Or, you’re assuming your users will write adhoc queries anyway.

You could be making both mistakes.

Steve Krandel :us: (BOB member since 2002-06-25)

13

you need this syntax for universe object SQL Select:

sum(
case
when flag=‘X’ then amt1
when flag=‘Y’ then amt2
else null
end)

HTH

ottoman

Ottoman :uk: (BOB member since 2002-10-04)