| pls help in query [message #608427] |
Wed, 19 February 2014 10:02  |
simplesanju
Messages: 36
Registered: July 2008
|
Member |
|
|
Hi ,
I have this query:
SELECT * FROM RRCV_EXCHANGE_RATE WHERE FK_SRC_CURRENCY_ID ='HKD' AND FK_TGT_CURRENCY_ID='USD' and Effective = :p1
my requirement is
If :p1 is the future date, need to retrieve record from the most recent date in table. If it is in the past, need data of closest date of past date.
Thanks in advance
|
|
|
|
|
|
|
|
|
|
| Re: pls help in query [message #618261 is a reply to message #608427] |
Thu, 10 July 2014 02:50  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
simplesanju wrote on Wed, 19 February 2014 21:32
If it is in the past, need data of closest date of past date.
What do you mena by closest? It could have three cases logically, less than, greater than and equal to. Which one is supposed to be closest?
|
|
|
|
| Re: pls help in query [message #618265 is a reply to message #618261] |
Thu, 10 July 2014 03:16 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Lalit Kumar B wrote on Thu, 10 July 2014 08:50simplesanju wrote on Wed, 19 February 2014 21:32
If it is in the past, need data of closest date of past date.
What do you mena by closest? It could have three cases logically, less than, greater than and equal to. Which one is supposed to be closest?
And what if there is a tie
|
|
|
|
| Re: pls help in query [message #618268 is a reply to message #618265] |
Thu, 10 July 2014 03:36 |
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Roachcoach wrote on Thu, 10 July 2014 10:16And what if there is a tie
Well, the way "Exchange Rates" are usually stored in the data model is that "Rate X" for "Currency Y" was set on "Time Z"
So to get the Exchange rate at a specific time, you select the "last" change of the exchange rate.
Something like:
select * from (
SELECT * FROM RRCV_EXCHANGE_RATE
WHERE FK_SRC_CURRENCY_ID ='HKD'
AND FK_TGT_CURRENCY_ID ='USD'
and Effective < :p1
order by effective desc
) where rownum = 1;
In that sense a "tie" would mean that an exchange rate for a specific currency was change to "two different values" at the same time, which should hopefully be made impossible by the primary key on the relevant table.
|
|
|
|
| Re: pls help in query [message #618271 is a reply to message #618265] |
Thu, 10 July 2014 03:55 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Roachcoach wrote on Thu, 10 July 2014 13:46And what if there is a tie
That's why I said 3 logical cases, and the 3rd one is a tie or equal to :
Lalit Kumar B wrote on Thu, 10 July 2014 13:20less than, greater than and equal to
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
