In this article, we will look at ways to use aggregate functions in table based Valueset even though when Oracle says that aggregate functions cannot be achieved in Valueset.
This workaround method might not work always. However in 90% of the cases it should work successfully.
If you are new to Valueset and it’s usage, please read through the below posts.
We cannot use the aggregate function in SELECT clause, however we can use the aggregate function in the FROM clause to achieve this requirement.
The FROM clause has a limitation of 240 characters, so you need to ensure that you write the grouping SQL within that limit and you can pass the input variable in the where clause.
For example consider the requirement of getting the manager direct reports count in extract. You already have the Manager ID and you want to get the count of employees reporting to him.
We can do it in the below way:
SELECT n.n
FROM
(SELECT TO_NUMBER( LEVEL) - 1 n FROM DUAL CONNECT BY LEVEL <= 1000000 ) n
WHERE
n.n = (SELECT count(DISTINCT sup.PERSON_ID) FROM PER_ASSIGNMENT_SUPERVISORS_F sup
WHERE sup.EFFECTIVE_END_DATE > SYSDATE
AND sup.MANAGER_ID = :{PARAMETER.P_MANAGER_ID})
In the from clause we are writing a query to get all numbers from 1 to 10lakh (could be changed as per your requirement to have less impact on performance) and then in the where clause we are joining it with the manager direct reports count. so even though we achieve our requirement, there would be a performance hit if the employee count is huge. You can use this approach to get the count, sum, min, max functions, make sure that you round the numbers and join to the n.n in where clause.
Table of Contents
Alternative approach:
In this approach we are going to put the entire SQL in the FROM clause along with the field which we are trying to pass as input parameter and then we will write a where condition to join the input parameter.
SELECT
SUP.COUNT
FROM
(SELECT count(distinct PERSON_ID) COUNT, MANAGER_ID from PER_ASSIGNMENT_SUPERVISORS_F where EFFECTIVE_END_DATE > SYSDATE group by MANAGER_ID) SUP
WHERE
SUP.manager_id= :{PARAMETER.P_MANAGER_ID}
This approach has better performance than the earlier approach. However as the FROM clause length is 240, it might not be possible to fit in the entire SQL in the FROM clause in most of the cases and hence we have to go with the first approach in 80% of the cases.
Another sample to get the sum of salary for an employee:
SELECT
n.cs
FROM
(select sum(salary_amount) cs,person_id from cmp_salary where trunc(sysdate) BETWEEN date_from AND NVL(date_to,trunc(sysdate)) group by person_id ) n
WHERE
n.person_id = (SELECT distinct person_id
FROM per_all_assignments_m paam
,fnd_lookup_values flv
,per_assignment_status_types_tl past
,per_person_types_tl ppt
WHERE paam.person_id= :{PARAMETER.P_PERSON_ID}
AND paam.assignment_type='E'
AND trunc(sysdate) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND paam.employment_category=flv.lookup_code(+)
AND flv.lookup_type(+)='EMP_CAT'
AND paam.assignment_status_type_id = past.assignment_status_type_id
AND NVL(past.source_lang,'US')='US'
AND paam.person_type_id=ppt.person_type_id(+)
AND NVL(ppt.source_lang,'US')='US'
AND UPPER(ppt.user_person_type) IN ('STAFF','FACULTY')
AND paam.employment_category IN ('PR','FR','Term Assignment')
AND paam.assignment_status_type = 'ACTIVE')
In both of the above cases we cannot use the input parameters in the FROM clause. We have to select the fields and/or counter in the FROM clause and add the input parameter criteria in the where clause.
Tip: You can use this approach to get aggregate functions in Valueset in 90% of the cases. However don’t try to increase the level beyond 10000000 (1crore) as it will throw an error of insufficient memory.
