Oracle related to count(*) syntax alternate options

by ashutosh   Last Updated June 13, 2019 09:06 AM

i am using a Stored Procedure which are using multiple count(*) in oracle which makes slowness of the server the same i have used in db2 using if exists() which works fine in db2 but the same syntax is not supporting in oracle. below is my Stored Procedure: i have to use 8 elsif conditions(just use here 2 only)

in the below SP i don't want to use count(*). can anyone help on this:

CREATE OR REPLACE FUNCTION GetStandadrdDefaultDataXml
(
    p_pf_wkstn_oid_sh number,   
    p_pf_wkstn_oid_lng number   
)
RETURN varchar2
IS
 StdDefaultValue varchar2(1500);
 rate_count1L NUMBER(5);
 rate_count1 NUMBER(5);
 v_count NUMBER(5);
BEGIN 

    --RateTable1L
    select count(*) into rate_count1L FROM RateTable1L where wkstn_oid_sh = p_pf_wkstn_oid_sh 
            AND wkstn_oid_lng = p_pf_wkstn_oid_lng and exact='STANDARD DEFAULT';
    --RateTable1
    select count(*) into rate_count1  FROM RateTable1 where wkstn_oid_sh = p_pf_wkstn_oid_sh 
            AND wkstn_oid_lng = p_pf_wkstn_oid_lng and exact='STANDARD DEFAULT';


    --RateTable1 and RateTable1L
    IF( rate_count1L > 0 ) 
    THEN
        --RateTable1L
        SELECT rate into StdDefaultValue FROM RateTable1L where wkstn_oid_sh = p_pf_wkstn_oid_sh AND wkstn_oid_lng = p_pf_wkstn_oid_lng  and exact ='STANDARD DEFAULT';
    ELSIF ( rate_count1 > 0 )
    THEN
        --RateTable1
        SELECT rate into StdDefaultValue FROM RateTable1 where wkstn_oid_sh = p_pf_wkstn_oid_sh AND wkstn_oid_lng = p_pf_wkstn_oid_lng  and exact ='STANDARD DEFAULT';
    --RateTable2 and RateTable2L
    --- multiple elsif we have to use here.
        END IF;

    return StdDefaultValue;
END;
/

Please provide solution/hints to handle

Tags : oracle


Related Questions


Updated February 05, 2018 08:06 AM

Updated July 03, 2016 08:02 AM

Updated February 24, 2017 12:06 PM

Updated August 28, 2017 14:06 PM

Updated August 02, 2018 06:06 AM