[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

IF condition in Oracle SP

Posted on 2011-02-28
4
Medium Priority
?
518 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
[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
4 Comments
 
LVL 77

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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 setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

650 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