Hi,
I would to display all rows in a query for two filter conditions on 'status'. I have used an outer join on the outer query to return the values inside the inline query but this does not return back all rows.
create table test(
ids number,
start_date date,
status varchar2(1),
amount number,
cust_type number
);
insert into test values (001, '01-JUN-2014', 'M', 189, 78);
insert into test values (001, '26-MAR-2014', 'R', 175, 4);
insert into test values (001, '01-DEC-2014', 'R', 89, 91);
insert into test values (001, '13-AUG-2014', 'J', 19, 2);
insert into test values (001, '12-AUG-2014', 'E', 19, 2);
insert into test values (002, '01-JAN-2014', 'R', 17, 4);
insert into test values (002, '26-JUN-2014', 'R', 175, 4);
insert into test values (002, '01-FEB-2014', 'J', 9, 8);
insert into test values (002, '13-OCT-2014', 'M', 190, 2);
insert into test values (003, '01-JUN-2014', 'J', 189, 78);
insert into test values (003, '26-MAR-2014', 'R', 175, 4);
insert into test values (003, '01-DEC-2014', 'R', 89, 91);
insert into test values (003, '13-AUG-2014', 'J', 19, 2);
insert into test values (003, '12-AUG-2014', 'J', 19, 2);
commit;
select i.ids, i.start_date, i.cust_type, i.amount as gross_amount, i.amount + ii.amount as net_amount
from test i,
(select ids, amount
from test
where (status = 'R' or status = 'J')) ii
where i.cust_type in(78, 4, 91, 2, 4, 8)
and i.ids(+) = ii.ids
and i.status(+) in('M', 'E')
and i.start_date between '01-jan-14' and '31-dec-2014'
The above query excludes ids 003 as there are no status (M or E) butI would still like to display all ids even if the condition does not meet