Include aggregate functions in Table Type Value Sets

We have limitation with table type value sets, what is that limitation; we cannot create table type value set with SQL query having aggregate function.

What are aggregate functions? Count, Sum, Min, Max these all are aggregate functions.

Using this trick we can have more options, enhanced capabilities, broader scope in scenarios where delivered DBIs cannot meet certain requirement, and we have to look for an alternate to write SQL, including them into Table type value set, and ultimately consuming those value sets in FF, or Extracts.

Taking below example to demonstrate the steps –

Writing SQL which will return Employee’s child count less than 18 years

Step 1 - Build SQL query, validate it is giving correct output, snippet -

    select   COUNT(CONTACT_PERSON_ID)
 from   PER_PERSONS PP
 ,   PER_CONTACT_RELSHIPS_F PCR, PER_ALL_PEOPLE_F PAPF
 where
 PCR.CONTACT_PERSON_ID=PP.person_id
 and   PCR.person_id=papf.person_id
 and   PAPF.person_id=:{PARAMETER.PERSON_ID}
 and   PCR.contact_type='C'
 and   FLOOR(MONTHS_BETWEEN(SYSDATE, PP.DATE_OF_BIRTH) /12)<19

Step 2 - We will not be able to create table type value set for above query as it contains aggregate function COUNT. We will modify this query as below –

    select to_char(to_number(counter))
 from (select level counter from dual connect by   level <= 1000000)
 where counter =
 (
 select COUNT(CONTACT_PERSON_ID)
 from PER_PERSONS PP
 , PER_CONTACT_RELSHIPS_F PCR, PER_ALL_PEOPLE_F PAPF
 where 
 PCR.CONTACT_PERSON_ID=PP.person_id
 and PCR.person_id=papf.person_id
 and PAPF.person_id=:{PARAMETER.PERSON_ID}
 and PCR.contact_type='C'
 and FLOOR(MONTHS_BETWEEN(SYSDATE, PP.DATE_OF_BIRTH)   /12)<19
 )
    

Step 3 - Once the query is ready, navigate in application to setup and maintenance, search – Manage Value Sets.

Select Manage Value Sets task, and click on + icon to create new value set

Step 4 - Select Validation Type as Table to create Table based value set. Adjust validated SQL query in correct fields, Save and Close.

Value Set is ready to get called from Fast Formula and return value.

Step 5 - Below syntex to call value set from Fast Formula –

 L_Person_ID = to_char(GET_CONTEXT(PERSON_ID,0))
 l_total_child_per_emp = GET_VALUE_SET('XX_CHILD_COUNT_VS','|=PERSON_ID='''||L_Person_ID||'''')