[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

IF condition in Oracle SP

Posted on 2011-02-28
4
Medium Priority
?
521 Views
Last Modified: 2012-05-11
Below is my SP.

Though the count(*) is greater than 1 it is getting into IF condition and stting  o_original_case_num := -3. What mistake I am doing here? Please help me.
CREATE OR REPLACE PROCEDURE GPQPROC.SP_VAL_DUP_CASE
(
    i_instrument_id in GPQDBO.GPQ_ERM_REQ_DATA.ISS_ACT%TYPE,
    i_prc_date IN GPQDBO.GPQ_ERM_REQ_DATA.PRC_DT%TYPE,
    i_prc_pnt IN GPQDBO.GPQ_ERM_REQ_DATA.PRC_PNT%TYPE,
    i_curr IN GPQDBO.GPQ_ERM_REQ_DATA.CURR%TYPE,
    i_cntry IN GPQDBO.GPQ_ERM_REQ_DATA.CNTRY_QUOTE%TYPE,
    
    o_original_case_num OUT NUMBER
)
  
AS
   
   t_count number;
   
BEGIN

    o_original_case_num := 0;
    t_count := 10;
   
    SELECT COUNT(*) INTO t_count
    FROM GPQ_ERM_REQ_DATA
    WHERE ISS_ACT = i_instrument_id
    AND PRC_DT = i_prc_date
    AND PRC_PNT = i_prc_pnt
    AND CURR = i_curr
    AND CNTRY_QUOTE = i_cntry;
    
    IF (t_count <= 1) THEN
        o_original_case_num := -3;
    ELSE 
        SELECT ERM_CASE_NUM INTO o_original_case_num
        FROM GPQ_ERM_REQ_DATA
        WHERE ERM_ID = (
                        SELECT MIN(ERM_ID)
                        FROM GPQ_ERM_REQ_DATA
                        WHERE ISS_ACT = i_instrument_id
                        AND PRC_DT = i_prc_date
                        AND PRC_PNT = i_prc_pnt
                        AND CURR = i_curr
                        AND CNTRY_QUOTE = i_cntry
                        );
    END IF;
EXCEPTION
        WHEN NO_DATA_FOUND THEN
            o_original_case_num := -1;
END;
/

Open in new window

0
Comment
Question by:GouthamAnand
4 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35001469
add some 'debug' statements.  I suggest a

...
AND CNTRY_QUOTE = i_cntry;
dbms_output.put_line(' My count: ' || t_count);    
IF (t_count <= 1) THEN
...
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 2000 total points
ID: 35004182
1)   " Though the count(*) is greater than 1 it is getting into IF condition and stting  o_original_case_num := -3.  " ---> This simply means the select statement is getting a count less than or equal to 1.  

2) what is the count of records if you run the query manually in the toad/sqlplus by replacing the variables with actual values ?

3) Also this statement "t_count := 10;" is not required in your procedure code as the t_count declaration will set the value to null and count(*) is not going to return null right.  so you can comment/remove this line "t_count := 10;"

Thanks,
0
 
LVL 5

Expert Comment

by:jaiminpsoni
ID: 35004200
You have 2 different schemas and probably the issue has to do with the authid....

GPQPROC and GPQDBO are 2 different schemas.

Do you have the table GPQ_ERM_REQ_DATA in both the schemas?

Try with Authid current user...

create procedure <Procname>
  authid current_user

You can also try using qualified table name.

SELECT COUNT(*) INTO t_count
    FROM GPQDBO.GPQ_ERM_REQ_DATA
    WHERE ISS_ACT = i_instrument_id
0
 

Author Closing Comment

by:GouthamAnand
ID: 35084187
Thanks a lot.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

872 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