Forum Stats

  • 3,825,248 Users
  • 2,260,487 Discussions
  • 7,896,466 Comments

Discussions

Parameterised Views

ApexBine
ApexBine Member Posts: 153 Silver Badge
edited Jan 15, 2015 5:45AM in Database Ideas - Ideas

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'


which would save me from inventing any workarounds.

user7023476user55363562817563user13075257
4 votes

Active · Last Updated