View with input parameters

Question

Let's say that I have a view with the following structure (and sample data):

Sample_Name     Sample_Date         Sample_ID
John            01-01-2015          453
Jacob           10-10-2016          777
Jingle          11-11-2017          888
Heimer          12-12-2018          999
Schmidt         07-07-2019          333

If someone specifies the year (i.e. '2019'), I want to return that year and the year prior to it. So the following data set would return:

Sample_Name     Sample_Date         Sample_ID
Heimer          12-12-2018          999
Schmidt         07-07-2019          333

Is there any way for me to create the view with input parameters (similar to a function), where the user can specify what date they are looking for? Wen the date is specified, the view should return data from that year and the year prior to it. I know with functions you can do something similar but according to my google search, this isn't possible in Oracle :(

Littlefoot · Accepted Answer · 2020-01-14 20:03:42Z

2

It doesn't make sense.

A view is just a stored query. If you want to fetch certain data from it, use a where clause, e.g.

select sample_name, sample_date, sample_id
from your_view
where extract (year from sample_date) >= :par_year - 1;

(where you'd enter 2019 into :par_year, when prompted) (: might need to be replaced by &, depending on a tool you use).

answered Jan 14, 2020 at 20:03

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

View with input parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related