I don't have such a table... Your first assumption is along what I
want. This is another question/problem I had.... I don't know how to
have a PL/SQL procedure that returns a resultset unless the resultset
comes directly rom a SQL. I don't know how to get a resultset that I
"made" (such as what you desribed under solution 1 in using a loop to
get my dates) returned. Can someone provide a quick example of this?
"Guido Konsolke" <Guido.Konsolke_at_triaton.com> wrote in message news:<1048231163.408940_at_news.thyssen.com>...
> "Luch" wrote...
> > I would like to have a resultset that has every day in a certain range
> > appear at least once, in conjuction with my table called t_history which
> > will have records for some days.
> >
> > For example:
> > Table t_history has fields mydate(date) and count(integer).
> >
> > t_history has these records:
> >
> > March 10, 2003 5
> > March 13, 2003 3
> > March 15, 2003 1
> >
> >
> > I'd like to use a SQL with a range of March 10-15 and have the following
> > resultset returned:
> > March 10, 2003 5
> > March 11, 2003 0
> > March 12, 2003 0
> > March 13, 2003 3
> > March 14, 2003 0
> > March 15, 2003 1
> >
> > The zero's can be nulls as well, I don't care.
> >
> > In a way, what I would like is an outer join to a table that had a record
> > for every single day. Any ideas how I can go about this?
> >
> > --
> > Luch
> > replace nospam with hotmail for e-mail.
>
> Hi Luch,
>
> sorry, I don't see the problem. Let's have a guess:
> 1. You don't have a table with a row for every day in a period?
> Well, get one by using a PL/SQL loop.
> 2. You have such a table but don't know how to join?
> Sounds like homework. Will be something like
> SELECT tabledat.dat, sum(val) FROM tabledat, tablevalue
> WHERE tabledat.dat = tablevalue.dat(+)
> GROUP BY tabledat.dat;
> You will have to use the TRUNC function on the date columns.
> To show '0' instead of NULL, use the NVL function on the SUM.
>
> If my assumptions are wrong, give some more details.
>
> hth,
> Guido
Received on Fri Mar 21 2003 - 08:31:24 CST
