Exiting from sql script when no data found
612530Jan 30 2008 — edited Jan 30 2008Hi
i'm having a sql script in that there are three queries.
i want to exit from the sql script displaying as "No duplicates" if i have no records for the first query, can anyone suggest me how to do it in a sql script, without anonymous block, below i have given my script
========================================
whenever sqlerror exit sql.sqlcode
set serveroutput on
column fn new_value filename
select 'remove_duplicates_'||to_char(sysdate, 'yyyymmddhh24miss')||'.log' as fn from dual;
spool E:/&filename
---------------------------------------
prompt 'Checking for duplicates'
---------------------------------------
select account_id, count(1)
from accounts
group by account_id
having count(1) > 1;
---------------------------------------
prompt 'Records to be deleted'
---------------------------------------
select *
from accounts
where decode ( substr(account_id,1,3),'COG',1,0)=1
group by account_id
having count(1) > 1;
---------------------------------------
prompt ' Deleting duplicates'
---------------------------------------
delete
from accounts
where account_id in (
select account_id
from accounts
where decode ( substr(account_id,1,3),'COG',1,0)=1
group by account_id
having count(1) > 1
);
commit;
/