Skip navigation

Parameterized Views

score 750
You have not voted. Delivered

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

Comments

Vote history