Help ! Very challenging SQL - Oracle/Teradata

From: <dba2adm_at_yahoo.com>
Date: 4 Jun 2004 01:14:30 -0700
Message-ID: <903bd22e.0406040014.20292d8b@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 - 03:14:30 CDT