Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

outer join while selecting max date

636309Apr 5 2013 — edited Apr 5 2013
Hi,
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
This post has been answered by jeneesh on Apr 5 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 3 2013
Added on Apr 5 2013
5 comments
2,506 views