Solved

Re : Function

Posted on 2011-03-14
18
651 Views
Last Modified: 2013-12-07
Hi every one
create or replace function employee_billrate_func (employee_number in varchar2, project_name in varchar2)
return number
is
v_bill_rate number;
v_bill_rate1 number;
v_bill_rate2 number;
--v_bill_rate3 number;
begin
dbms_output.put_line('v_bill_rate is');
begin
select distinct pbr2.rate into v_bill_rate
from pa_emp_bill_rate_overrides pbr2,
ap_invoice_lines_all aia,
per_people_x ppf,
pa_projects_all ppa
where pbr2.project_id = aia.project_id
and ppa.project_id = aia.project_id
and aia.attribute1 = ppf.npw_number
and ppf.person_id = pbr2.person_id
and aia.attribute1 is not null
and ppf.npw_number = employee_number
and ppa.name = project_name;
exception
when others then
dbms_output.put_line('error'||sqlerrm);
end;
if v_bill_rate IS NOT null then
dbms_output.put_line('v_bill_rate is'|| v_bill_rate);
end if;
return v_bill_rate;
begin
select distinct pbr3.rate into v_bill_rate1
from pa_job_bill_rate_overrides pbr3,
ap_invoice_lines_all aia,
per_people_f ppf,
pa_projects_all ppa,
per_all_assignments_f paf
where pbr3.project_id = aia.project_id
and ppa.project_id = aia.project_id
and aia.attribute1 = ppf.npw_number
and paf.job_id = pbr3.job_id
and ppf.person_id = paf.person_id
and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
and paf.primary_flag='Y'
and aia.attribute1 is not null
and ppf.npw_number = employee_number
and ppa.name = project_name;
exception
when others then
dbms_output.put_line('error'||sqlerrm);
end;
if v_bill_rate1 IS NOT null then
dbms_output.put_line('v_bill_rate1 is'|| v_bill_rate1);
end if;
return v_bill_rate1;
begin
select distinct pbr.rate into v_bill_rate2
from pa_projects_all ppa,
pa_tasks pta,
ap_invoice_lines_all aia,
per_people_x ppf,
pa_bill_rates_all pbr
where ppa.project_id = pta.project_id
and ppa.project_id = aia.project_id
and pta.task_id = aia.task_id
and ppf.npw_number = aia.attribute1
and ppf.person_id = pbr.person_id
and pbr.org_id = aia.org_id
and ppf.npw_number = employee_number
and ppa.name = project_name
union
select distinct pbr.rate
from pa_projects_all ppa,
pa_tasks pta,
ap_invoice_lines_all aia,
per_all_assignments_f ppf,
per_people_x papf,
pa_bill_rates_all pbr
where ppa.project_id = pta.project_id
and ppa.project_id = aia.project_id
and pta.task_id = aia.task_id
and papf.npw_number = aia.attribute1
and ppf.job_id = pbr.job_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and ppf.primary_flag='Y'
and papf.person_id = ppf.person_id
and aia.org_id = pbr.org_id
and papf.npw_number = employee_number
and ppa.name = project_name;
--dbms_output.put_line('v_bill_rate is'|| v_bill_rate2);
exception
when others then
dbms_output.put_line('error'||sqlerrm);
end;
if v_bill_rate2 IS NOT null then
dbms_output.put_line('v_bill_rate2 is'|| v_bill_rate2);
end if;
return v_bill_rate2;
exception
when others then
dbms_output.put_line('error'||sqlerrm);
end;
exception
when others then
dbms_output.put_line('error'||sqlerrm);
end;
/
i am using the above query to return a bill rate when i execute the select statements individually they are working but when i call the function
its not returning any value

saying no data found

comments please
0
Comment
Question by:iamtechnical
  • 8
  • 6
  • 4
18 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 35130642
please post your exact tests and results
0
 

Author Comment

by:iamtechnical
ID: 35130871
what do u mean exact tests......
because i tested with my DB values
0
 
LVL 11

Expert Comment

by:Akenathon
ID: 35131193
You have unreachable code, probably your returns should go inside the preceeding IFs, for instance the first one:

   IF v_bill_rate IS NOT NULL THEN
      DBMS_OUTPUT.put_line ('v_bill_rate is' || v_bill_rate);
   END IF;

   RETURN v_bill_rate;

Open in new window


As it is outside the IF, the function returns NULL whenever your first SELECT returns NULL :-)
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35131202
>>> when i execute the select statements individually

post your select statement including the values you tested with

also post your code that executes the function, including the values you tested with
0
 
LVL 11

Expert Comment

by:Akenathon
ID: 35131272
By the way: You can have Oracle spot similar issues by using alter session set plsql_warnings='enable:all', see here:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams166.htm#REFRN10249
0
 

Author Comment

by:iamtechnical
ID: 35131683
create or replace function xxcra_apb_emp_br2 (employee_number in varchar2, project_name in varchar2)
return number
       is
/******************************************************************************
   NAME:       xxcra_apb_emp_br
   PURPOSE:    

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        3/14/2011          1. AT&T Created this function.

   NOTES:

   Automatically available Auto Replace Keywords:
      Object Name:     xxcra_apb_emp_br
      Sysdate:         3/14/2011
      Date and Time:   3/14/2011, 11:16:02 AM, and 3/14/2011 11:16:02 AM
      Username:         (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)
Description: Fetch Bill-rate for CWK workers associated with Projects in AP Batch Edit Project
******************************************************************************/
          v_bill_rate     number;
--          v_bill_rate1    number;
  --        v_bill_rate2    number;  
    begin
    dbms_output.put_line('v_bill_rate is');    
     
         select distinct pbr2.rate into v_bill_rate
              from  pa_emp_bill_rate_overrides pbr2,
              ap_invoice_lines_all aia,
              per_people_x   ppf,
              pa_projects_all ppa
              where pbr2.project_id = aia.project_id
              and ppa.project_id = aia.project_id
              and aia.attribute1 = ppf.npw_number
              and ppf.person_id = pbr2.person_id
              and aia.attribute1 is not null  
              and ppf.npw_number = employee_number
              and ppa.name = project_name;
           
            dbms_output.put_line('executed first query '|| v_bill_rate);  
     
        IF v_bill_rate IS not NULL THEN
       
                    RETURN V_BILL_RATE;
                   
        else
            select distinct pbr3.rate into v_bill_rate
              from  pa_job_bill_rate_overrides pbr3,
              ap_invoice_lines_all aia,
              per_people_f   ppf,
              pa_projects_all ppa,
              per_all_assignments_f paf
              where pbr3.project_id = aia.project_id
              and ppa.project_id = aia.project_id
              and aia.attribute1 = ppf.npw_number
              and paf.job_id = pbr3.job_id
              and ppf.person_id = paf.person_id
              and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date
              and paf.primary_flag='Y'
              and aia.attribute1 is not null
              and ppf.npw_number = employee_number
              and ppa.name = project_name;
             
              IF v_bill_rate IS not NULL THEN
       
                    RETURN V_BILL_RATE;
             
            else
                select distinct pbr.rate into v_bill_rate
                from pa_projects_all ppa,
                pa_tasks pta,
                ap_invoice_lines_all aia,
                per_people_x ppf,
                pa_bill_rates_all pbr
                where ppa.project_id = pta.project_id
                and ppa.project_id = aia.project_id
                and pta.task_id = aia.task_id
            and ppf.npw_number = aia.attribute1
            and ppf.person_id = pbr.person_id
            and pbr.org_id = aia.org_id
            and ppf.npw_number = employee_number
            and ppa.name =  project_name
        union
        select distinct pbr.rate --into v_bill_rate2
            from pa_projects_all ppa,
            pa_tasks pta,
            ap_invoice_lines_all aia,
            per_all_assignments_f ppf,
            per_people_x papf,
            pa_bill_rates_all pbr
            where ppa.project_id = pta.project_id
            and ppa.project_id = aia.project_id
            and pta.task_id = aia.task_id
            and papf.npw_number = aia.attribute1
            and ppf.job_id = pbr.job_id
            and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
            and ppf.primary_flag='Y'
            and papf.person_id = ppf.person_id
            and aia.org_id = pbr.org_id
            and papf.npw_number = employee_number
            and ppa.name =  project_name;
                RETURN V_BILL_RATE;
       
                   
            END if;
           
         end if;
       
exception
            when others then
           dbms_output.put_line('error1 '||sqlerrm);
end xxcra_apb_emp_br2;
 /


modified little bit but getting same error again
0
 

Author Comment

by:iamtechnical
ID: 35131694
@SDSTUBER

select distinct pbr2.rate , ppa.name, aia.attribute1
              from  pa_emp_bill_rate_overrides pbr2,
              ap_invoice_lines_all aia,
              per_people_x   ppf,
              pa_projects_all ppa
              where pbr2.project_id = aia.project_id
              and ppa.project_id = aia.project_id
              and aia.attribute1 = ppf.npw_number
              and ppf.person_id = pbr2.person_id
              and aia.attribute1 is not null  
              and ppf.npw_number = 'C06423'
              and ppa.name = 'K16060'

this is what a i tried


0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35131735
and what was the result of running that query?

how did you invoke your function?

something like this?

select  xxcra_apb_emp_br2 ('C06423', 'K16060')  from dual;

what does it return?



0
 

Author Comment

by:iamtechnical
ID: 35131755
error
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "APPS.XXCRA_APB_EMP_BR2", line 114

dbms output is

v_bill_rate is
error1 ORA-01403: no data found
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 11

Expert Comment

by:Akenathon
ID: 35131834
You have now erased the exception blocks... so the first SELECT that doesn't find anything rightfully raises the 1403.

The 6503 just menas that ALL your SELECTs returned NULL, so the returns (which are now contained within the IFs) never get executed. Why don't you unconditionally return NULL at the bottom of your function when you are not finding anything? Or else, put the last return OUTSIDE the IFs...
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35131841
that's your problem...

one of your queries is returning no rows

which then raises a no_data_found exception
your code captures the exception but then doesn't reraise an exception or return a value, it simply ends which isn't allowed

figure out which query is returning no rows and either fix it so it does, or handle the exception
0
 

Author Comment

by:iamtechnical
ID: 35131920
@sdstuber

yeah i need that , every query need not raise a output for me......

actually the thg is if i have rate from first select stmt, it shd return value there itself and exit the function
else it shd go to next select,

even though all the select stmts has output it shd return which comes first
0
 

Author Comment

by:iamtechnical
ID: 35131944
Akenathon:

can you make it more precise
would you please modify according to your argument for me
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
ID: 35131991
if you want to check for no data found on each query, you must do so explicitly
CREATE OR REPLACE FUNCTION xxcra_apb_emp_br2(employee_number IN VARCHAR2, project_name IN VARCHAR2)
    RETURN NUMBER
IS
    /******************************************************************************
       NAME:       xxcra_apb_emp_br
       PURPOSE:

       REVISIONS:
       Ver        Date        Author           Description
       ---------  ----------  ---------------  ------------------------------------
       1.0        3/14/2011          1. AT&T Created this function.

       NOTES:

       Automatically available Auto Replace Keywords:
          Object Name:     xxcra_apb_emp_br
          Sysdate:         3/14/2011
          Date and Time:   3/14/2011, 11:16:02 AM, and 3/14/2011 11:16:02 AM
          Username:         (set in TOAD Options, Procedure Editor)
          Table Name:       (set in the "New PL/SQL Object" dialog)
    Description: Fetch Bill-rate for CWK workers associated with Projects in AP Batch Edit Project
    ******************************************************************************/
    v_bill_rate   NUMBER;
--          v_bill_rate1    number;
--        v_bill_rate2    number;
BEGIN
    DBMS_OUTPUT.put_line('v_bill_rate is');

    BEGIN
        SELECT DISTINCT pbr2.rate
          INTO v_bill_rate
          FROM pa_emp_bill_rate_overrides pbr2,
               ap_invoice_lines_all aia,
               per_people_x ppf,
               pa_projects_all ppa
         WHERE     pbr2.project_id = aia.project_id
               AND ppa.project_id = aia.project_id
               AND aia.attribute1 = ppf.npw_number
               AND ppf.person_id = pbr2.person_id
               AND aia.attribute1 IS NOT NULL
               AND ppf.npw_number = employee_number
               AND ppa.name = project_name;
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            v_bill_rate  := NULL;
    END;

    DBMS_OUTPUT.put_line('executed first query ' || v_bill_rate);

    IF v_bill_rate IS NOT NULL
    THEN
        RETURN v_bill_rate;
    ELSE
        BEGIN
            SELECT DISTINCT pbr3.rate
              INTO v_bill_rate
              FROM pa_job_bill_rate_overrides pbr3,
                   ap_invoice_lines_all aia,
                   per_people_f ppf,
                   pa_projects_all ppa,
                   per_all_assignments_f paf
             WHERE     pbr3.project_id = aia.project_id
                   AND ppa.project_id = aia.project_id
                   AND aia.attribute1 = ppf.npw_number
                   AND paf.job_id = pbr3.job_id
                   AND ppf.person_id = paf.person_id
                   AND TRUNC(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
                   AND paf.primary_flag = 'Y'
                   AND aia.attribute1 IS NOT NULL
                   AND ppf.npw_number = employee_number
                   AND ppa.name = project_name;
        EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
                v_bill_rate  := NULL;
        END;

        IF v_bill_rate IS NOT NULL
        THEN
            RETURN v_bill_rate;
        ELSE
            BEGIN
                SELECT DISTINCT pbr.rate
                  INTO v_bill_rate
                  FROM pa_projects_all ppa,
                       pa_tasks pta,
                       ap_invoice_lines_all aia,
                       per_people_x ppf,
                       pa_bill_rates_all pbr
                 WHERE     ppa.project_id = pta.project_id
                       AND ppa.project_id = aia.project_id
                       AND pta.task_id = aia.task_id
                       AND ppf.npw_number = aia.attribute1
                       AND ppf.person_id = pbr.person_id
                       AND pbr.org_id = aia.org_id
                       AND ppf.npw_number = employee_number
                       AND ppa.name = project_name
                UNION
                SELECT DISTINCT pbr.rate                                         --into v_bill_rate2
                  FROM pa_projects_all ppa,
                       pa_tasks pta,
                       ap_invoice_lines_all aia,
                       per_all_assignments_f ppf,
                       per_people_x papf,
                       pa_bill_rates_all pbr
                 WHERE     ppa.project_id = pta.project_id
                       AND ppa.project_id = aia.project_id
                       AND pta.task_id = aia.task_id
                       AND papf.npw_number = aia.attribute1
                       AND ppf.job_id = pbr.job_id
                       AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date
                                              AND ppf.effective_end_date
                       AND ppf.primary_flag = 'Y'
                       AND papf.person_id = ppf.person_id
                       AND aia.org_id = pbr.org_id
                       AND papf.npw_number = employee_number
                       AND ppa.name = project_name;
            EXCEPTION
                WHEN NO_DATA_FOUND
                THEN
                    v_bill_rate  := NULL;
            END;

            RETURN v_bill_rate;
        END IF;
    END IF;
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line('error1 ' || SQLERRM);
END xxcra_apb_emp_br2;

Open in new window

0
 
LVL 11

Expert Comment

by:Akenathon
ID: 35132025
He was capturing all exceptions in the very first version, but he then removed all exception blocks. All that was really needed was to move the return statements inside the IFs which check for NULL return values...
0
 

Author Closing Comment

by:iamtechnical
ID: 35132418
good one , but still have doubts
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35132430
please don't close questions with penalty grades unless you're not getting feedback from the participants

0
 

Author Comment

by:iamtechnical
ID: 35132444
@sdstuber

CREATE OR REPLACE FUNCTION xxcra_apb_emp_br3(employee_number IN VARCHAR2, project_name IN VARCHAR2)
    RETURN NUMBER
IS
    /******************************************************************************
       NAME:       xxcra_apb_emp_br
       PURPOSE:

       REVISIONS:
       Ver        Date        Author           Description
       ---------  ----------  ---------------  ------------------------------------
       1.0        3/14/2011          1. AT&T Created this function.

       NOTES:

       Automatically available Auto Replace Keywords:
          Object Name:     xxcra_apb_emp_br
          Sysdate:         3/14/2011
          Date and Time:   3/14/2011, 11:16:02 AM, and 3/14/2011 11:16:02 AM
          Username:         (set in TOAD Options, Procedure Editor)
          Table Name:       (set in the "New PL/SQL Object" dialog)
    Description: Fetch Bill-rate for CWK workers associated with Projects in AP Batch Edit Project
    ******************************************************************************/
    v_bill_rate   NUMBER:=9999;
--          v_bill_rate1    number;
--        v_bill_rate2    number;
BEGIN
    DBMS_OUTPUT.put_line('v_bill_rate is');

    BEGIN
    if (employee_number is not null) then
        SELECT DISTINCT nvl(pbr2.rate ,888) into v_bill_rate
          --INTO v_bill_rate
          FROM pa_emp_bill_rate_overrides pbr2,
               ap_invoice_lines_all aia,
               per_people_x ppf,
               pa_projects_all ppa
         WHERE     pbr2.project_id = aia.project_id
               AND ppa.project_id = aia.project_id
               AND aia.attribute1 = ppf.npw_number
               AND ppf.person_id = pbr2.person_id
               AND aia.attribute1 IS NOT NULL
               AND ppf.npw_number = employee_number
               AND ppa.name = project_name;
               
       DBMS_OUTPUT.put_line('1. executed first query ' || v_bill_rate);        
    end if;
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
        DBMS_OUTPUT.put_line('2. executed first query ' || v_bill_rate);
            v_bill_rate  := NULL;
   
    END;

    DBMS_OUTPUT.put_line('executed first query ' || v_bill_rate);

    IF v_bill_rate IS NOT NULL
    THEN
        RETURN v_bill_rate;
    ELSE
        BEGIN
            SELECT DISTINCT pbr3.rate
              INTO v_bill_rate
              FROM pa_job_bill_rate_overrides pbr3,
                   ap_invoice_lines_all aia,
                   per_people_f ppf,
                   pa_projects_all ppa,
                   per_all_assignments_f paf
             WHERE     pbr3.project_id = aia.project_id
                   AND ppa.project_id = aia.project_id
                   AND aia.attribute1 = ppf.npw_number
                   AND paf.job_id = pbr3.job_id
                   AND ppf.person_id = paf.person_id
                   AND TRUNC(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
                   AND paf.primary_flag = 'Y'
                   AND aia.attribute1 IS NOT NULL
                   AND ppf.npw_number = employee_number
                   AND ppa.name = project_name;
        EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
                v_bill_rate  := NULL;
        END;
       
        dBMS_OUTPUT.put_line('executed second query ' || v_bill_rate);

        IF v_bill_rate IS NOT NULL
        THEN
            RETURN v_bill_rate;
        ELSE
            BEGIN
                SELECT DISTINCT pbr.rate
                  INTO v_bill_rate
                  FROM pa_projects_all ppa,
                       pa_tasks pta,
                       ap_invoice_lines_all aia,
                       per_people_x ppf,
                       pa_bill_rates_all pbr
                 WHERE     ppa.project_id = pta.project_id
                       AND ppa.project_id = aia.project_id
                       AND pta.task_id = aia.task_id
                       AND ppf.npw_number = aia.attribute1
                       AND ppf.person_id = pbr.person_id
                       AND pbr.org_id = aia.org_id
                       AND ppf.npw_number = employee_number
                       AND ppa.name = project_name
                UNION
                SELECT DISTINCT pbr.rate                                         --into v_bill_rate2
                  FROM pa_projects_all ppa,
                       pa_tasks pta,
                       ap_invoice_lines_all aia,
                       per_all_assignments_f ppf,
                       per_people_x papf,
                       pa_bill_rates_all pbr
                 WHERE     ppa.project_id = pta.project_id
                       AND ppa.project_id = aia.project_id
                       AND pta.task_id = aia.task_id
                       AND papf.npw_number = aia.attribute1
                       AND ppf.job_id = pbr.job_id
                       AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date
                                              AND ppf.effective_end_date
                       AND ppf.primary_flag = 'Y'
                       AND papf.person_id = ppf.person_id
                       AND aia.org_id = pbr.org_id
                       AND papf.npw_number = employee_number
                       AND ppa.name = project_name;
            EXCEPTION
                WHEN NO_DATA_FOUND
                THEN
                    v_bill_rate  := NULL;
                END;
                dBMS_OUTPUT.put_line('executed third query ' || v_bill_rate);
            RETURN v_bill_rate;
        END IF;
    END IF;
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line('error1 ' || SQLERRM);
END xxcra_apb_emp_br3;


Made some changes to your code and executed the function
this is o/p i am getting

"v_bill_rate is
2. executed first query 9999
executed first query
executed second query
executed third query"

u can see their its directly going to dbms line ...without executing the first select query

help me out

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now