Stored procedure & trigger compiles but no data found error

Question

I have successfully ran the following procedure and the trigger but when I run the whole script, I get the error message followed by the code. Please help me to understand the error message I get here. I see the data on the tables but it doesn't grab?

--ERROR MESSAGE Error starting at line 1,243 in command: EXECUTE p_update_cust_fuzzy_perform_mv Error report: ORA-01403: no data found ORA-01403: no data found ORA-06512: at "FUZZYTEST1.T_CUST_FUZZY_PERFORMANCE_MV", line 23 ORA-04088: error during execution of trigger 'FUZZYTEST1.T_CUST_FUZZY_PERFORMANCE_MV' ORA-06512: at "FUZZYTEST1.P_UPDATE_CUST_FUZZY_PERFORM_MV", line 30 ORA-06512: at line 1 01403. 00000 - "no data found"

*Cause:
*Action:

CREATE or REPLACE 
PROCEDURE p_update_cust_fuzzy_perform_mv is

v_customer_no wt_net.customer_no%type;
v_lname  wt_net.lname%type;
v_fname wt_net.fname%type;
v_report_date date;
v_mem_set int;
v_fuzzy_status int;

CURSOR performance_cur is
select t1.customer_no,t1.lname,t1.fname, sysdate,t2.mem_set, t2.status
FROM 
(
SELECT customer_no, lname, fname, sysdate
FROM wt_net)t1,
(
select DISTINCT MEM_SET as mem_set, status
from fuzzy_param_triangle
where status=1)t2;



BEGIN
OPEN performance_cur;

LOOP
    FETCH performance_cur into v_customer_no,  v_lname, v_fname, v_report_date, v_mem_set, v_fuzzy_status;
    EXIT when performance_cur%notfound;
    IF v_fuzzy_status =1 THEN
INSERT INTO CUST_FUZZY_PERFORMANCE_mv (customer_no, lname, fname, report_date, mem_set) VALUES(v_customer_no, v_lname, v_fname, v_report_date, v_mem_set);
END IF;
END LOOP;
CLOSE performance_cur;
END;
/

--drop trigger t_CUST_FUZZY_PERFORMANCE_mv;
CREATE OR REPLACE TRIGGER t_CUST_FUZZY_PERFORMANCE_mv 
BEFORE INSERT ON CUST_FUZZY_PERFORMANCE_mv
FOR EACH ROW
--This trigger will update the emp_fuzzy_performance and 
--emp_fuzzy_performance_weight  tables when an insert is made on the 
--emp_fuzzy_performance_mv materialized view.


DECLARE

v_s_customer_no cust_fuzzy_TOTAL_TIME.customer_no%type;
v_s_excellent cust_fuzzy_TOTAL_TIME.excellent%type;
v_s_average cust_fuzzy_TOTAL_TIME.average%type;
v_s_POOR cust_fuzzy_TOTAL_TIME.POOR%type;
v_s_mem_set cust_fuzzy_TOTAL_TIME.mem_set%type;

v_o_excellent cust_fuzzy_NUM_OF_FOLLOWUP.excellent%type;
v_o_average cust_fuzzy_NUM_OF_FOLLOWUP.average%type;
v_o_POOR cust_fuzzy_NUM_OF_FOLLOWUP.POOR%type;


count_rec int;

BEGIN


select count (*) into count_rec from fuzzy_param_trapezoid where MEM_SET = (:new.mem_set);
if(count_rec = 0) then


SELECT DISTINCT s.customer_no, s.POOR, s.average, s.excellent, s.mem_set, o.POOR, o.average, o.excellent

INTO v_s_customer_no, v_s_POOR, v_s_average, v_s_excellent, v_s_mem_set, v_o_POOR, v_o_average, v_o_excellent


FROM cust_fuzzy_TOTAL_TIME s, cust_fuzzy_NUM_OF_FOLLOWUP o
WHERE s.customer_no = o.customer_no

AND s.customer_no = :new.customer_no
AND s.mem_set = o.mem_set

AND s.mem_set = :new.mem_set;   


--update fuzzy fields in emp_fuzzy_performance_mv
--if there is an overlap in values, the highest rating will be used.  
--for example if the employee has a value in both average and below average, average will be
 --used. 
:new.NUM_OF_FOLLOWUP := case 
when v_o_excellent >0 then 'EXCELLENT'
when v_o_average > 0 then 'AVG'
when  v_o_POOR > 0 then 'POOR'  
end;


:new.TOTAL_TIME := case 
when v_s_excellent >0 then 'EXCELLENT'
when v_s_average > 0 then 'AVG'
when  v_s_POOR > 0 then 'POOR'  
end;

INSERT INTO CUST_FUZZY_PERFORMANCE values(:new.customer_no, v_s_mem_set, :new.lname, :new.fname, :new.report_date, 
case 
when v_o_excellent >0 then 'EXCELLENT'
when v_o_average > 0 then 'AVG'
when  v_o_POOR > 0 then 'POOR'  
end,


case 
when v_s_excellent >0 then 'EXCELLENT'
when v_s_average > 0 then 'AVG'
when  v_s_POOR > 0 then 'POOR'  
end);

INSERT INTO CUST_FUZZY_PERFORMANCE_weight values(:new.customer_no,v_s_mem_set, :new.lname, :new.fname, :new.report_date, 
case 
when v_o_excellent >0 then 'EXCELLENT'
when v_o_average > 0 then 'AVG'
when  v_o_POOR > 0 then 'POOR'  
end,

case 
when v_o_excellent >0 then v_o_excellent
when v_o_average > 0 then v_o_average 
when  v_o_POOR > 0 then v_o_POOR  
end,


case 
when v_s_excellent >0 then 'EXCELLENT'
when v_s_average > 0 then 'AVG'
when  v_s_POOR > 0 then 'POOR'  
end,

case 
when v_s_excellent >0 then v_s_excellent 
when v_s_average > 0 then v_s_average 
when  v_s_POOR > 0 then v_s_POOR
 end);

END IF;

END;
/

It says "no data found"; copy your select statement and run it separately. If you see rows returned, then it is an issue; If no rows, then it is NOT an issue. Maybe your query is not correct. — jose_bacoy
as the error suggested there should be query that is returning nothing so you have to check your all select statements first. — Paras

Unknown Unknown · Accepted Answer · 2018-04-05T01:56:41

what you are doing is like joining 2 tables and whats missing in your code is the where clause like this

where t1.col = t2.col

you can also do it using join like this

select t1.customer_no, t1.lname, t1.fname, sysdate, t2.mem_set, t2.status
FROM 
(SELECT customer_no, lname, fname, sysdate
FROM wt_net) t1
JOIN
(select DISTINCT MEM_SET as mem_set, status
from fuzzy_param_triangle
where status=1) t2
ON t1.col = t2.col;

Stored procedure & trigger compiles but no data found error

1 Answers