Solved

IF condition in Oracle SP

Posted on 2011-02-28
4
507 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 76

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 500 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Records that don't match 8 50
Query to identify changes between rows of two tables 8 55
Fill Null values 5 28
Procedure syntax 5 38
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

792 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