Re: Help ! Very challenging SQL - Oracle/Teradata

Can I be able to do, something like

select mst.*, 1 from mst mst join dat dat on dt = dt1 and ind=1 union all
(
select mst.*, count(dt) from mst left join dat dat on dt between dt1 and dt2 and ind=1
where not exists <the query above union all> group by dt1,dt2
) ;

I couln't get it correct. I tried "NOT IN" as below instead of not exists. I am not sure whether I am getting the correct results.

--where (mst.dt1, mst.dt2) not in (select dt1, dt2 from mst, dat where dt = dt1 and ind=1)

Thanks.
"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
> 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 - 14:54:04 CDT