Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Parameterised Views

ApexBineJan 15 2015 — edited Jan 15 2015

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.

Comments
Post Details
Added on Jan 15 2015
0 comments
103 views