Re: This year vs Last year queries

Thanks Thomas!
Two more question (for you or anyone who can help :)

What I didn't mention in my previous post was that the year to year query was imbedded in a view (I use it for web queries returning data directly into ms excel)

So, question #1:
  Since I don't think I can pass the dates into the sub-selects in a view,
  do I have to convert the view to a stored procedure (and if I do, can   I make that work by passing in parameters?)

Question #2:
  I am also trying to return percentage growth etc but I am getting   division by zero (which you would expect in some lost sales scenarios.)
  Is there some why to have it return some value instead of failing and   returning no rows.
  < to_char(sum(margin99/sell99)*100,'99.9') gppct > fails when sell99 is
  zero (we lost that sale.)

Again, thanks for any help.

Kevin Skaalrud.

Thomas Kyte wrote:
>
> A copy of this was sent to Kevin Skaalrud <kevin_skaalrud_at_neptune.serca.com>
> (if that email address didn't require changing)
> On Mon, 20 Jul 1998 11:50:12 -0700, you wrote:
>

[year to year query question -- snipped]
>
> assuming the table looks something like:
>
> create table T ( custno number, descript varchar2, sale_amount number, sale_date
> );
>
> Then a query such as:
>
> select custno, descript, sum(ty_sales) ty_sales, sum(ly_sales) ly_sales,
> sum(ty_sales-ly_sales) diff
> from
> (
> select custno, descript, sale_amount ty_sales, 0 ly_sales
> from T
> where sale_date between to_date( '....' ) and to_date( '....' )
> union all
> select custno, descript, 0, sale_amount
> from T
> where sale_date between to_date( '....' ) and to_date( '....' )
> )
> group by custno, descript
> /
>
> will do it as long as the descript field is constant for all of the same values
> of custno...
>
> >Thanks in advance.
> >
> >Kevin Skaalrud
>
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Government
> Herndon VA
>
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
>
> Anti-Anti Spam Msg: if you want an answer emailed to you,
> you have to make it easy to get email to you. Any bounced
> email will be treated the same way i treat SPAM-- I delete it.
Received on Mon Jul 20 1998 - 18:07:05 CDT