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