Re: Create View Problem

tojo <Tojo_at_hotmail.com> wrote in message news:<MPG.188f822f41421f4b9896e1_at_news.t-online.de>...
> 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

Thanks Tom, but I wanted to create the view for all products instead of one. Your example creates the view for one product. Any help is appreciated to solve this.

Ronald Received on Thu Jan 16 2003 - 03:57:09 CST