Not a GROUP BY expression error when using a function in a query

oracle

I use a function in a query to select data but I have this error:

not a GROUP BY expression

This is the query:

SELECT          PROF_EMPLOYEE_DATA.EMP_CODE, PROF_EMPLOYEE_DATA.EMP_NAME_AR, PROF_TREE_DETAILS.TREE_NAME, PROF_JOBS_DETAILS.JOB_NAME, PROF_EMPLOYEE_DATA.GROUP_START_DATE, 
                         COUNT(DISTINCT ATT_EMPLOYEEATTENDANCE.ATTEND_DATE) AS WORKSDAYS
                         ,nvl(getEmployee_OffDays ('" + dtp_PrintManth.Text + "',PROF_EMPLOYEE_DATA.SHIFT_ID),0) - GETEMPLOYEE_OFFDAYSWork(extract(month from to_date('" + dtp_PrintManth.Text + "','dd/mm/yyyy')),extract(year from to_date('" + dtp_PrintManth.Text + "','dd/mm/yyyy')),PROF_EMPLOYEE_DATA.EMPLOYEE_ID)as OffDays 
FROM            PROF_EMPLOYEE_DATA INNER JOIN
                         PROF_TREE_DETAILS ON PROF_EMPLOYEE_DATA.TREE_ID = PROF_TREE_DETAILS.TREE_ID INNER JOIN
                         PROF_JOBS_DETAILS ON PROF_EMPLOYEE_DATA.JOB_ID = PROF_JOBS_DETAILS.JOB_ID LEFT OUTER JOIN
                         ATT_EMPLOYEEATTENDANCE ON PROF_EMPLOYEE_DATA.EMPLOYEE_ID = ATT_EMPLOYEEATTENDANCE.EMPLOYEE_ID
WHERE        (PROF_EMPLOYEE_DATA.EMP_CODE = '12411') AND (ATT_EMPLOYEEATTENDANCE.ATTEND_DATE BETWEEN TO_DATE('10/1/2016', 'MM/DD/YYYY') AND TO_DATE('10/31/2016', 'MM/DD/YYYY'))
GROUP BY PROF_EMPLOYEE_DATA.EMP_CODE, PROF_EMPLOYEE_DATA.EMP_NAME_AR, PROF_TREE_DETAILS.TREE_NAME, PROF_JOBS_DETAILS.JOB_NAME, PROF_EMPLOYEE_DATA.GROUP_START_DATE

And this is the function that I created to display days number in month:

create or replace
FUNCTION "GETEMPLOYEE_OFFDAYS" (month_p nvarchar2,shift_id_p number)
  RETURN decimal 
  IS VALUE_P decimal;
    VALUE1_P decimal;
   VALUE2_P decimal;
    i   NUMBER := 0;
BEGIN

for RC in 
(SELECT LOK_DAYS.*
FROM ATT_SHIFTS
RIGHT JOIN ATT_SHIFTSDAYSOFF
ON ATT_SHIFTS.SHIFT_ID = ATT_SHIFTSDAYSOFF.SHIFT_ID
LEFT JOIN LOK_DAYS
ON LOK_DAYS.DAY_ID        = ATT_SHIFTSDAYSOFF.DAY_ID
WHERE ATT_SHIFTS.SHIFT_ID = shift_id_p 
)
loop
i := i + 1;
IF i =1 THEN
      select
 count(to_char(to_date(month_p,'dd/mm/yyyy')-to_char(to_date(month_p,'dd/mm/yyyy'),'dd')+level,'fmDay')) into value_p
         from dual
         where to_char(to_date(month_p,'dd/mm/yyyy')-to_char(to_date(month_p,'dd/mm/yyyy'),'dd')+level,'fmDAY')=upper(rc.DAY_NAME_AR)
         OR to_char(to_date(month_p,'dd/mm/yyyy')-to_char(to_date(month_p,'dd/mm/yyyy'),'dd')+level,'fmDAY')=upper(rc.DAY_NAME_EN)
         connect by level<=to_char(last_day(to_date(month_p,'dd/mm/yyyy')),'dd')  ;

         else if i=2 then
         select
 count(to_char(to_date(month_p,'dd/mm/yyyy')-to_char(to_date(month_p,'dd/mm/yyyy'),'dd')+level,'fmDay')) into value1_p
         from dual
         where to_char(to_date(month_p,'dd/mm/yyyy')-to_char(to_date(month_p,'dd/mm/yyyy'),'dd')+level,'fmDAY')=upper(rc.DAY_NAME_AR)
         OR to_char(to_date(month_p,'dd/mm/yyyy')-to_char(to_date(month_p,'dd/mm/yyyy'),'dd')+level,'fmDAY')=upper(rc.DAY_NAME_EN)
         connect by level<=to_char(last_day(to_date(month_p,'dd/mm/yyyy')),'dd')  ;

    END IF;
     END IF;
END LOOP;
RETURN nvl(VALUE_P,0)+nvl(value1_p,0);
END;

Best Answer

You can not just use any function in the SELECT list when you use GROUP BY.

You need to use an aggregate function (MIN, MAX, SUM, etc) on columns not included in the GROUP BY list, or include the columns used by a regular function in the GROUP BY list.

You use PROF_EMPLOYEE_DATA.SHIFT_ID and PROF_EMPLOYEE_DATA.EMPLOYEE_ID with your function, but they are not included in the GROUP BY list.

Including those columns in the GROUP BY list should solve this, but that may not provide the result you want.

Another way is to write your own aggregate function:

Using User-Defined Aggregate Functions

Related Question