Solved

IF condition in Oracle SP

Posted on 2011-02-28
4
506 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
Deny Oracle DBAs from Connecting  "/ as sysdba" 5 61
Maintaining Oracle Managed Accounts 2 37
Oracle SQL 6 57
ODBC in excel 2016 in Windows 10 via VBA 16 58
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

816 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

9 Experts available now in Live!

Get 1:1 Help Now