Parameterized Views

ApexBine

I have query like this

SELECT

isin,

MAX(price_date)                                       AS price_date,

MAX(price) KEEP (DENSE_RANK LAST ORDER BY price_date) AS price

FROM share_price

WHERE trunc(price_date,'DD') <= to_date('30.11.2014','DD.MM.YYYY')

GROUP BY isin;

and I need it every month, so I would like to put it in a view.

Selecting from that view using an ISIN in the where clause is no problem, but as the reporting date changes every month, it would be great to have a date parameter that I can pass to the view when calling it.

So instead of using a fixed date, I would declare:

CREATE VIEW share_price_date AS

SELECT

isin,

MAX(price_date)                                       AS price_date,

MAX(price) KEEP (DENSE_RANK LAST ORDER BY price_date) AS price

FROM share_price

WHERE trunc(price_date,'DD') <= :reporting_date

GROUP BY isin;

A plain SELECT would then look like

SELECT isin, price_date, price

FROM share_price_date (to_date('30.11.2014','DD.MM.YYYY'))

WHERE isin = 'DE123456789022'

and would save me from inventing any workarounds.

I have similar requirements for views that contain even more time intervals and 'count() over' functions.

Update: this has been implemented in Oracle 20c:

CREATE OR REPLACE FUNCTION budget(job varchar2) RETURN VARCHAR2 SQL_MACRO IS

BEGIN

  RETURN q'{

  select deptno, sum(sal) budget

    from emp

      where job = budget.job

        group by deptno

  }';

END;

/

SELECT * FROM budget ('MANAGER');

    DEPTNO     BUDGET 

---------- ---------- 

        20       2975 

        30       2850 

        10       2450

Geert Gruwez

bad for performance.

your where clause negates the use of an index on column price_date

unless you have create a function index ?

top.gun

So your idea wold be a view functioning like a function, instead of the conventional way which is something like this?

SELECT isin, price_date, price

FROM share_price_date_view

WHERE isin = 'DE123456789022'

   AND trunc(price_date,'DD') <= to_date(:reporting_date,'DD.MM.YYYY');

ApexBine

bad for performance.

your where clause negates the use of an index on column price_date

unless you have create a function index ?

Just assume the date has no time part then we don't need the trunc.

Geert Gruwez

Just assume the date has no time part then we don't need the trunc.

Assumptions are the mother of all screwups !

Never make assumptions. Those are open invitations for Murphy, and the cool thing about Murphy ... He never fails to show up.

ApexBine

So your idea wold be a view functioning like a function, instead of the conventional way which is something like this?

SELECT isin, price_date, price

FROM share_price_date_view

WHERE isin = 'DE123456789022'

   AND trunc(price_date,'DD') <= to_date(:reporting_date,'DD.MM.YYYY');

I'm not sure if we have the same understanding here.

The parameter I use in my 'view definition' is used for finding the max date before the given date.

My select

SELECT isin, price_date, price

FROM share_price_date (to_date('30.11.2014','DD.MM.YYYY'))

WHERE isin = 'DE123456789022'

would return one line like

'DE123456789022', '28.11.2014', 67.89

ApexBine

Assumptions are the mother of all screwups !

Never make assumptions. Those are open invitations for Murphy, and the cool thing about Murphy ... He never fails to show up.

Your remark is absolutely valid but I think we are digressing from the actual suggestion.

William Robertson

In this example I'm not sure why you couldn't just expose the date in the view's SELECT list. However I can see it could be useful in more complex cases, for example:

select a, b, c from   sometab t connect by prior t.id = t.parent_idstart with t.id = 123

When this is part of a view, there is currently no way to expose the 'start with' predicate to allow it to be reused for other values of t.id (or if there is, it's complicated and error-prone).

I think the syntax would need an explicitly named and typed parameter similar to functions:

create view share_price_date (dateparam date) as ...

This could even be extended to WITH clauses:

with cte (dateparam date) as
     ( select b.c
       from   blahblah b
       where  b.d >= dateparam -1 )select * from cte (sysdate);

Edit: ...although not this actual syntax, because it already exists (without the datatypes) for specifying the column names.

ctrieb

Good idea, I think this wiil help to write better code.

ApexBine

Good idea, I think this wiil help to write better code.

Thanks Christian,

indeed, I think it would help encapsulating logic in a way that it could easily be re-used in different contexts.

If my package uses that view and logs some error message, for instance, the specialist could use the underlying view for checking the issue.

berx

maybe you can use this example to implement what you want to do already?

create table Blume as

select rownum rn, dbms_random.value num

from

        dual

connect by

        level <=1000;

--  grant EXECUTE on DBMS_SESSION to &user;

--  grant create any context to &user;

-- some code borrowed from http://www.adp-gmbh.ch/ora/sql/create_context.html

CREATE CONTEXT BIENE using Wiese;

create or replace package Wiese as

    procedure set_value_in_context(some_value in varchar2);

end Wiese;

/

create or replace package body Wiese as

    procedure set_value_in_context(some_value in varchar2) is

    begin

        dbms_session.set_context('BIENE', 'random_filter', some_value);

    end set_value_in_context;

end Wiese;

/

create view Rasenmaeher as

select count(*) Bluemchen

from Blume b

where b.num >

     to_number(sys_context('BIENE', 'random_filter') );

exec Wiese.set_value_in_context(to_char(42/100));

select sys_context('BIENE', 'random_filter') from dual;

select * from Rasenmaeher;

-- 572 in my testcase

exec Wiese.set_value_in_context(to_char(99/100));

select * from Rasenmaeher;

-- 4 in my testcase