Re: Create View Problem

In article <bf7e8fc9.0301150525.3438e64a_at_posting.google.com>, roul_at_gmx.de says...
> Hi,
> I have a table with several prices that are valid from a specific
> date.
>
> sku : VARCHAR2(28)
> price : NUMBER(38)
> validFrom : DATE
>
> Now I want to create a view that shows me the prices that are
> currently valid.
> The statement below shows how to get the appropriate row for one
> specific product.
>
> select * from productprice where validfrom < sysdate and sku='4711'
> and rownum=1 order by (sysdate-validfrom) ASC
>
> I've no clue how to create this view. I just wanted to use a
> subselect, but rownum and order by clauses are not allowed in that.
> Can you give me a hint?
>
> Thanks, Ronald
>

Don't use rownum in your view. Just do a

 create view my_view as
  select * from productprice
   where validfrom < sysdate and sku='4711'    order by (sysdate-validfrom) ASC

You can then select from your view and filter by rownum:

 select * from my_view where rownum = 1

• Tom