Re: select WEEKNUM, SUM(whatever) ... => all weeks

Hi, Jenda.

        This is not a very elegant solution. Hopefully, the gurus out there will have a better way for you. If not, this should work:

        Make another table that just has all the week numbers 1 to 52 and use an outer join with this table.

 select WEEKNUM, SUM(VALUE)
 from That_table, All_Weeks
 where WEEKNUM >= WEEK_SINCE and WEEKNUM <= WEEK_TILL  and All_Weeks.WEEK_NO = That_table.WEEKNUM(+)  GROUP_BY WEEKNUM
 ORDER BY WEEKNUM -Jeff

On Fri, 24 Sep 1999 17:57:37 GMT, Jenda_at_Krynicky.cz (Jenda Krynicky) wrote:

>I have a table like :
>
> WEEKNUM number;
> VALUE number;
> FOO bar;
> BAZ bar;
> ...
>
>I do a statement like this :
>
> select WEEKNUM, SUM(VALUE)
> from That_table
> where WEEKNUM >= WEEK_SINCE and WEEKNUM <= WEEK_TILL
> GROUP_BY WEEKNUM
> ORDER BY WEEKNUM
>
>I then define a cursor for this select and process and print the values.
>
>The problem is that if there is no row for a certain week I do not get (TheWeek, 0).
>
>So of course if I blindli go and print "Sum|" for each row from the cursor I do not get
> "23|0|15" but "23|15". Which is useless.
>
>Is there an easy way to ensure the select returns a row for each week in the range.
>I don't mind if its 0 or NULL, but I would not like to have to check for the spaces.
>
>Thanks a lot, Jenda
>http://Jenda.Krynicky.cz
>
Received on Fri Sep 24 1999 - 13:24:25 CDT