CODEWITHSUNDEEP
sqloracle-databaseview
Clomez
Clomez

Reputation: 1512

Oracle database, conditional column

I'm not sure from where to start hitting this problem from.

I have huge table, at least hundreds of thousand rows.

One of the columns should be price, but in customers currency. The data available is

  1. Price data in EUR
  2. Package with function to get exchange rate

Of course, exchange rates will update frequently and first fetching the row, then checking if customer uses EUR and if not, doing another fetch for rate information seems like a bad idea.

Also many of the customers will use EUR, so always checking the rate with same query might cause useless overhead.

My first idea was to fetch price and customer data (both needed anyways) and then if customer uses different currency from EUR, I would make another query.

Second idea was to make table witch would have correct price in customer currency to start with. Maybe using Oracle function (which again I already have)

I think, second way is 'more correct' implementation but I have no idea how to use package function to get one column in view.

Is there better way, or should i go with one of these?

Upvotes: 1

Views: 212

Answers (3)

Marmite Bomber
Marmite Bomber

Reputation: 21063

Virtual column comes to your rescue. Note that virtual columns are there starting with Oracle 11g.

Simple add the formula defining the calculation as an virtual column as in example below.

alter table price add
( home_price     NUMBER GENERATED ALWAYS AS 
(case when currency  = 'USD' then price
else price * convert_rate('EUR','USD') end) VIRTUAL
);

Note that is is required, that the rate conversion function is declated as DETERMINISTIC.

Here is the full example for converting EUR in the home rate of UDS

create table price as 
select 10 price, 'EUR' currency from dual union all
select 20 price, 'USD' currency from dual;

create or replace function convert_rate (p_from varchar2, p_to VARCHAR2) return number DETERMINISTIC as
BEGIN 
  return 1.1;
END;
/

alter table price add
( home_price     NUMBER GENERATED ALWAYS AS 
(case when currency  = 'USD' then price
else price * convert_rate('EUR','USD') end) VIRTUAL
);

select *
from price;

     PRICE CUR HOME_PRICE
---------- --- ----------
        10 EUR         11
        20 USD         20

Upvotes: 1

MMV
MMV

Reputation: 980

Creating a view is not a bad call, but you have to realise it's actually just a header, so the query in there would execute each time the value is called, so I'm not sure that's exactly what you want.

what you can do is create a materialized view for all values in all currencies using the function, index it properly for fast search and refresh every time your rates change (doubt they will change every 15 mins) - for materialized view the refresh will perform automatically if set this way.

Upvotes: 1

Popeye
Popeye

Reputation: 35900

As you said that exchange rates are updated frequently and you will always need updated currency values so My suggestion is to create the VIEW and use it as a table wherever you need in your application.

create or replace view your_view_name as
select <column_list>, 
case when currency = 'EUR' then price else price*conversion_rate end as price
from <both of your tables with join>

Cheers!!

Upvotes: 1

Related Questions