Thanks Jonathan,
Yesterday after few tries I came up with the following sql, I am not
sure this is correct.
select mst.*, sum(case when dt1 < dt2 and dt between mst.dt1 and
mst.dt2 and ind = 1 then 1 else 0 end) as dt from mst, dat group by
mst.dt1, mst.dt2.
I also tried the left outer join, but my logic was wrong.
Now, how do I extend your logic to include another "when" inside the
case. something similar to the following.
select mst.*,
case
WHEN mst.dt1 = dat.dt and dat.ind = 1 THEN 1 -> if dt1 is there
and ind = 1 return 1
when dt1 < dt2
then (select count(*) from dat where ind = 1 and dt between
mst.dt1 and
mst.dt2)
else 0
end as ct from mst
PS. The "dt" column in dat is the PK. It basically has all the dates
for a few years with different indicator (such as working day, company
holiday, federal holiday etc)
Note: After fighting a while I looked at the teradata forum and the
manuals for creating an UDF. It seems the udfs (external programs
written in c) they have, doesn't allow SQL calls. I am not sure yet.
Regards.
-Dave
"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
> /
>
>
> The only other alternative is to check if
> teradata can call a function inside a SQL
> statement, and use that to return the count
> of dates - avoiding the massive sort is
> probably a good idea.
>
>
>
> --
> 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
>
>
> <dba2adm_at_yahoo.com> wrote in message
> news:903bd22e.0406040014.20292d8b_at_posting.google.com...
> > Dear Group,
> >
> > I have the following SQL which I want to rewrite. I am involved in a
> > oracle/terradata integration project and needs to convert some sqls
> > from teradata to oracle and vice versa. The later seems to be very
> > complex when it comes to scalar queries.
> >
> > The following works fine in Oracle (and DB2).
> >
> > create table dat (dt date, ind int);
> >
> > create table mst (dt1 date, dt2 date) ;
> >
> > insert into dat select trunc(sysdate) - 11 + rownum, decode
> > (mod(rownum,5),0,0,1) from all_tables where rownum < 21
> >
> > insert into mst select dt, dt + trunc(dbms_random.value(1,20)) from
> > dat ;
> >
> > update mst set dt1 = dt2 + 1 where rownum = 1 ;
> >
> >
> > select mst.*, case when dt1 < dt2
> > then (select count(*) from dat where ind = 1 and dt between mst.dt1
> > and mst.dt2)
> > else 0
> > end as ct from mst
> > --where ..
> >
> > My requirement is that I have to find another way to rewrite this
> > query. I have been struggling with this query for more than a day now.
> > I have to get the same result without using scalar sub query.
> >
> > Basically, I have to get the,
> > "count(*) from dat where the dt column is between mst.dt1 and mst.dt2
> > else I have to get 0"
> >
> > Note: there is no direct relation between dat table and mst table.
> > "dat" table is a table with a date column and an indicator column.
> >
> > Note, teradata doesn't allow the following forms.
> >
> > 1, select col1, col2, (SELECT bld bla.. from .. where ..) as col3 from
> > ..
> > 2, select col1, col2, case when cond then (SELECT ...) ...
> >
> > Thanks,
> > -Dave
Received on Fri Jun 04 2004 - 10:01:04 CDT
