Not a single-group group function

building semantic layer
1

Hi -

One of our DBA’s created a custom sql report for one of our customers and I have been tasked with the job of recreating the report using variables added to designer so as to get rid of the custom sql.

I have created an object for Max Survey Date Year and the sql is as follows:

SELECT
  to_char(Max(REPORTING_PERIODSurveyDate.FULL_DATE),'YYYY')
FROM
  PERIOD  REPORTING_PERIODSurveyDate,
  SURVEY_HEADER
WHERE
(SURVEY_HEADER.SURVEY_DATE_KEY=REPORTING_PERIODSurveyDate.PERIOD_KEY  )
  AND  (SURVEY_HEADER.SURVEY_DATE_KEY=REPORTING_PERIODSurveyDate.PERIOD_KEY  )

I am now trying to modify an existing Current Year Indicator object with the following:

decode(REPORTING_PERIODSurveyDate.CALENDAR_YEAR,@Select(Survey Date\Max Survey Date Year),'1','0')

This is resulting in an oracle error: Not a single-group group function. I realize that it has something to do with the max. Can anyone tell me if there’s another way to do this??

Thanks, Tammy

Tammy :us: (BOB member since 2002-11-12)

2

You need to have some thing in the group by clause of the query when you aggregate functions like sum ,max ,min

For example


select 
max(tb1.col1)
from 
tb1,
tb2
where 
tb1.col2 = tb2.col4
group by
tb1.col2

Is your Date --> Max Survey Date Year object is a measure object with max in it?[/code]

JaiGupta (BOB member since 2002-09-12)

3

There is a to_char on it, so it is a dimension.

After reading your post I modifed the Max Survey Date Year object to remove the to_char and make it a measure.

When I went back to the Current Year Indicator object and parsed it, this time I get a “missing right parenthesis” error…but the parens are correct?

Thanks, Tammy

Tammy :us: (BOB member since 2002-11-12)

4

Ok, I’m seeing now that I have to take about 20 steps back (I was hoping to modify just a couple objects but think it will require more than that) and ask another question.

The Survey_Header table has a Survey_Date_Key which is joined to a Date_Key on a generic date table that contains full dates, years, months, quarters, etc.

My goal is to determine the max year in that Survey_Header table using the Survey_Date_Key joined to the Date_Key on the generic date table.

I had tried using the previously mentioned Max Survey Year object, but I now see that it is not working the way it exists now.

Tammy :us: (BOB member since 2002-11-12)

5

You need to add an aggregate function on to calender year or get rid of the max on the other object. The problem has nothing to do with the to_char or it being a measure or dimension, your DB has no idea about measures or dimensions. Your decode has one aggregate object and one non aggregate object. It cant put anything in the group by since the decode contains an aggregate and it cant resolve the query since you dont have a group by.

If you use a case statement it is more clear of what is going on.

case when A then aggregate
                 else  dont aggregate
          end

Cant do that, DB cant aggregate 4 rows and then leave 2 rows not aggregated.

Scott Bowers :us: (BOB member since 2002-09-30)