Hi,
I have a requirement to define a specific where clause when a condition is true and another when the condition is false....
Is this possible????
For example:
create table x
(req_id number(2),
req_line_id number(3),
epp varchar2(5),
col1 varchar2(20))
;
alter table x
add constraint x_pk primary key(req_id,req_line_id,epp);
create table y
(req_id number(2),
req_line_id number(3),
epp varchar2(5),
period varchar2(5))
;
alter table y
add constraint y_pk primary key(req_id,req_line_id,epp,period);
insert into x values(1,1,'10100','1.1');
insert into x values(1,2,'10500','1.2');
insert into x values(1,3,'10100','1.3');
insert into x values(2,3,'10100','2.1');
insert into x values(2,1,'20100','2.1');
insert into x values(2,2,'20100','2.2');
insert into y values(1,0,'10500','11/10');
insert into y values(1,0,'10900','12/10');
insert into y values(1,1,'10100','01/10');
insert into y values(1,2,'10100','01/10');
insert into y values(1,3,'10101','01/10');
insert into y values(2,0,'10100','05/10');
insert into y values(2,1,'10200','03/10');
insert into y values(2,1,'10201','08/10');
The condition is as follows...:
if the column 'req_line_id' of table y =0 then
the condition between the two tables should be:
x.req_id=y.req_id
and x.epp=y.epp
but when the the value of the column 'req_line_id' of table y =!0 then
the condition between the two tables should be:
x.req_id=y.req_id
and x.req_line_id=y.req_line_id
as in the below sql stmt:
SQL> select x.col1,
2 y.period
3 from x
4 join y
5 on x.req_id=y.req_id
6 and x.req_line_id=y.req_line_id
7 union
8 select x.col1,
9 y.period
10 from x
11 join y
12 on x.req_id=y.req_id
13 and x.epp=y.epp
14 and y.req_line_id=0
15 ;
COL1 PERIOD
-------------------- ------
1.1 01/10
1.2 01/10
1.2 11/10
1.3 01/10
2.1 03/10
2.1 05/10
2.1 08/10
Because the real stmt statement is much more difficult i want to avoid the union operator....
Is there any alternative..????
Note: I use db10g v.2,
Thank you
Sim