Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Awkward SQL selection on exchange rate

Status
Not open for further replies.
MCubitt

MCubitt

Programmer
Mar 14, 2002
1,081
GB
We have a table which holds exchange rates for various currencies:
Code: 
SQL> desc currency_rate_tab
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 COMPANY                                   NOT NULL VARCHAR2(20)
 VALID_FROM                                NOT NULL DATE
 CURRENCY_TYPE                             NOT NULL VARCHAR2(10)
 CURRENCY_CODE                             NOT NULL VARCHAR2(3)
 CURRENCY_RATE                                      NUMBER
 ROWVERSION                                NOT NULL DATE
 CONV_FACTOR                               NOT NULL NUMBER
 REF_CURRENCY_CODE                         NOT NULL VARCHAR2(3)

If I were retrieving all rates for a currency I would use:
Code: 
select currency_rate from currency_rate_tab cr where
cr.COMPANY=&COMP and
cr.CURRENCY_CODE=&CURR and
cr.CURRENCY_TYPE='1'

However, I want to find the rate at a particular date.

EG.
VALID_FROM RATE
2005-01-01 10
2005-02-01 10.5
2005-03-01 9.8
2005-04-01 10.1

If the requested date were 2005-02-19 the rate should be 10.5
If the requested date were 2005-02-01 the rate should be 10.5
If the requested date were 2005-03-01 the rate should be 9.8
If the requested date were 2005-04-02 the rate should be 10.1
If the requested date were 2006-02-22 the rate should be 10.1


Anhy ideas if this can be done in a single SQL statement, please?

thanks





Applications Support
UK
 
Sort by date Sort by votes
...but you should not assume ALL valid_from dates are monthly. The table could be:

VALID_FROM RATE
2005-01-05 10
2005-01-19 10.5
2005-02-03 9.8
2005-02-22 10.1
2005-02-28 10.2
2005-03-06 9.9



Applications Support
UK
 
Upvote 0 Downvote
Code: 
select currency_rate 
from currency_rate_tab cr where
cr.COMPANY,cr.CURRENCY_CODE,cr.CURRENCY_TYPE,cr.VALID_FROM
= 
 (select cr1.COMPANY,cr1.CURRENCY_CODE,cr1.CURRENCY_TYPE,  min(cr1.VALID_FROM)
 from from currency_rate_tab cr1 where
 cr1.COMPANY=&COMP and
 cr1.CURRENCY_CODE=&CURR and
 cr1.CURRENCY_TYPE='1' and 
 cr1.VALID_FROM >= &DT
 group by r.COMPANY,cr.CURRENCY_CODE,cr.CURRENCY_TYPE)

This query should work correctly if you have no overlapped rates and if you don't ask about dates before the first entry. Don't forget about quotes in parameters.

Regards, Dima
 
Upvote 0 Downvote
Dima,

Thank you very much for that, I am amazed it is possible!

I will try to implement..


Applications Support
UK
 
Upvote 0 Downvote
A few errors but stuck on this one:
1 select currency_rate
2 from currency_rate_tab cr where
3 cr.COMPANY,cr.CURRENCY_CODE,cr.CURRENCY_TYPE,cr.VALID_FROM
4 =
5 (select cr1.COMPANY,cr1.CURRENCY_CODE,cr1.CURRENCY_TYPE, min(cr1.VALID_FROM)
6 from currency_rate_tab cr1 where
7 cr1.COMPANY='OKM' and
8 cr1.CURRENCY_CODE='AUD' and
9 cr1.CURRENCY_TYPE='1' and
10 cr1.VALID_FROM >= '01/01/2004'
11* group by (cr.COMPANY,cr.CURRENCY_CODE,cr.CURRENCY_TYPE)
SQL> /
cr.COMPANY,cr.CURRENCY_CODE,cr.CURRENCY_TYPE,cr.VALID_FROM
*
ERROR at line 3:
ORA-00920: invalid relational operator


Any ideas?


Applications Support
UK
 
Upvote 0 Downvote
Yes, that looks more right, but still fails:

1 select currency_rate
2 from currency_rate_tab cr where
3 cr.COMPANY,cr.CURRENCY_CODE,cr.CURRENCY_TYPE,cr.VALID_FROM
4 in
5 (select cr1.COMPANY,cr1.CURRENCY_CODE,cr1.CURRENCY_TYPE, min(cr1.VALID_FROM)
6 from currency_rate_tab cr1 where
7 cr1.COMPANY='OKM' and
8 cr1.CURRENCY_CODE='AUD' and
9 cr1.CURRENCY_TYPE='1' and
10 cr1.VALID_FROM >= '01/01/2004'
11* group by (cr.COMPANY,cr.CURRENCY_CODE,cr.CURRENCY_TYPE)
SQL>
SQL> /
cr.COMPANY,cr.CURRENCY_CODE,cr.CURRENCY_TYPE,cr.VALID_FROM
*
ERROR at line 3:
ORA-00920: invalid relational operator




Applications Support
UK
 
Upvote 0 Downvote
it seems there is a pair of brackets missing:
... where
(cr.COMPANY,cr.CURRENCY_CODE,cr.CURRENCY_TYPE,cr.VALID_FROM)
in ...
 
Upvote 0 Downvote
Thank you very much.. I had used brackets when it was = but not when it was IN.

Thank you


Applications Support
UK
 
Upvote 0 Downvote
Almost!!!

1 select currency_rate
2 from currency_rate_tab cr where
3 (cr.COMPANY,cr.CURRENCY_CODE,cr.CURRENCY_TYPE,cr.VALID_FROM)
4 in
5 (select cr1.COMPANY,cr1.CURRENCY_CODE,cr1.CURRENCY_TYPE, min(cr1.VALID_FROM)
6 from currency_rate_tab cr1 where
7 cr1.COMPANY='OKM' and
8 cr1.CURRENCY_CODE='AUD' and
9 cr1.CURRENCY_TYPE='1' and
10 cr1.VALID_FROM >= '01/01/2004'
11* group by (cr.COMPANY,cr.CURRENCY_CODE,cr.CURRENCY_TYPE)
SQL> /
group by (cr.COMPANY,cr.CURRENCY_CODE,cr.CURRENCY_TYPE)
*
ERROR at line 11:
ORA-00920: invalid relational operator

thanks


Applications Support
UK
 
Upvote 0 Downvote
Hi the group by is being performed on the sub query and therefore should be defined as:
group by cr1.COMPANY,cr1.CURRENCY_CODE,cr1.CURRENCY_TYPE);
Note that there is no parenthesis at the start, the close parenthesis is to close off the sub query.

HTH
Jim

 
Upvote 0 Downvote
Sorry, I tried that too:


1 select currency_rate
2 from currency_rate_tab cr where
3 (cr.COMPANY,cr.CURRENCY_CODE,cr.CURRENCY_TYPE,cr.VALID_FROM)
4 in
5 (select cr1.COMPANY,cr1.CURRENCY_CODE,cr1.CURRENCY_TYPE, min(cr1.VALID_FROM)
6 from currency_rate_tab cr1 where
7 cr1.COMPANY='OKM' and
8 cr1.CURRENCY_CODE='AUD' and
9 cr1.CURRENCY_TYPE='1' and
10 cr1.VALID_FROM >= '01/01/2004'
11* group by cr.COMPANY,cr.CURRENCY_CODE,cr.CURRENCY_TYPE
SQL> /
group by cr.COMPANY,cr.CURRENCY_CODE,cr.CURRENCY_TYPE
*
ERROR at line 11:
ORA-00920: invalid relational operator




This seems to work:
Code: 
select currency_rate
from currency_rate_tab cr where
(cr.COMPANY,cr.CURRENCY_CODE,cr.CURRENCY_TYPE,cr.VALID_FROM)
 in
 (select cr1.COMPANY,cr1.CURRENCY_CODE,cr1.CURRENCY_TYPE,  min(cr1.VALID_FROM)
 from currency_rate_tab cr1 where
 cr1.COMPANY='OKM' and
 cr1.CURRENCY_CODE='AUD' and
 cr1.CURRENCY_TYPE='1' and
 to_char(cr1.VALID_FROM,'YYYY-MM-DD') >= '2004-01-01'
 group by cr1.COMPANY,cr1.CURRENCY_CODE,cr1.CURRENCY_TYPE)



Applications Support
UK
 
Upvote 0 Downvote
Actually, it does not work!

It should be ...max(cr1.valid_from)... not min.

Also, should be <= not >=

The way it was working was to get the rate AFTER the current.





Applications Support
UK
 
Upvote 0 Downvote
So final :
[/code]
select currency_rate
from currency_rate_tab cr where
(cr.COMPANY,cr.CURRENCY_CODE,cr.CURRENCY_TYPE,cr.VALID_FROM)
in
(select cr1.COMPANY,cr1.CURRENCY_CODE,cr1.CURRENCY_TYPE, max(cr1.VALID_FROM)
from currency_rate_tab cr1 where
cr1.COMPANY='OKM' and
cr1.CURRENCY_CODE='AUD' and
cr1.CURRENCY_TYPE='1' and
to_char(cr1.VALID_FROM,'YYYY-MM-DD') <= '2004-01-01'
group by cr1.COMPANY,cr1.CURRENCY_CODE,cr1.CURRENCY_TYPE)
[/code]

Thanks all for accumulated assistance.


Applications Support
UK
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Andrzejek
  • Locked
  • Question
Get part of a string with SQL 1
Replies
2
Views
395
Andrzejek
Andrzejek
Livia8
  • Locked
  • Question
SQL SELECT - where date is null or greater than 2
Replies
9
Views
2K
carp
carp
Chopstik
  • Locked
  • Question
How to identify a package based on a code snippet 1
Replies
1
Views
335
Andrzejek
Andrzejek
annie1975
  • Locked
  • Question
sql script in an cron job script failing
Replies
3
Views
173
carp
carp
Livia8
  • Locked
  • Question
SQL script to find number of characters allowed in &quot;Description&quot; column of &quot;AP_INVOIC 2
Replies
5
Views
195
Livia8
Livia8

Part and Inventory Search

Sponsor

Back
Top