ORACLE:
CREATE TABLE products (prod_id VARCHAR2(20),price NUMBER, prod_life NUMBER);
CREATE TABLE increase_rates (year NUMBER, rate NUMBER);
CREATE OR REPLACE VIEW vw_prices
AS
SELECT prod_id
,CASE WHEN prod_life>0 THEN price*(1+rate0) END AS price0
,CASE WHEN prod_life>1 THEN price*(1+rate1) END AS price1
,CASE WHEN prod_life>2 THEN price*(1+rate2) END AS price2
,CASE WHEN prod_life>3 THEN price*(1+rate3) END AS price3
,CASE WHEN prod_life>4 THEN price*(1+rate4) END AS price4
FROM products
,(SELECT MAX(CASE WHEN year=0 THEN rate END) AS rate0
,MAX(CASE WHEN year=1 THEN rate END) AS rate1
,MAX(CASE WHEN year=2 THEN rate END) AS rate2
,MAX(CASE WHEN year=3 THEN rate END) AS rate3
,MAX(CASE WHEN year=4 THEN rate END) AS rate4
FROM increase_rates
);
CREATE TABLE products (prod_id VARCHAR2(20),price NUMBER, prod_life NUMBER);
CREATE TABLE increase_rates (year NUMBER, rate NUMBER);
CREATE OR REPLACE VIEW vw_prices
AS
SELECT prod_id
,CASE WHEN prod_life>0 THEN price*(1+rate0) END AS price0
,CASE WHEN prod_life>1 THEN price*(1+rate1) END AS price1
,CASE WHEN prod_life>2 THEN price*(1+rate2) END AS price2
,CASE WHEN prod_life>3 THEN price*(1+rate3) END AS price3
,CASE WHEN prod_life>4 THEN price*(1+rate4) END AS price4
FROM products
,(SELECT MAX(CASE WHEN year=0 THEN rate END) AS rate0
,MAX(CASE WHEN year=1 THEN rate END) AS rate1
,MAX(CASE WHEN year=2 THEN rate END) AS rate2
,MAX(CASE WHEN year=3 THEN rate END) AS rate3
,MAX(CASE WHEN year=4 THEN rate END) AS rate4
FROM increase_rates
);