Re: Newbie SQL question

Sorry but this is not class work, I merely stated the problem with a simplified
example so that it would be easier for somebody with more SQL experience than
me to understand the gist of the problem I'm trying to convey. My background is
not with Oracle, but with another database that has it's own query language but is much
syntactically different than Oracle SQL.

I have looked at Oracle SQL books but none of the examples deal with effective dated
outer joins.

If you can provide a concrete example that works using this test data I would appreciate
the help and my project would appreciate it too.

Thanks.
Richard.

"DA Morgan" <damorgan_at_exesolutions.com> wrote in message news:3E580485.A11ACD0D_at_exesolutions.com...
> Richard Olivier wrote:
>
> > I have two tables, a customer table and a region table, I would like to
get
> > a count of the number
> > of customers in each region and I would like to display the region
> > description
> >
> > Customer table:
> > SETID CUSTID REGION
> > SHARE 0001
> > SHARE 0002
> > SHARE 0003 A
> > SHARE 0004 B
> >
> > Subcust Table (Region):
> > SETID REGION DESCR EFFDT
> > SHARE A Region A 01-JAN-1900
> > SHARE B Region B 01-JAN-1900
> >
> > The SQL I am using is below. The result I am getting is as follows:
> >
> > Count Region Descr
> > 1 A Region A
> > 1 B Region B
> >
> > I would Like to see:
> >
> > Count Region Descr
> > 2
> > 1 A Region A
> > 1 B Region B
> >
> > In other words, I need to see how many customers don't have a region
> > assigned to them.
> >
> > Why is this outer join not returning all the records in the customer
table?
> >
> > select distinct count(*),a.subcust_qual2,b.descr from ps_customer a,
> > ps_subcust_q2_tbl b
> > where a.setid = b.setid (+)
> > and a.subcust_qual2 = b.subcust_qual2 (+)
> > and b.effdt = (select max(effdt) from ps_subcust_q2_tbl
> > where b.setid = setid
> > and b.subcust_qual2 = subcust_qual2
> > and effdt <= sysdate)
> > group by a.subcust_qual2,b.descr
> > order by a.subcust_qual2
>
> This is classword and you should identify it as such.
>
> You can do this in a number of ways using subqueries.
>
> To research the many possible ways to do this look at your class notes and
open
> your textbook.
>
> Daniel Morgan
>
>
Received on Sun Feb 23 2003 - 14:32:52 CST