Re: Create View Problem

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 19 Jan 2003 17:25:15 GMT
Message-ID: <b0en1q$o15nf$2@ID-82536.news.dfncis.de>

> 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

Ronald,

Use analytical functions for these kinds of problems. They're truly great, although unfortunately, their use is not as widespread as it could be:

set pagesize 500
alter session set nls_date_format = 'DD.MM.YYYY';

create table prices_ (

  sku        varchar2(38),
  price      number,

  valid_from date
);

insert into prices_ values ('4711',18, '08.01.2003');
insert into prices_ values ('4711',19, '01.05.2000');
insert into prices_ values ('4711',20, '18.01.2001');
insert into prices_ values ('4711',21, '09.01.2000');

insert into prices_ values ('beer',14, '07.03.2000');
insert into prices_ values ('beer',15, '10.01.2003');
insert into prices_ values ('beer',16, '18.01.2001');

insert into prices_ values ('beer',19, '16.11.2001'); insert into prices_ values ('beer',17, '19.02.2002');

create view prices_today_ as
  select
    sku,
    price,
    valid_from
  from
    (select

       sku,
       price,
       valid_from,
       rank() over (partition by sku order by valid_from desc) r
     from
       prices_

    )
  where r=1;

select * from prices_today_;

drop table prices_;
drop view prices_today_;

hth
Rene Nyffenegger

-- 
  no sig today

Received on Sun Jan 19 2003 - 11:25:15 CST