How can I make this query more generic?

I have a query:

select 
  some_id
from 
  tablename t
where 
  t.end_date < current_date
  and not exists (
    select null
    from tablename ti
    where ti.end_date > t.end_date
      and ti.some_id = t.some_id
      and ti.some_criteria = t.some_criteria
  )

How can I change this so that I can query it for any date without having to change the query on the inside? (replace the "current_date" with a value)

I'm not looking for a PL/SQL answ开发者_JAVA技巧er, as I want to put this in a view...

Although you said you don't want to use PL/SQL, the best option would be to create a (pipelined) table function.

It's a bit more complicate to setup than a view, but once created, you could use the function "like" a view with parameters:

SELECT *
FROM table(get_stuff(current_date));

Here are some examples:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_packages.htm#sthref1054
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#sthref2351
http://psoug.org/reference/pipelined.html
http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/x/Table_Functions_Cursor_Expressions/Pipelined_Table_Functions.htm

• UPDATED #2

The only date parameter there is on this line

t.end_date < current_date

So there is all you need to change without changing the inner query. If you mean that the view is

CREATE VIEW v1
as
select 
  some_id
from 
  tablename t
where 
  t.end_date < current_date
  and not exists (
    select null
    from tablename ti
    where ti.end_date > t.end_date
      and ti.some_id = t.some_id
      and ti.some_criteria = t.some_criteria
  )

And you want to be able to use something like this

select * from v1 where end_date < current_date-7

Not sure if Oracle can inline the filter into the view, but you can try this (remove the filter). A function may work better than a view though.

CREATE VIEW v1
as
select 
  some_id
from 
  tablename t
where not exists (
    select null
    from tablename ti
    where ti.end_date > t.end_date
      and ti.some_id = t.some_id
      and ti.some_criteria = t.some_criteria
  )

You can use a package variable and a package function along with the view. The package variable is set at the session level so can be different for different sessions.

Create the package spec

create or replace package pkg_current_date is

  current_date date;
  function get_current_date return date;

end pkg_current_date;

And the package body:

create or replace package body pkg_current_date is

    function get_current_date return date is

  begin
    return current_date;
  end;


end pkg_current_date;

Then use the function you created within a view

CREATE VIEW v1
as
select 
  some_id
from 
  tablename t
where 
  t.end_date < pkg_current_date.get_current_date;

To query the view, just set the date and then query. It would probably pay to set some sort of default date otherwise the view will return nothing as current_date will be null.

begin
  pkg_current_date.current_date := date '2010-01-01'
end;

select * from v1