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

"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 Received on Fri Sep 15 2000 - 09:58:29 CDT