Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The title was best I could find to explain my question, I don't think it helps though. Anyways, I have two tables:

 CREATE TABLE YEARLY_RATES
 ( 
   RATE_YEAR NUMBER(4,0),
   RATE NUMBER(6,4)
 );

 CREATE TABLE PERSON_ACCOUNT
(
   PAID_YEAR NUMBER(4,0),
   PAID_AMOUNT(10,2)
);

I need to select PERSON_ACCOUNT.PAID_YEAR, PERSON_ACCOUNT.PAID_AMOUNT,PERSON_ACCOUNT.PAID_AMOUNT * multiplication of the YEARLY_RATES.RATE WHERE YEARLY_RATES.RATE_YEAR >= PERSON_ACCOUNT.PAID_YEAR. So for instance if I have the following data in PERSON_ACCOUNT

PAID_YEAR|PAID_AMOUNT
---------------------
   2006    2150.69
   2007    3290.40
   2008    3000.35

and YEARLY_RATES tables

RATE_YEAR|RATE
---------------------
   2006    1.067
   2007    1.208
   2008    1.057

then I should be able to have the following output:

PAID_YEAR|PAID_AMOUNT|INDEXED_AMOUNT
----------------------------------------------------
   2006    2150.69      2150.69*1.067*1.208*1.057
   2007    3290.40      3290.40*1.208*1.057 
   2008    3000.35      3000.35*1.057
share|improve this question
add comment

1 Answer 1

Here's how I solved it:

   SELECT PA.PAID_YEAR,PA.PAID_AMOUNT,EXP(SUM(LN(YR.RATE)))*PA.PAID_AMOUNT
     FROM YEARLY_RATES YR, PERSON_ACCOUNT PA
     WHERE YR.RATE_YEAR>=PA.PAID_YEAR
     GROUP BY PA.PAID_YEAR,PA.PAID_AMOUNT

EXP(SUM(LN(YR.RATE))) is just a trick to get the multiplication of the column values as there's no ready function available in Oracle.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.