My stored procedure has this structure:
if Count 1>0 then
count 2 with (condition 1,)
if Count 2>0 then
count 3 with (condition 1,2)
if Count 3>0 then
count 4 with(condition 1,2,3,)
if Count 4>0 then
count 5 with (condition 1,2,3,4,)
if Count 5>0 then
count 6 with (condition 1,2,3,4,5)
if count 6>0 then
count 7 where (condition 1,2 := +,3,4,5)
…
like wise 60 filters are there.
This filter in where clause goes on until it satisfies count = 1 where clause filters are like:
cnt1 checking filter only on DD_EQP_SIZE if cnt is more than 1 then goes adding one more filter DD_EQP_TYPE,
if again cnt is more than 1 then adds DD_DEL_COUNTRY etc.
and at end checking againg only filter DD_EQP_TYPE.
like wise many more filter are there.
In the end we want tarif ID based on the eqpt type and epqt size ,deliver country and rec country tarif will be calculated and sometimes we end up no ID after appling all filters . so after confirming CNT3=0 then adding and removing one by one filters.
For example, if there is no country-specific tarif for this equipment, we'll default to the international tarif, and so on for each of the optional filters that help getting a "better" tarif but, if not found, can be omitted.
i want to reduce multiple line of code and if conditions without effecting performance.
Here is the start of the function (I tried to reduce as much as I can; see it complete and working in a fiddle):
Create OR REPLACE function Fun_Dem_Ref (P_PARTNER_CODE in varchar2 , P_del_country in varchar2,P_rec_country in varchar2,
P_cnSize in varchar2 , P_cnType in varchar2)
RETURN VARCHAR2 IS
result VARCHAR2(50);
CNTn NUMBER; CNT3 NUMBER;CNT4 NUMBER;CNT5 NUMBER;CNT6 NUMBER;CNT7 NUMBER;CNT8 NUMBER;
CNT9 NUMBER; CNT10 NUMBER; CNT11 NUMBER; CNT12 NUMBER;CNT13 NUMBER; CNT14 NUMBER; CNT15 NUMBER;
CNT16 NUMBER; CNT17 NUMBER; CNT18 NUMBER; CNT19 NUMBER;N_DD_REF NUMBER;VAR_DND_TYPE VARCHAR2(2);
VAR_DND_SIZE VARCHAR2(2);
VAR_DND_DEL_COUNTRY VARCHAR2(2);
CNTR1 NUMBER;
CNTR2 NUMBER;
endDate DATE := SYSDATE;
begin
--IF CNT2 > 0 THEN
SELECT COUNT(*) INTO CNT3 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE =P_PARTNER_CODE AND DD_EQP_SIZE = P_cnSize
AND DD_EQP_TYPE = P_cnType AND DD_DEL_COUNTRY = P_del_country
AND DD_REC_COUNTRY = P_rec_country;
IF CNT3 =1 THEN
SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE =P_PARTNER_CODE AND DD_EQP_SIZE = P_cnSize
AND DD_EQP_TYPE = P_cnType AND DD_DEL_COUNTRY = P_del_country
AND DD_REC_COUNTRY = P_rec_country;
result:= N_DD_REF;
END IF;
IF CNT3=0 THEN
SELECT COUNT(*) INTO CNT4 FROM TEMP_DEMDET_TARIF ;
IF CNT4 >0 THEN SELECT COUNT(*) INTO CNT5 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE =P_PARTNER_CODE;
IF CNT5 >0 THEN SELECT COUNT(*) INTO CNT6 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = P_cnSize;
IF CNT6 >0 THEN SELECT COUNT(*) INTO CNT6 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = P_cnSize
AND DD_EQP_TYPE = P_cnType;
IF CNT7>0 THEN
VAR_DND_TYPE := P_cnType;
SELECT COUNT(*) INTO CNT8 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = P_cnSize
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = P_del_country;
IF CNT8 >0 THEN VAR_DND_DEL_COUNTRY := P_del_country;
ELSE
VAR_DND_DEL_COUNTRY := '+';
SELECT COUNT(*) INTO CNT9 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = P_cnSize
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY;
IF CNT9 = 1 THEN SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = P_cnSize
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY;
result:= N_DD_REF;
else
SELECT COUNT(*) INTO CNTR1 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = P_cnSize
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY
AND DD_REC_COUNTRY = P_rec_country;
IF CNTR1=1 THEN
SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = P_cnSize
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY
AND DD_REC_COUNTRY = P_rec_country;
result:= N_DD_REF;
ELSE
SELECT COUNT(*) INTO CNTR2 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = P_cnSize
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY
AND DD_REC_COUNTRY = '+';
IF CNTR2 =1 THEN
SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = P_cnSize
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY
AND DD_REC_COUNTRY = '+'
AND DD_REC_COUNTRY = '+';
result:= N_DD_REF;
END IF;
END IF;
END IF;
END IF;
ELSE
VAR_DND_TYPE := '+';
SELECT COUNT(*) INTO CNT10 FROM TEMP_DEMDET_TARIF
WHERE DD_EQP_SIZE = P_cnSize
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY;
IF CNT10>0 THEN
VAR_DND_DEL_COUNTRY := P_del_country;
SELECT COUNT(*) INTO CNT11 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE =P_PARTNER_CODE
AND DD_EQP_SIZE = P_cnSize
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY;
IF CNT11 = 1 THEN
SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE =P_PARTNER_CODE
AND DD_EQP_SIZE = P_cnSize
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY;
result:= N_DD_REF;
ELSE
SELECT COUNT(*) INTO CNTR1 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = P_cnSize
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY
AND DD_REC_COUNTRY = P_rec_country;
IF CNTR1=1 THEN
SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = P_cnSize
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY
AND DD_REC_COUNTRY = P_rec_country;
result:= N_DD_REF;
ELSE
SELECT COUNT(*) INTO CNTR2 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = P_cnSize
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY
AND DD_REC_COUNTRY = '+';
IF CNTR2 =1 THEN
SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = P_cnSize
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY
AND DD_REC_COUNTRY = '+';
result:= N_DD_REF;
END IF;
END IF;
END IF;
ELSE
VAR_DND_DEL_COUNTRY := '+';
SELECT COUNT(*) INTO CNT12 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = P_cnSize
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY ;
IF CNT12 = 1 THEN
SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = P_cnSize
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY;
result:= N_DD_REF;
ELSE
SELECT COUNT(*) INTO CNTR1 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = P_cnSize
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY
AND DD_REC_COUNTRY = P_rec_country;
IF CNTR1=1 THEN
SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = P_cnSize
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY
AND DD_REC_COUNTRY = P_rec_country;
result:= N_DD_REF;
ELSE
SELECT COUNT(*) INTO CNTR2 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = P_cnSize
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY
AND DD_REC_COUNTRY = '+';
IF CNTR2 =1 THEN
SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = P_cnSize
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY
AND DD_REC_COUNTRY = '+';
result:= N_DD_REF;
END IF;
END IF;
END IF;
END IF;
END IF;
ELSE
VAR_DND_SIZE := '+';
SELECT COUNT(*) INTO CNT13 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = VAR_DND_SIZE
AND DD_EQP_TYPE = P_cnType;
IF CNT13>=1 THEN
SELECT COUNT(*) INTO CNT14 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = VAR_DND_SIZE
AND DD_EQP_TYPE = P_cnType
AND DD_DEL_COUNTRY = P_del_country;
IF CNT14 >=1 THEN
SELECT COUNT(*) INTO CNT15 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = VAR_DND_SIZE
AND DD_EQP_TYPE = P_cnType
AND DD_DEL_COUNTRY = P_del_country;
IF CNT15 = 1 THEN
SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = VAR_DND_SIZE
AND DD_EQP_TYPE = P_cnType
AND DD_DEL_COUNTRY = P_del_country;
result:= N_DD_REF;
ELSE
SELECT COUNT(*) INTO CNTR1 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = VAR_DND_SIZE
AND DD_EQP_TYPE = P_cnType
AND DD_DEL_COUNTRY = P_del_country
AND DD_REC_COUNTRY = P_rec_country;
IF CNTR1=1 THEN
SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = VAR_DND_SIZE
AND DD_EQP_TYPE = P_cnType
AND DD_DEL_COUNTRY = P_del_country
AND DD_REC_COUNTRY = P_rec_country;
result:= N_DD_REF;
ELSE
SELECT COUNT(*) INTO CNTR2 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = VAR_DND_SIZE
AND DD_EQP_TYPE = P_cnType
AND DD_DEL_COUNTRY = P_del_country
AND DD_REC_COUNTRY = '+';
IF CNTR2 =1 THEN
SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = VAR_DND_SIZE
AND DD_EQP_TYPE = P_cnType
AND DD_DEL_COUNTRY = P_del_country
AND DD_REC_COUNTRY = '+';
result:= N_DD_REF;
END IF;
END IF;
END IF;
ELSE
VAR_DND_DEL_COUNTRY := '+';
SELECT COUNT(*) INTO CNT16 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = VAR_DND_SIZE
AND DD_EQP_TYPE = P_cnType
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY ;
IF CNT16 = 1 THEN
SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = VAR_DND_SIZE
AND DD_EQP_TYPE = P_cnType
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY;
result:= N_DD_REF;
ELSE
SELECT COUNT(*) INTO CNTR1 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = VAR_DND_SIZE
AND DD_EQP_TYPE = P_cnType
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY
AND DD_REC_COUNTRY = P_rec_country;
IF CNTR1=1 THEN
SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = VAR_DND_SIZE
AND DD_EQP_TYPE = P_cnType
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY
AND DD_REC_COUNTRY = P_rec_country;
result:= N_DD_REF;
ELSE
SELECT COUNT(*) INTO CNTR2 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = VAR_DND_SIZE
AND DD_EQP_TYPE = P_cnType
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY
AND DD_REC_COUNTRY = '+';
IF CNTR2 =1 THEN
SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = VAR_DND_SIZE
AND DD_EQP_TYPE = P_cnType
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY
AND DD_REC_COUNTRY = '+';
result:= N_DD_REF;
END IF;
END IF;
END IF;
END IF;
ELSE
VAR_DND_TYPE := '+';
SELECT COUNT(*) INTO CNT17 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE =P_PARTNER_CODE
AND DD_EQP_SIZE = VAR_DND_SIZE
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = P_del_country;
IF CNT17 >=1 THEN
SELECT COUNT(*) INTO CNT18 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE =P_PARTNER_CODE
AND DD_EQP_SIZE = VAR_DND_SIZE
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = P_del_country;
IF CNT18 = 1 THEN
SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE =P_PARTNER_CODE
AND DD_EQP_SIZE = VAR_DND_SIZE
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = P_del_country;
result:= N_DD_REF;
ELSE
SELECT COUNT(*) INTO CNTR1 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = VAR_DND_SIZE
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = P_del_country
AND DD_REC_COUNTRY = P_rec_country;
IF CNTR1=1 THEN
SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = VAR_DND_SIZE
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = P_del_country
AND DD_REC_COUNTRY = P_rec_country;
result:= N_DD_REF;
ELSE
SELECT COUNT(*) INTO CNTR2 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = VAR_DND_SIZE
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = P_del_country
AND DD_REC_COUNTRY = '+';
IF CNTR2 =1 THEN
SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = VAR_DND_SIZE
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = P_del_country
AND DD_REC_COUNTRY = '+';
result:= N_DD_REF;
END IF;
END IF;
END IF;
ELSE
VAR_DND_DEL_COUNTRY := '+';
SELECT COUNT(*) INTO CNT19 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = VAR_DND_SIZE
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY ;
IF CNT19 = 1 THEN
SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = VAR_DND_SIZE
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY;
result:= N_DD_REF;
ELSE
SELECT COUNT(*)INTO CNTR1 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = VAR_DND_SIZE
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY
AND DD_REC_COUNTRY = P_rec_country;
IF CNTR1=1 THEN
SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = VAR_DND_SIZE
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY
AND DD_REC_COUNTRY = P_rec_country;
result:= N_DD_REF;
ELSE
SELECT COUNT(*) INTO CNTR2 FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = VAR_DND_SIZE
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY
AND DD_REC_COUNTRY = '+';
IF CNTR2 =1 THEN
SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = P_PARTNER_CODE
AND DD_EQP_SIZE = VAR_DND_SIZE
AND DD_EQP_TYPE = VAR_DND_TYPE
AND DD_DEL_COUNTRY = VAR_DND_DEL_COUNTRY
AND DD_REC_COUNTRY = '+';
result:= N_DD_REF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
ELSE
SELECT count(*) into CNTn FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = '+'
AND DD_EQP_SIZE = '+'
AND DD_EQP_TYPE = '+'
AND DD_DEL_COUNTRY = '+'
AND DD_REC_COUNTRY = '+';
END IF;
if CNTn = 1 then
SELECT DD_REF INTO N_DD_REF FROM TEMP_DEMDET_TARIF
WHERE DD_PARTNER_CODE = '+'
AND DD_EQP_SIZE = '+'
AND DD_EQP_TYPE = '+'
AND DD_DEL_COUNTRY = '+'
AND DD_REC_COUNTRY = '+';
result:= N_DD_REF;
END IF;
END IF;
END IF;
END IF;
RETURN result;
END Fun_Dem_Ref;
Blindly giving clues, as we're waiting for your description of what happens at the end of that IFs chain (or in the ELSE):
SELECT COUNT(*) INTO CNT1 FROM tab_mdl_pool_dnd_parm … and IF CNT1 > 0 THEN SELECT DND_ID INTO N_DND_ID FROM tab_mdl_pool_dnd_parm …) can be reduced to SELECT DND_ID INTO N_DND_ID FROM tab_mdl_pool_dnd_parm … and replacing the test by IF N_DND_ID IS NOT NULL.SELECT /* non optional filters */ ORDER BY CASE WHEN filter1 THEN 0 END, CASE WHEN filter2 THEN 0 END, CASE WHEN filter3 THEN 0 END: your returned top row (SELECT * FROM (…) WHERE ROWNUM = 1 or FETCH FIRST ROWS depending on your RDBMS version) will be **the one with the most filters passing.IF CNT2 > 0 THEN would be the right location (because it only adds filters to an already filters set on 8 criteria: DD_REF which was determined against 4 of them, plus cnSize, cnType, del_country, REC_country).WHERE clauses will help reduce the still-to-be-scored resultset.DD_REF in TAB_MHD_TARIFF_DND_PARAM. For example, if each matching DD_REF is associated with a price, ORDER BY DD_PRICE and keep the smallest one: customers will be happy!For the performance: although the hints hereabove warn you on filtering as soon as possible,
on the other hand keep in mind that running only one albeit complex SELECT, instead of dozens of simpler SELECTs will alleviate your database workload, so maybe all in all you'll get better performance even if circumventing indices.
The first step common to all these methods is of course filtering: you can discard too specific but non matching tariffs, that is, if sending to India, you can keep tariffs for India (DD_REC_COUNTRY = 'IN'), the ones for generic international delivery (DD_REC_COUNTRY = '+'), but you can certainly remove tariffs to other countries: you wouldn't want to choose a tariff to France even if (by chance, or by typo?) it's less costly than to India, because the delivery would be rejected.
This condition can be expressed as WHERE DD_REC_COUNTRY IN (P_rec_country, '+').
Then you'll still have multiple candidate tariffs, but you want to keep only one. You'll have to order the remaining tariffs, and then return only the first "best match".
This is ORDER BY ??? FETCH FIRST ROW ONLY, with the ??? to be determined (we'll study this in the next paragraphs).
Thus your function would look like:
Create OR REPLACE function Fun_Dem_Ref (P_PARTNER_CODE in varchar2 , P_del_country in varchar2,P_rec_country in varchar2,
P_cnSize in varchar2 , P_cnType in varchar2)
RETURN VARCHAR2 IS
result VARCHAR2(50);
CNT2 NUMBER; CNT3 NUMBER;CNT4 NUMBER;CNT5 NUMBER;CNT6 NUMBER;CNT7 NUMBER;CNT8 NUMBER;
CNT9 NUMBER; CNT10 NUMBER; CNT11 NUMBER; CNT12 NUMBER;CNT13 NUMBER; CNT14 NUMBER; CNT15 NUMBER;
CNT16 NUMBER; CNT17 NUMBER; CNT18 NUMBER; CNT19 NUMBER;N_DD_REF NUMBER;VAR_DND_TYPE VARCHAR2(2);
VAR_DND_SIZE VARCHAR2(2);
VAR_DND_DEL_COUNTRY VARCHAR2(2);
VAR_DND_ACT_TYPE VARCHAR2(10); -- guessing from usage
CNTR1 NUMBER;
CNTR2 NUMBER;
endDate DATE := SYSDATE;
begin
SELECT t.DD_REF INTO N_DD_REF
FROM TEMP_DEMDET_TARIF t
WHERE DD_PARTNER_CODE IN (P_PARTNER_CODE, '+')
AND DD_EQP_SIZE IN (P_cnSize, '+')
AND DD_EQP_TYPE IN (P_cnType, '+')
AND DD_DEL_COUNTRY IN (P_del_country, '+')
AND DD_REC_COUNTRY IN (P_rec_country, '+')
ORDER BY ???
FETCH FIRST ROW ONLY;
RETURN N_DD_REF;
END;
If you can say that "partner is the most important, then equipment size, and so on", that is, if you will choose a tariff with a matching partner (but all other criteria at '+'), over tariffs that are on DD_PARTNER_CODE = '+' (even though they have a perfect match on size, type, delivering and receiving countries),
then you will order by each of these successively:
ORDER BY
DECODE(DD_PARTNER_CODE, P_PARTNER_CODE, 0),
DECODE(DD_EQP_SIZE, P_cnSize, 0),
DECODE(DD_EQP_TYPE, P_cnType, 0),
DECODE(DD_DEL_COUNTRY, P_del_country, 0),
DECODE(DD_REC_COUNTRY, P_rec_country, 0)
(note: this method 2. solution is well explained in Thorsten Kettner's all-in-one answer)
N.B.: I initially used a DECODE(DD_PARTNER_CODE, P_PARTNER_CODE, 2, '+', 1, 0) DESC, but the simpler form with DECODE(DD_PARTNER_CODE, P_PARTNER_CODE, 0) ASC works because 0 (for a match) gets ordered before NULL (default value, for a non-match); as we eliminated non-match non-'+' with the WHERE, the only remaining non-matches are the +, which fall into the implicit ELSE NULL and thus get ordered after the match.
If all filters are equally important, then you have to ORDER against them not successively, but combined. For example: count the number of specific filters matching, and take the tariff with the most "points" (1 point for a specific match, 0 point for a generic '+'):
ORDER BY
DECODE(DD_PARTNER_CODE, P_PARTNER_CODE, 1) +
DECODE(DD_EQP_SIZE, P_cnSize, 1) +
DECODE(DD_EQP_TYPE, P_cnType, 1) +
DECODE(DD_DEL_COUNTRY, P_del_country, 1) +
DECODE(DD_REC_COUNTRY, P_rec_country, 1)
DESC
Note that:
What would be really great is that you already have some kind of score in your tables, which would simply be the price: thus, when having multiple possible delivery modes, you would simply choose the lowest cost one.
To prototype it, I took the liberty to add a TAB_MHD_TARIFF_DND_PARAM.DD_PRICE column, filled with some simple rules (for size: 1 ₹ per unit (so 20 ₹ or 40 ₹ for your example data; and I chose a fixed price of 60 ₹ for the generic size of '+'); for other filters: 5 ₹ for a specific match, 15 ₹ for a '+').
Then the SELECT becomes simply:
SELECT t.DD_REF INTO N_DD_REF
FROM TEMP_DEMDET_TARIF t JOIN TAB_MHD_TARIFF_DND_PARAM tp ON tp.DD_REF = t.DD_REF
WHERE DD_PARTNER_CODE IN (P_PARTNER_CODE, '+')
AND DD_EQP_SIZE IN (P_cnSize, '+')
AND DD_EQP_TYPE IN (P_cnType, '+')
AND DD_DEL_COUNTRY IN (P_del_country, '+')
AND DD_REC_COUNTRY IN (P_rec_country, '+')
ORDER BY tp.DD_PRICE
FETCH FIRST ROW ONLY;
You could also say that an equipment of size 20 would fit a package of size 40: so we could transform the WHERE to also accept bigger packages, if by chance this allows getting a better price (for example, if the partner only has a tariff for packages of 40, but at a better price than packages of 20 via generic partner '+', then choose the better price in the oversize package).
Of course, if we end up with both packages at the same price, choose the smallest one, as a second clause to the ORDER BY:
SELECT t.DD_REF INTO N_DD_REF
FROM TEMP_DEMDET_TARIF t JOIN tab_mhd_tariff_dnd_param tp ON tp.DD_REF = t.DD_REF
WHERE DD_PARTNER_CODE IN (P_PARTNER_CODE, '+')
AND (DD_EQP_SIZE IN (P_cnSize, '+') OR (DD_EQP_SIZE <> '+' AND P_cnSize <> '+' AND CAST(DD_EQP_SIZE AS NUMBER) > P_cnSize)) -- Do not forget to cast to number! Else 100 will be (alphabetically) lower than 80.
AND DD_EQP_TYPE IN (P_cnType, '+')
AND DD_DEL_COUNTRY IN (P_del_country, '+')
AND DD_REC_COUNTRY IN (P_rec_country, '+')
ORDER BY tp.DD_PRICE, DD_EQP_SIZE
FETCH FIRST ROW ONLY;
I've made these 3 variants run on your data + some tests of mine in a complete fiddle:
| P_PARTNER_CODE | P_DEL_COUNTRY | P_REC_COUNTRY | P_CNSIZE | P_CNTYPE | EXPECTED | OLD_RESULT | 2.prio | 3.score | 4.price |
|---|---|---|---|---|---|---|---|---|---|
| AE00000118 | + | + | 40 | + | 233 | 233 | 1339 | 233 | |
| PK0000188 | OM | IN | 20 | GP | 1340 | 1340 | 1340 | ||
| IN0002409 | OM | IN | 40 | GP | 1339 | 1339 | 1339 | 1339 | |
| IN0002409 | OM | IN | + | GP | 1339 | 1339 | 1339 | 1339 | |
| REC1 | IN | FR | + | GP | 1339 | 1339 | 1339 | ||
| REC1 | IN | FR | 20 | GP | 1000 | 1000 | 1339 | 1000 | |
| REC1 | IN | ES | 20 | GP | 1100 | 1339 | 1100 | ||
| REC1 | FR | DE | 20 | GP | 900 | 900 | 1339 | 900 | |
| REC1 | FR | GB | 20 | GP | 1339 | 1339 | 1339 |
I personally would choose solution 4. which is the more elegant (and client oriented)… but relies on you having the tariffs prices, of course.
Final advice: keep your current Fun_Dem_Ref running along the new function(s) you'll choose,
take time to compare their results (for example compare on your last year of deliveries: compute the percentage of exactly matching results where the new function can serve as a replace-in for the current, and understand and explain the differences to either (if the new function is erroneous) refine the scoring or (if the new function returns a better result) enjoy;
then once you're fully satisfied switch to the new function (you could simply rename Fun_Dem_Ref to Fun_Dem_Ref_old and the new function to Fun_Dem_Ref).