Custom Search
www.rocket99.com : Technical Guides Sybase Oracle UNIX Javascript


Technical Guides
Sybase
Oracle
UNIX
Javascript




Of Interest

Business Intelligence and Analytics



Oracle Training





Oracle » PL-SQL » Coding »

Complex IF/THEN Processing

     





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;















Oracle : Related Topics

Oracle : PL-SQL : Creating a stored procedure
Oracle : PL-SQL : Displaying output
Oracle : PL-SQL : Output variables
Oracle : PL-SQL : Arrays and structures
Oracle : PL-SQL : Conditionals
Oracle : PL-SQL : Looping in PL-SQL
Oracle : PL-SQL : Packages
Oracle : PL-SQL : Exception Handling
Oracle : PL-SQL : Sleep and Wait
Oracle : PL-SQL : Date Manipulation
Oracle : PL-SQL : Parallel Query Processing
Oracle : PL-SQL : Exit if error, in a SQL script
Oracle : PL-SQL : Cursors
Oracle : PL-SQL : Creating triggers
Oracle : PL-SQL : Ranking Data
Oracle : PL-SQL : How to do an outer join in Oracle
Oracle : PL-SQL : Loops in PL-SQL : Hierarchy

Sybase Web Site
Sybase iAnywhere Mobile Web Site
Oracle Enterprise Web Site



Get the latest Rocket99 news and tech tips via






Site Index About this Guide to Sybase, Oracle, and UNIX Contact Us Advertise on this site




Copyright © 2016 Stoltenbar Inc All Rights Reserved.