Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Help ! Very challenging SQL - Oracle/Teradata

Re: Help ! Very challenging SQL - Oracle/Teradata

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 4 Jun 2004 12:57:28 +0000 (UTC)
Message-ID: <c9prjo$ns0$1@titan.btinternet.com>

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.
>
Received on Fri Jun 04 2004 - 07:57:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US