Re: Help ! Very challenging SQL - Oracle/Teradata

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