Re: This year vs Last year queries

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 21 Jul 1998 19:46:27 GMT
Message-ID: <35beef09.25061156@192.86.155.100>

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 16:07:05 -0700, you wrote:

>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?)
>

Ok, if it has to be buried in a view, we need to create a parameterized view. This is a little tricky but very useful. It might go something like this:

create or replace package query_parms
as

    g_this_year_start date;
    g_this_year_stop  date;
    g_last_year_start date;
    g_last_year_stop  date;

    function get_date( p_which in varchar2 ) return date;     pragma restrict_references( get_date, wnds, rnds, wnps ); end;
/

create or replace package body query_parms as
function get_date( p_which in varchar2 ) return date is

    l_which varchar2(25) default lower(p_which); begin

    if    ( l_which = 'this_year_start' ) then return g_this_year_start;
    elsif ( l_which = 'this_year_stop' ) then return g_this_year_stop;
    elsif ( l_which = 'last_year_start' ) then return g_last_year_start;
    elsif ( l_which = 'last_year_stop' ) then return g_last_year_stop;

    else return NULL;
    end if;
end;

end;
/

So, now we have some pl/sql that we can call to setup 4 parameters -- the parameters for our view. Now we need to roll them into the view. You can do that as such:

create or replace view T_View
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, ( select distinct query_parms.get_date('this_year_start') p_start,

                            query_parms.get_date('this_year_stop')  p_stop
              from dual )

 where sale_date between p_start and p_stop  union all
select custno, descript, 0, sale_amount   from T, ( select distinct query_parms.get_date('last_year_start') p_start,

                            query_parms.get_date('last_year_stop')  p_stop
              from dual )

 where sale_date between p_start and p_stop )
group by custno, descript
/

I use the (select distinct PL/SQL FUNCTIONS from dual) as a way to force a temp result set to be built -- this makes it so that the pl/sql functions are called once per query, not once per row per query (making this pretty efficient as well)...

Now in sqlplus you can do something like:

SQL> exec query_parms.g_this_year_start := to_date('01-JAN-81') SQL> exec query_parms.g_this_year_stop := to_date('31-DEC-81')

SQL> exec query_parms.g_last_year_start := to_date('01-JAN-82') SQL> exec query_parms.g_last_year_stop := to_date('31-DEC-82')

SQL> select * from T_View;

In the web (assuming pl/sql cartridge with the webserver) you would just add pl/sql to set the query_parms package before you opened your cursor on the view...

>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.)
>

decode( sell99, 0, to_char(NULL), to_char(sum(margin99/sell99)*100,'99.9') )

will do it

>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]
>>

[snip]  

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 Tue Jul 21 1998 - 14:46:27 CDT