Re: Help ! Very challenging SQL

From: <dba2adm_at_yahoo.com>
Date: 4 Jun 2004 12:18:03 -0700
Message-ID: <903bd22e.0406041118.41f239e5@posting.google.com>

Thank you all for your response.

Is it possible to extend the same, when I have one more case to test. Something like the one below

select mst.*,
case when mst.dt1 = dat.dt and dat.ind =1 then 1 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 ..

Table DAT

Dt  Date --has all dates for five years
ind int  --says whether its a holiday or not etc
         --ex ind = 1 working day, ind = 2 fed holiday ..

Table MST

dt1 date
dt2 date
fld1 int ..

What I am looking for is,

*if mst.dt1 = dat.dt and ind = 1 then return 1 (ie, if the dt1 is in dat table with ind = 1 then return 1) *if mst.dt1 < mst.dt2 then return the number of working days between mst.dt1 and mst.dt2
(ie count(*) from dat where dt between mst.dt1 and mst.dt2 and ind = 1)
*else return 0

ie. select mst.* , dat.result of the above-case from mst, dat ...

dba2adm_at_yahoo.com wrote in message news:<903bd22e.0406040701.36ae540e_at_posting.google.com>...
> 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 - 14:18:03 CDT

Re: Help ! Very challenging SQL - Oracle/Teradata