mahjag
asked on
create a parametrized view
Hi Oracle Gurus
I have a view created in oracle that it runs from bigger base tables and user who is going to consume the data wants to filter that view based on start dates like '01-JAN-2012' or '01-FEB-2012' for their analysis - how would I get the user input start date and filter the record using that date - I did modify the view to run the start dates for them everytime but since they will be using this often they wanted to use it as parameter to pass and get the results.
I have a view created in oracle that it runs from bigger base tables and user who is going to consume the data wants to filter that view based on start dates like '01-JAN-2012' or '01-FEB-2012' for their analysis - how would I get the user input start date and filter the record using that date - I did modify the view to run the start dates for them everytime but since they will be using this often they wanted to use it as parameter to pass and get the results.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi slightwv
Are you saying the someother table will get the user input start dates? -
Are you saying the someother table will get the user input start dates? -
>>> I am confused as to how to take your solution and implement the way that I have asked for?
what you have asked for is not possible.
What slightwv and I have both suggested are alternates.
My example view IS parameterized. The results of the view are based on the function result. The view results are based on the parameters given with the SET_MY_DATE procedure. If that procedure isn't called, the view will always return no rows.
In order for the function to return a meaningful fliter for the view, you must first set a parameter.
Since you are using dates are your input, you would use the exact same idea I have above.
In fact, to test, you could use the package above with no modifications.
Then, in your view, where you currently have something like
WHERE your_start_date >= TO_DATE('01-JAN-2012','DD-
you would change that to
WHERE your_start_date >= my_view_pkg.get_my_date
Now, when you query the view, the results will be based on whatever you pass into the SET_MY_DATE procedure
what you have asked for is not possible.
What slightwv and I have both suggested are alternates.
My example view IS parameterized. The results of the view are based on the function result. The view results are based on the parameters given with the SET_MY_DATE procedure. If that procedure isn't called, the view will always return no rows.
In order for the function to return a meaningful fliter for the view, you must first set a parameter.
Since you are using dates are your input, you would use the exact same idea I have above.
In fact, to test, you could use the package above with no modifications.
Then, in your view, where you currently have something like
WHERE your_start_date >= TO_DATE('01-JAN-2012','DD-
you would change that to
WHERE your_start_date >= my_view_pkg.get_my_date
Now, when you query the view, the results will be based on whatever you pass into the SET_MY_DATE procedure
>>Are you saying the someother table will get the user input start dates? -
someOtherTable will join on whatever columns you use to join to MyView. The rows in MyView will be restricted to those dates.
Unless we have additional information, our responses will continue to be vague.
someOtherTable will join on whatever columns you use to join to MyView. The rows in MyView will be restricted to those dates.
Unless we have additional information, our responses will continue to be vague.
Grading with anything less than an A is inappropriate if you haven't responded to the posts given.
Please respond with adequate explanation of why you think a B is appropriate given that you failed to ask for any further information from either of the participants
Please respond with adequate explanation of why you think a B is appropriate given that you failed to ask for any further information from either of the participants
Why did you give a B grade?
If you neededed additional information you should have asked.
If you neededed additional information you should have asked.
another way to do this is creating a view on the top of a parallel pipelined function some thing like below:
create or replace view my_vw as
select * from table(select * from pipe_fn(<parameters>))
The function does all the calcn and results a dataset which can be exposed via view..
however this method is also another alternative and is not exactly what you want to do..
create or replace view my_vw as
select * from table(select * from pipe_fn(<parameters>))
The function does all the calcn and results a dataset which can be exposed via view..
however this method is also another alternative and is not exactly what you want to do..
ASKER
I dont understand how the modified objects is being used - are you saying the user values can be got from FUNCTION get_my_date ? I am confused as to how to take your solution and implement the way that I have asked for?