Re: Help ! Very challenging SQL - Oracle/Teradata

Thank you, that looks much better.
ANSI seems to be so much more
flexible.

My estimate of 2nd best was clearly
far too optimistic ;) I had been struggling with how to keep the zero counts in.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


"VC" <boston103_at_hotmail.com> wrote in message
news:toZvc.43839$eY2.30140_at_attbi_s02...


> Hi,

>

>

> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message

> > I still have to think hard about ANSI joins

> > so it may be wrong, or second best.

> >

> > select mst.dt1, mst.dt2, nvl(sum(flg),0)

> > from

> >  mst

> > left outer join

> >  (select dt,1 flg from dat where ind = 1)

> > on

> >  dt between dt1 and dt2

> > group by

> >  mst.dt1, mst.dt2

> > /

>

> It can be simplified a bit:

>

> select mst.*, count(dt)

> from mst left join dat

>    on dt between dt1 and dt2 and ind=1

> group by dt1,dt2;

>

> Regards.

>