Function in Oracle

don't open a cursor to only read one result.

if it's possible your cursor will return more than one row, your current incarnation will effectively act as a random row selector because only the results of the last row will be returned but you have no constraints on which row will be last.

Looks like you had an order by originally, that will address the problem of which row will be last; but you commented it out.
Since you only return the last row, there is no point in processing all of the other rows, so the easiest solution is to simply sort in reverse order and pick the FIRST row.

Note, your original code doesn't have a condition for 'SRA - TPA Auditor'  so the function will return NULL in that case.
This code below does the same but without all the IFs and unnecessary looping.  Using this method you can also eliminate the need to use a DISTINCT.


CREATE OR REPLACE FUNCTION get_role_name(p_user_name VARCHAR2)
    RETURN VARCHAR2
IS
    l_result VARCHAR2(4000);
BEGIN
    SELECT CASE
               WHEN sn = 'SRA - Facility Contact' THEN 'Facility Contact'
               WHEN sn = 'SRA - Lead' OR sn = 'SRA - Verification Lead' THEN 'Lead'
               WHEN sn = 'SRA - Verification Lead' THEN 'Verification Lead'
           END
      INTO l_result
      FROM (  SELECT sat.activity_short_name AS sn
                FROM si_users_t u,
                     si_user_org_roles uor,
                     si_org_roles sor,
                     si_roles_t r,
                     si_role_activities sra,
                     si_activities_t sat
               WHERE u.user_id = uor.user_id
                 AND uor.org_role_id = sor.org_role_id
                 AND sor.role_id = r.role_id
                 AND sra.role_id = r.role_id
                 AND sat.activity_id = sra.activity_id
                 AND sat.activity_short_name IN ('SRA - Auditor',
                                                 'SRA - TPA Auditor',
                                                 'SRA - Lead',
                                                 'SRA - Verification Lead',
                                                 'SRA - Facility Contact')
                 AND u.user_name = p_user_name
            ORDER BY sat.activity_short_name DESC)
     WHERE ROWNUM = 1;

    RETURN l_result;
END;

Actually when we send one username it can return multiple rows like any activity in my IN clause within the select query.

So one user can have multiple activities but I want to display with certain logic like my function should return only one activity as output  :

1) If the result of the SQL returns activities along with 'SRA - Facility Contact' then return only one result i.e. Facility Contact.

2) If the SQL returns activities  'SRA - Lead' OR sn = 'SRA - Verification Lead'  but not having 'SRA - Facility Contact' then return  ''Lead''

3) If it returns  'SRA - Verification Lead' but not  'SRA - Lead' or 'SRA - Facility Contact' then return 'Verification Lead'

Functions by definition return exactly one item.  They cannot return multiple items.  You could however concatenate multiple, comma-separated values inside a function into a single, large variable, then return that one variable.  Or, depending on which program/application you call this function from you may be able to change the function to return a ref cursor, or an array, then pass one of these objects back to your calling program as the "return" value from this function.  Either of those objects could actually contain multiple values.

you need to do collection membership not single value compares

also note your rules conflict with each other


if you have two rows...

 'SRA - Verification Lead'   and  'SRA - Lead'  then rule 2 says you'll return 'Lead' so you'll never get to rule 3 which says you shouldn't

what are the proper rules for 2 and 3 to work together ?




and, you still don't have any rules for your Auditor names,  so the function will return NULL  is that what you expect?

Ok ... there are other rules for an example I shared 3 of them ..

If we make the In clause to the below:

                                                ('SRA - Lead',
                                                 'SRA - Verification Lead',
                                                 'SRA - Facility Contact')

Then no matters what i have to go with if rule 1 pass then show it . and if rule 1 fail then go to rule 2 .. similarly ..

you misunderstood

rule 2 in my example doesn't fail, it succeeds

but rule 3 has a contradictory rule, so it doesn't make sense. It's not that rule 3 will fail, it simply doesn't pertain to a logical condition

In other words,   rule 3 will NEVER apply to anything, you could simply delete it and it would have no effect on your results

no, an "IN clause" is not what you're looking for
MEMBER OF "a collection" is what you want

I suppose you could do it with multiple queries and sub queries on  IN and NOT IN conditions but that's a lot more complicated.

I have the function written already except for a consistent set of rules.

Just post those and I can put them into the function and post it.

Here is the logic:

The below are the activities which one user can have one or multiple:

'SRA - Auditor',
'SRA - TPA Auditor',
'SRA - Lead',
'SRA - Verification Lead',
'SRA - Facility Contact'

Here are the rules which need to be done sequentially :

--1) Check if the user has SRA - Facility Contact activity then display 'Facility Contact'

-- 2) If the user have 'SRA - Verification Lead'  or 'SRA - Lead'  but should not have 'Facility Contact'  then display 'Lead'

-- 3) If the user have ''SRA - Verification Lead'   but  not having 'SRA - Lead'   or  SRA - Facility Contact  then display 'Verification Lead'

--4) if the user having SRA - Auditor but not FC,VL,L, TPA Auditor ..  activity then go to visit_setup table and check if the user present in onsite_lead column for status with 'active' then display 'Onsite lead' 

--5) if 4th rule fails then display 'Auditor'

--6) if the user having 'SRA - TPA Auditor' but not FC,VL,L,'SRA - Auditor ' activity then go to visit_setup table and check if the user present in onsite_lead column for status with 'active' then display 'Onsite lead' 

--7) if 6th rule fails then display ''Auditor''

Select allOpen in new window

thanks a lot ...