CODEWITHSUNDEEP
sqloracle-database
John Wick
John Wick

Reputation: 745

View with input parameters

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 :(

Upvotes: 0

Views: 204

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

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).

Upvotes: 2

Related Questions