Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do I handled exceptions in a join SQL Procedure

Status
Not open for further replies.

john9

Programmer
May 31, 2002
16
0
0
US
In the SQL shown below I am joining several tables in order to compare record count totals. How do you cause SQL to generate a exception error and abnormally terminate the procedure when any one of the columns being compared do not match?
===========================================================
select bal.detail_record_cnt, act.trl_policy_record_total, act.trl_agent_record_total
from commdev.comm_agent_balances_trl_stg1 bal, commdev.comm_policy_agent_trl_stg1 act,
commdev.comm_agent_activity_count agtcnt, commdev.comm_policy_activity_count polcnt,
commdev.comm_agent_balance_count balcnt

where bal.detail_record_cnt = balcnt.agent_balance_count
and act.trl_agent_record_total = agtcnt.agent_activity_count
and act.trl_policy_record_total = polcnt.policy_activity_count;
 
DECLARE
lv_1 NUMBER;
lv_2 NUMBER;
lv_3 NUMBER;
BEGIN
select bal.detail_record_cnt,
act.trl_policy_record_total,
act.trl_agent_record_total
into lv_1, lv_2, lv_3
from commdev.comm_agent_balances_trl_stg1 bal,
commdev.comm_policy_agent_trl_stg1 act,
commdev.comm_agent_activity_count agtcnt,
commdev.comm_policy_activity_count polcnt,
commdev.comm_agent_balance_count balcnt
EXCEPTION
WHEN no_data_found THEN
RAISE_APPLICATION_ERROR(-20001, 'At Least One Column Is Different!');
END;
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top