outer join while selecting max date
636309Apr 5 2013 — edited Apr 5 2013Hi,
Oracle 11g R2.
I'd like to generate a query which outer joins between 2 related tables, but only selects the max date if more than 1 record is returned for a given customer. Here are the tables that I have and some records.
create table customer (custnum number, name varchar2(100))
create table orders (ordnum number, custnum number, orddate date)
insert into customer values(1, 'ABC')
insert into customer values(2, 'DEF')
insert into customer values(3, 'ABC')
insert into customer values(4, 'DEF')
insert into orders values (1, 1, sysdate)
insert into orders values (2, 1, sysdate + 1)
insert into orders values (3, 2, sysdate + 2)
I've created the following query, but it returns both dates for custnum = 1. I'd like to display the customer name regardless if he has an order. But if there's more than order, I'd like to display the record with the max date.
select a.custnum, a.name, b.orddate
from customer a, orders b
where a.custnum = b.custnum(+)
and b.orddate(+) <= sysdate + 10 --addtional criteria as part of edit
This is a simplified example of a more complex query I'm trying to solve. If at all possible, could you please suggestion a query without the use of analytic functions or inline views?
Thanks!!
Edited by: arizona9952 on Apr 4, 2013 10:05 PM