Submitted by: JP Vijaykumar, Oracle DBA
FOR PROCESSING DATA IN A PROCEDURE THE FOLLWOING CONDITONS
ARE TO BE SATISFIED:
01 CONDITION
TABLE TABA SHOULD HAVE MATCHING RECORDS IN TABLE TABB
(WHEN TABA.TRAINS = 'Y' AND TABB.TYPE = 'S'
AND TABA.CID = TABB.CID)
OR
(WHEN TABA.CARS = 'Y' AND TABB.TYPE = 'A'
AND TABA.CID = TABB.CID)
OR
(WHEN TABA.BIKES = 'Y' AND TABB.TYPE = 'G'
AND TABA.CID = TABB.CID)
02 CONDITION
TABLE TABB SHOULD HAVE MATCHING RECORDS IN TABLE TABC
TABB.PROD_NO = TABC.PROD_NO
03 IF ATLEAST ANY ONE COLUMN(TRAINS/CARS/BIKES) OF TABLE
TABA IS SET TO 'Y AND HAGING MATCHING RECORDS IN TABLE
TABB, ALL TABB'S RECORDS IN TURN HAVE MATCHING RECORDS IN TABC.
(LET US SAY TABB HAS 10 RECORDS FOR A PARTICULAR CID AND
TYPE FROM TABLE TABA. OUT OF WHICH ONLY 9 RECORDS ARE HAVING
MATCHES IN TABLE TABC. THEN THAT CID FROM TABLE TABA
SHOULD NOT BE PROCESSED.)
***********************************************************************/
declare
v_cnt number(10);
v_te number(10);
v_me number(10);
v_ge number(10);
v_status varchar2(10):='Y';
/***************************
TRAINS CARS BIKES
---------------------
Y Y Y
Y Y N
Y N N
N N N
N N Y
N Y Y
N Y N
Y N Y
S A G
-----------------
Y Y Y
Y Y N
Y N N
N N N
N N Y
N Y Y
N Y N
Y N Y
create table taba(cid number,TRAINS char(1),CARS char(1),BIKES char(1));
insert into taba values(1,'Y','Y','Y');
insert into taba values(2,'Y','Y','N');
insert into taba values(3,'Y','N','N');
insert into taba values(4,'N','Y','Y');
insert into taba values(5,'N','N','Y');
insert into taba values(6,'Y','N','Y');
insert into taba values(7,'N','Y','N');
insert into taba values(8,'N','N','N');
create table tabb(cid number,prod_no number,type char(1));
insert into tabb values(1,101,'S');
insert into tabb values(1,102,'A');
insert into tabb values(1,103,'G');
insert into tabb values(2,201,'S');
insert into tabb values(2,202,'A');
insert into tabb values(2,203,'G');
insert into tabb values(3,301,'S');
insert into tabb values(3,302,'A');
insert into tabb values(3,303,'G');
insert into tabb values(4,401,'S');
insert into tabb values(4,402,'A');
insert into tabb values(4,403,'G');
insert into tabb values(5,501,'S');
insert into tabb values(5,502,'A');
insert into tabb values(5,503,'G');
insert into tabb values(6,601,'S');
insert into tabb values(6,602,'A');
insert into tabb values(6,603,'G');
insert into tabb values(7,701,'S');
insert into tabb values(7,702,'A');
insert into tabb values(7,703,'G');
insert into tabb values(8,801,'S');
insert into tabb values(8,802,'A');
insert into tabb values(8,804,'G');
create table tabc(prod_no number,prod_type varchar2(10));
insert into tabc values(101,'BAN');
insert into tabc values(102,'BEN');
insert into tabc values(103,'BUN');
insert into tabc values(201,'DAN');
insert into tabc values(202,'DEN');
insert into tabc values(203,'DON');
insert into tabc values(301,'FAN');
insert into tabc values(302,'FAT');
insert into tabc values(303,'FEW');
insert into tabc values(401,'GEL');
insert into tabc values(402,'GET');
insert into tabc values(403,'GUN');
insert into tabc values(501,'HAM');
insert into tabc values(502,'HAT');
insert into tabc values(503,'HEN');
insert into tabc values(601,'LAN');
insert into tabc values(602,'LET');
insert into tabc values(603,'LUN');
insert into tabc values(701,'MAN');
insert into tabc values(702,'MEN');
insert into tabc values(703,'MOM');
insert into tabc values(801,'NET');
insert into tabc values(802,'NEW');
insert into tabc values(803,'NUN');
***************************/
begin
for c100 in (select
cid,TRAINS,CARS,BIKES
from taba a) loop
v_cnt :=0;
v_te :=0;
v_me :=0;
v_ge :=0;
if (upper(c100.TRAINS) = 'Y') then
select count(*) into v_cnt
from tabb a
where a.cid = c100.cid
and a.type = 'S';
if (v_cnt = 0) then
v_te:=v_te+1;
elsif (v_cnt > 0) then
select count(*) into v_cnt
from tabb a
where a.cid = c100.cid
and a.type = 'S'
and a.prod_no not in (select prod_no
from tabc);
if (v_cnt > 0) then
v_te:=v_te+1;
v_te:=v_te+1;
elsif (v_cnt = 0) then
dbms_output.put_line(' '||v_status);
end if;
end if;
end if;
if (upper(c100.CARS) = 'Y') then
select count(*) into v_cnt
from tabb a
where a.cid = c100.cid
and a.type = 'A';
if (v_cnt = 0) then
v_me:=v_me+1;
--v_num2:= v_num2 + 1;
--dbms_output.put_line('v_num2: '||v_num2 );
elsif (v_cnt > 0) then
select count(*) into v_cnt
from tabb a
where a.cid = c100.cid
and a.type = 'A'
and a.prod_no not in (select prod_no
from tabc);
if (v_cnt > 0) then
v_me:=v_me+1;
elsif (v_cnt = 0) then
dbms_output.put_line(' '||v_status);
end if;
end if;
end if;
if (upper(c100.BIKES) = 'Y') then
select count(*) into v_cnt
from tabb a
where a.cid = c100.cid
and a.type = 'G';
if (v_cnt = 0) then
v_ge:=v_ge+1;
elsif (v_cnt > 0) then
select count(*) into v_cnt
from tabb a
where a.cid = c100.cid
and a.type = 'G'
and a.prod_no not in (select prod_no
from tabc);
if (v_cnt > 0) then
v_ge:=v_ge+1;
elsif (v_cnt = 0) then
dbms_output.put_line(' '||v_status);
end if;
end if;
end if;
if
(
((upper(c100.TRAINS)='N') and
(upper(c100.CARS)='N') and
(upper(c100.BIKES)='N')) or
(
((upper(c100.TRAINS)='Y') and (v_te = 1)) and
((upper(c100.CARS)='Y') and (v_me = 1)) and
((upper(c100.BIKES)='Y') and (v_ge = 1))) or
(
((upper(c100.TRAINS)='Y') and (v_te = 1)) and
((upper(c100.CARS)='Y') and (v_me = 1)) and
((upper(c100.BIKES)='N'))) or
(
((upper(c100.TRAINS)='Y') and (v_te = 1)) and
((upper(c100.CARS)='N')) and
((upper(c100.BIKES)='N'))) or
(
((upper(c100.TRAINS)='N')) and
((upper(c100.CARS)='Y') and (v_me=1)) and
((upper(c100.BIKES)='Y') and (v_ge = 1))) or
(
((upper(c100.TRAINS)='N')) and
((upper(c100.CARS)='N')) and
((upper(c100.BIKES)='Y') and (v_ge = 1))) or
(
((upper(c100.TRAINS)='Y') and (v_te = 1)) and
((upper(c100.CARS)='N')) and
((upper(c100.BIKES)='Y') and (v_ge = 1))) or
(
((upper(c100.TRAINS)='N')) and
((upper(c100.CARS)='Y') and (v_me = 1)) and
((upper(c100.BIKES)='N')))) then
dbms_output.put_line(c100.cid||' FAILURE TO PROCESS RECORDS');
elsif (
(
((upper(c100.TRAINS)='Y') and (v_te = 0)) and
((upper(c100.CARS)='Y') and (v_me = 0)) and
((upper(c100.BIKES)='Y') and (v_ge = 0))) or
(
((upper(c100.TRAINS)='Y') and (v_te = 0)) and
((upper(c100.CARS)='Y') and (v_me = 0)) and
((upper(c100.BIKES)='N'))) or
(
((upper(c100.TRAINS)='Y') and (v_te = 0)) and
((upper(c100.CARS)='N')) and
((upper(c100.BIKES)='N'))) or
(
((upper(c100.TRAINS)='N')) and
((upper(c100.CARS)='Y') and (v_me = 0)) and
((upper(c100.BIKES)='Y') and (v_ge = 0))) or
(
((upper(c100.TRAINS)='N')) and
((upper(c100.CARS)='N')) and
((upper(c100.BIKES)='Y') and (v_ge = 0))) or
(
((upper(c100.TRAINS)='Y') and (v_te = 0)) and
((upper(c100.CARS)='N')) and
((upper(c100.BIKES)='Y') and (v_ge = 0))) or
(
((upper(c100.TRAINS)='N')) and
((upper(c100.CARS)='Y') and (v_me = 0)) and
((upper(c100.BIKES)='N'))) or
(
((upper(c100.TRAINS)='Y') and (v_te = 0)) and
((upper(c100.CARS)='N')) and
((upper(c100.BIKES)='N')))) then
dbms_output.put_line(c100.cid||' RECORDS PROCESSING SUCCESS');
end if;
end loop;
end;
declare
v_cnt number(10);
v_num1 number(10);
v_num2 number(10);
v_status varchar2(10):='Y';
/***************************
TRAINS CARS BIKES
-------------------
y y y
y y n
y n n
n y y
n n y
y n y
n y n
n n n
S A G
-------------------
1 1 1
1 1 0
1 0 0
0 1 1
0 0 1
1 0 1
0 1 0
0 0 0
create table taba(cid number,trains char(1),cars char(1),bikes char(1));
insert into taba values(1,'Y','Y','Y');
insert into taba values(2,'Y','Y','N');
insert into taba values(3,'Y','N','N');
insert into taba values(4,'N','Y','Y');
insert into taba values(5,'N','N','Y');
insert into taba values(6,'Y','N','Y');
insert into taba values(7,'N','Y','N');
insert into taba values(8,'N','N','N');
create table tabb(cid number,prod_no number,type char(1));
insert into tabb values(1,101,'S');
insert into tabb values(1,102,'A');
insert into tabb values(1,103,'G');
insert into tabb values(2,201,'S');
insert into tabb values(2,202,'A');
insert into tabb values(2,203,'G');
insert into tabb values(3,301,'S');
insert into tabb values(3,302,'A');
insert into tabb values(3,303,'G');
insert into tabb values(4,401,'S');
insert into tabb values(4,402,'A');
insert into tabb values(4,403,'G');
insert into tabb values(5,501,'S');
insert into tabb values(5,502,'A');
insert into tabb values(5,503,'G');
insert into tabb values(6,601,'S');
insert into tabb values(6,602,'A');
insert into tabb values(6,603,'G');
insert into tabb values(7,701,'S');
insert into tabb values(7,702,'A');
insert into tabb values(7,703,'G');
insert into tabb values(8,801,'S');
insert into tabb values(8,802,'A');
insert into tabb values(8,803,'G');
create table tabc(prod_no number,type varchar2(10),);
insert into tabc values(101,'BAN');
insert into tabc values(102,'BEN');
insert into tabc values(103,'BUN');
insert into tabc values(201,'DAN');
insert into tabc values(202,'DEN');
insert into tabc values(203,'DON');
insert into tabc values(301,'FAN');
insert into tabc values(302,'FAT');
insert into tabc values(303,'FEW');
insert into tabc values(401,'GEL');
insert into tabc values(402,'GET');
insert into tabc values(403,'GUN');
insert into tabc values(501,'HAM');
insert into tabc values(502,'HAT');
insert into tabc values(503,'HEN');
insert into tabc values(601,'LAN');
insert into tabc values(602,'LET');
insert into tabc values(603,'LUN');
insert into tabc values(701,'MAN');
insert into tabc values(702,'MEN');
insert into tabc values(703,'MOM');
insert into tabc values(801,'NET');
insert into tabc values(802,'NEW');
insert into tabc values(803,'NUN');
***************************/
begin
for c100 in (select
cid,trains,cars,bikes
from taba a) loop
v_cnt :=0;
v_num1 :=0;
v_num2 :=0;
if (upper(c100.trains) = 'Y') then
select count(*) into v_cnt
from tabb a
where a.cid = c100.cid
and a.type = 'S';
v_num1:= v_num1 + 1;
if (v_cnt = 0) then
v_num2:= v_num2 + 1;
elsif (v_cnt > 0) then
select count(*) into v_cnt
from tabb a
where a.cid = c100.cid
and a.type = 'S'
and a.prod_no not in (select prod_no
from tabc);
if (v_cnt > 0) then
v_num2:= v_num2 + 1;
elsif (v_cnt = 0) then
dbms_output.put_line(' '||v_status);
end if;
end if;
end if;
if (upper(c100.cars) = 'Y') then
select count(*) into v_cnt
from tabb a
where a.cid = c100.cid
and a.type = 'A';
v_num1:= v_num1 + 1;
if (v_cnt = 0) then
v_num2:= v_num2 + 1;
elsif (v_cnt > 0) then
select count(*) into v_cnt
from tabb a
where a.cid = c100.cid
and a.type = 'A'
and a.prod_no not in (select prod_no
from tabc);
if (v_cnt > 0) then
v_num2:= v_num2 + 1;
elsif (v_cnt = 0) then
dbms_output.put_line(' '||v_status);
end if;
end if;
end if;
if (upper(c100.bikes) = 'Y') then
select count(*) into v_cnt
from tabb a
where a.cid = c100.cid
and a.type = 'G';
if (v_cnt = 0) then
v_num2:= v_num2 + 1;
elsif (v_cnt > 0) then
select count(*) into v_cnt
from tabb a
where a.cid = c100.cid
and a.type = 'G'
and a.prod_no not in (select prod_no
from tabc);
if (v_cnt > 0) then
v_num2:= v_num2 + 1;
elsif (v_cnt = 0) then
dbms_output.put_line(' '||v_status);
end if;
end if;
end if;
if ((v_num1 > 0) and (v_num1 > v_num2))
then
dbms_output.put_line(c100.cid||' RECORDS PROCESSING SUCCESS');
elsif ((v_num1 = 0) or (v_num1 = v_num2)) then
dbms_output.put_line(c100.cid||' FAILURE TO PROCESS RECORDS');
end if;
end loop;
end;
|
|
