Re: Help ! Very challenging SQL - Oracle/Teradata

Hi,

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:c9per3$6ne$1_at_titan.btinternet.com...
>
> If you can't do scalar subqueries in Teradata,
> I guess you'll have to do an outer cartesian
> join (to get the zero count) between the tables
> and group by every column in MST.
>
> For example:
>
> select mst.dt1, mst.dt2, nvl(sum(flg),0)
> from
> mst,
> (select dt,1 flg from dat where ind = 1)
> where
> dt(+) between dt1 and dt2
> group by
> mst.dt1, mst.dt2
> ;
>
> I think the Oracle syntax for the outer join
> above turns into the following ANSI, but
> 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.

VC

>
> --
> Regards
>
> Jonathan Lewis
>
Received on Fri Jun 04 2004 - 06:52:57 CDT