I am working on call center customer search query. If user enter last name and driving license I have to find out the most close matching customer(s) from database. I am using fuzzy search, soundex() to compare customer last name. Base on different conditions I have to keep using soundex function, it is effecting performance. I try to use With Clause but since I have many IF and ELSE parts it is giving table or view not found exception.
I tried to google to find how to use with the clause and with clause with function, none of them are helpful.
PROCEDURE RetrieveCustomer
(
i_lastname IN varchar,
i_DL IN varchar,
o_cursor OUT T_CURSOR
)
IS
v_DLAndExactLastNameCnt Number;
v_DLAndLastNameCnt Number;
v_DLCnt Number;
BEGIN
SELECT COUNT(*) INTO v_DLAndExactLastNameCnt
FROM t1, t2
WHERE t1.DL = i_DL AND (upper(t2.last_name) like upper(i_lastname));
IF(v_DLAndExactLastNameCnt > 0) THEN
OPEN o_cursor FOR
select
a,
b,
'DL + ELN' as search_result_baseon --exact last name match
from t1, t2, t3, t4
where t1.DL = i_DL AND (upper(t2.last_name) like upper(i_lastname));
ELSE
SELECT COUNT(*) INTO v_DLAndLastNameCnt
FROM t1, t2
WHERE t1.DL= i_DL
AND p.last_name in (select last_name from t2 where soundex(last_name) = soundex(i_lastname));
IF(v_DLAndLastNameCnt > 0) THEN
OPEN o_cursor FOR
select
a,
b,
'DL + LN' as search_result_baseon -- last name not exact match
from t1, t2, t3, t4
where
t1.DL= i_DL
AND p.last_name in (
select last_name from t2 where soundex(last_name) = soundex(i_lastname)
);
ELSE
SELECT COUNT(*) INTO v_DLCnt
FROM t1
WHERE t1.DL = i_DL;
IF(v_DLCnt > 0) THEN
OPEN o_cursor FOR
select a
b,
'DL' as search_result_baseon
from t1, t2, t3, t4
where t1.DL = i_DL;
ELSE
OPEN o_cursor FOR
select
a,
b,
'LN' as search_result_baseon
from t1, t2, t3, t4
where t2.last_name IN (
select last_name from t2 where soundex(last_name) = soundex(i_lastname)
)
END IF;
END IF;
END IF;
END RetrieveCustomer;
Is there any way I can store matching last name record in some in-memory table and use it as and when require in that procedure? Can anyone of you please suggest me better way to write my procedure:
