Re: PL/SQL based price conversion, is it possible?

There is a simpler and more transparent solution. 1. Create a conversion table with columns currency_name and rate. 2. Use this select to get the price in needed currency:

select i.price * c.rate
from inventory i, conversion c
where i.stuff = 'good'
  and c.currency_name = 'USD'.

This will work if all prices in inventory are stored in one currency. If currencies are different, you need double conversion:

select i.price * c.rate / d.rate
from inventory i, conversion c, conversion d

where i.stuff = 'good'
  and c.currency_name = 'USD'
  and d.currency_name = 'EUR'

In article <969030029.28737.0.nnrp-08.d4f028a1_at_news.demon.co.uk>,   "Barnaby Madgett" <barnaby_at_madgett.com> wrote:
>
> "Nicolas MONNET" <nico_at_monnet.to> wrote in message
> news:qNqw5.256$86.7236_at_tengri.easynet.fr...
> > In article <8ptb83$qg2$1_at_nnrp1.deja.com>, Mark D Powell
> > <markp7832_at_my-deja.com> wrote:
> >
> > > Sounds like a job for a function. Write a function to perform the
> > > conversion for the referenced column and then provide a view that
> > > applies the function to the price column and renames it back to
 price.
> > > Now when selected you get the converted price auto-magically.
> >
> > Yep, but is there anyway to enter a parameter in this?
> > Can I, for example do something like:
> >
> > SELECT price FROM inventory('USD') WHERE stuff = 'good';
> >
> > Any idea?
>
> How about :
>
> SELECT convert(price, 'USD')
> FROM inventory
> WHERE stuff = 'good';
>
> where 'convert' is your function that takes in a value (from price
 column)
> and a constant ('USD', 'GBP' or whatever) and returns a
 currency-converted
> value from this.
>
> This'll only work if all prices are in some fixed 'base' currency. If
> they're in varying currencies, perhaps change 'convert' to take in 3
> arguments e.g.
>
> SELECT convert(price, price_currency, 'USD')
> ...
>
> where price_currency is the column on inventory holding the currency
 that
> price is defined in.
>
> 'convert' function would do a lookup on your currencies table based on
 the
> 'from' and 'to' currencies to get the conversion factors appropriate,
 and
> return the converted price based on that.
>
> Packaging all this up into a nice clean view would make it seem
> auto-magical, as stated previously.
>
> Barns
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Sep 15 2000 - 16:06:27 CDT