Derived Field error

[christimess]

I have created a derived field in which I am adding/subtracting 6 different fields. Then in another derived field I need to take the results from the first derived field and multiply the result by the employee's hourly rate. When I try to create this I am getting an error message "ORA-00037: Not a Single-group group function". I am told that my problem is that there is to much adding/subtracting/multiplication going on and the system can't handle it. I am also being told that I can create a macro and that would solve my problem. However, I can not get the macro to work either. Does anyone have any suggestions?
Thanks for your help!

 

[CharlesCook]

This message has to do with database grouping and summary functions. Please post both derived fields.

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[christimess]

This is the how I arrive at my vacation balance (this derived field works):
( SUM(DISTINCT DECODE("ES2"."PLAN_TYPE",'51',"ES2"."HRS_CARRYOVER",'0'))+
( SUM(DISTINCT DECODE("ES2"."PLAN_TYPE",'51',"ES2"."HRS_ADJUST_YTD",'0'))+
( SUM(DISTINCT DECODE("ES2"."PLAN_TYPE",'51',"ES2"."HRS_EARNED_YTD",'0'))-
( SUM(DISTINCT DECODE("ES2"."PLAN_TYPE",'51',"ES2"."HRS_TAKEN_YTD",'0'))-
( SUM(DISTINCT DECODE("ES2"."PLAN_TYPE",'51',"ES2"."HRS_TAKEN_UNPROC",'0'))+
( SUM(DISTINCT DECODE("ES2"."PLAN_TYPE",'51',"ES2"."HRS_ADJUST_UNPROC",'0')))

This is the derived field where I am trying to take the above derived field and times it by the employee's hourly wage (where the error occurs):
(TO_CHAR((SUM(DISTINCT DECODE("ES2"."PLAN_TYPE",'51',"ES2"."HRS_CARRYOVER",'0'))+
( SUM(DISTINCT DECODE("ES2"."PLAN_TYPE",'51',"ES2"."HRS_ADJUST_YTD",'0'))+
( SUM(DISTINCT DECODE("ES2"."PLAN_TYPE",'51',"ES2"."HRS_EARNED_YTD",'0'))-
( SUM(DISTINCT DECODE("ES2"."PLAN_TYPE",'51',"ES2"."HRS_TAKEN_YTD",'0'))-
( SUM(DISTINCT DECODE("ES2"."PLAN_TYPE",'51',"ES2"."HRS_TAKEN_UNPROC",'0'))+
( SUM(DISTINCT DECODE("ES2"."PLAN_TYPE",'51',"ES2"."HRS_ADJUST_UNPROC",'0')))*
("J"."HOURLY_RT"))

Thanks!!!

 

[dlblack]

We are running version 4.2.2,Oracle, and ADP.

I was able produce the same error using a similiar set of derived fields:
testsum (adds earning codes)
SUM(DISTINCT DECODE("REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSCD",'51',"REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSAMT" ,'0'))
+ SUM(DISTINCT DECODE("REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSCD",'55',"REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSAMT" ,'0'))
+ SUM(DISTINCT DECODE("REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSCD",'56',"REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSAMT" ,'0'))
+ SUM(DISTINCT DECODE("REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSCD",'S',"REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSAMT" ,'0'))

answer1 (testsum * hrlyrate)

TO_CHAR(( SUM(DISTINCT DECODE("REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSCD",'51',"REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSAMT" ,'0'))
+ SUM(DISTINCT DECODE("REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSCD",'55',"REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSAMT" ,'0'))
+ SUM(DISTINCT DECODE("REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSCD",'56',"REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSAMT" ,'0'))
+ SUM(DISTINCT DECODE("REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSCD",'S',"REPORTS"."V_CHK_VW_EARNINGS"."CHECKVIEWEARNSAMT" ,'0')))* "REPORTS"."V_EMPLOYEE"."RATE1AMT")

I don't believe the TO_CHAR is necessary but it didn't make any difference.

When you test answer1 in the derived field box I get the error. Make sure that the rate field is highlighted in database grouping and the box is checked.

I found that eventhough RS complains, click 'ok' on the error and the derived field. The report should execute correctly, if the rest of the code is correct.

Hope this is of some help.





David Black, CSQA
Sr. Systems Analyst
Teepak, LLC