[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Function in Oracle

Posted on 2013-11-07
12
Medium Priority
?
549 Views
Last Modified: 2013-11-07
Can the below function optimized ?

CREATE OR REPLACE FUNCTION GET_ROLE_NAME(P_USER_NAME VARCHAR2)
RETURN VARCHAR2
IS
V_ROLE_NAME VARCHAR2(80);
L_RESULT VARCHAR2(4000);
CURSOR CUR_ROLE_NAME IS
select 
 distinct  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);
BEGIN
FOR I IN CUR_ROLE_NAME
LOOP
IF I.SN='SRA - Facility Contact' THEN
       L_RESULT:='Facility Contact';
       ELSE 
         IF I.SN='SRA - Lead'or i.sn='SRA - Verification Lead' 
         THEN
         L_RESULT:='Lead';
         ELSE
          IF I.SN='SRA - Verification Lead'AND 
          I.SN!='Facility Contact' AND I.SN!='SRA - Lead'
          THEN
          L_RESULT:='Verification Lead';
         
         END IF;
        END IF;
        END IF;
         END LOOP;
         RETURN L_RESULT;
         END;

Open in new window


Code to run like :

SQL> SELECT GET_ROLE_NAME('<<user name >>') FROM DUAL;
0
Comment
Question by:Swadhin Ray
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
12 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39631102
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.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39631128
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;
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39631225
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'
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39631327
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.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39631397
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?
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39631431
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 ..
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39631452
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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39631468
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.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39631498
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.
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39631684
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''

Open in new window

0
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 39631782
look at your rules 2 and 3, they don't make sense

First,  you have no value "Facility Contact"  so  the "should not have" condition for rule 2 doesn't apply.
Let's assume that's a mistake and it should have said "SRA - Facility Contact"


Let's go super simple.

user has exactly one row

"SRA - Verification Lead"

Rule 1 fails
Rule 2 succeeds  - done
Rule 3 (or others) are never checked.


Now, let's go more complicated

User has 2 rows

"SRA - Verification Lead"
"SRA - Facility Contact"

Rule 1 succeeds - done
Rule 2 (or others) never gets checked


User has 2 rows

"SRA - Verification Lead"
"SRA - Lead"

Rule 1 fails
Rule 2 succeeds - done
Rule 3 (or others)  never gets checked


Rule 3 has no conditions where it could succeed.
Either it won't be reached, or it will be reached and fail.

Lets' break down rule 3

The first condition:   If the user have ''SRA - Verification Lead'   if that's true then rule 2 applies first,
The second condition:  but  not having 'SRA - Lead' - this might as well be deleted.  If it's true or not true is irrelevant, because either way Rule 2 would apply first
The third condition: not having  "SRA - Facility Contact" - this is the same as rule 2, so if it's true, then Rule 2 would have already caught it, if it's not true, then rule 3 will fail for the same reason as rule 2.  Also this condition isn't needed in rule 2 or 3 because it's implicit in the fact that you got past rule 1

Also note,  Rules 6 and 7 are never going to be used either because rule 5 is a "catch all".
If you make it all the way to 4 and it fails, then 5 gives you an answer with no conditions.  So you're done when you reach 5.


I'm going to be offline for awhile
Here's the function with the first 3 rules in place.
I don't have your other tables so 4 can't be implemented here.
5,6,7 are probably not correct so I left them out too.

In any case, this has the framework of what you need.
Now it's just a matter of putting the corresponding rules that work in for each WHEN statement


CREATE OR REPLACE FUNCTION get_role_name(p_user_name VARCHAR2)
    RETURN VARCHAR2
IS
    l_result VARCHAR2(4000);
BEGIN
    SELECT CASE
               WHEN 'SRA - Facility Contact' MEMBER OF namestab
               THEN
                   'Facility Contact'
               WHEN 'SRA - Lead' MEMBER OF namestab OR 'SRA - Verification Lead' MEMBER OF namestab
               THEN
                   'Lead'
               WHEN 'SRA - Verification Lead' MEMBER OF namestab
                AND 'Facility Contact' NOT MEMBER OF namestab
                AND 'SRA - Lead' NOT MEMBER OF namestab
               THEN
                   'Verification Lead'
           END
      INTO l_result
      FROM (SELECT CAST(COLLECT(sn) AS SYS.ora_mining_varchar2_nt) AS namestab
              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));

    RETURN l_result;
END;

Open in new window

0
 
LVL 16

Author Closing Comment

by:Swadhin Ray
ID: 39631857
thanks a lot ...
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question