asked on

IF condition in Oracle SP

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

slightwv (䄆 Netminder)

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

ASKER CERTIFIED SOLUTION

Naveen Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jaiminpsoni

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

GouthamAnand

ASKER

Thanks a lot.