Home » SQL & PL/SQL » SQL & PL/SQL » pls help in query (pl sql)
pls help in query [message #608427] Wed, 19 February 2014 10:02 Go to next message
simplesanju
Messages: 21
Registered: July 2008
Junior 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 #608442 is a reply to message #608427] Wed, 19 February 2014 12:48 Go to previous messageGo to next message
Michel Cadot
Messages: 59136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So do it.

Re: pls help in query [message #608445 is a reply to message #608427] Wed, 19 February 2014 13:18 Go to previous messageGo to next message
Michel Cadot
Messages: 59136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Note that if you do not store dates in the future in your table (one thing among many others you didn't tell us), the latest date is the same than the closest date and so you have only one case.

Re: pls help in query [message #618257 is a reply to message #608445] Thu, 10 July 2014 02:30 Go to previous messageGo to next message
simplesanju
Messages: 21
Registered: July 2008
Junior Member
Thanks Michel
Re: pls help in query [message #618261 is a reply to message #608427] Thu, 10 July 2014 02:50 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2334
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 Go to previous messageGo to next message
Roachcoach
Messages: 1202
Registered: May 2010
Location: UK
Senior Member
Lalit Kumar B wrote on Thu, 10 July 2014 08:50
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?


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 Go to previous messageGo to next message
ThomasG
Messages: 3099
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Roachcoach wrote on Thu, 10 July 2014 10:16
And 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 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2334
Registered: May 2013
Location: World Wide on the Web
Senior Member
Roachcoach wrote on Thu, 10 July 2014 13:46
And 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:20
less than, greater than and equal to


Re: pls help in query [message #618299 is a reply to message #618271] Thu, 10 July 2014 07:33 Go to previous message
Roachcoach
Messages: 1202
Registered: May 2010
Location: UK
Senior Member
I posted with insufficient caffeine levels. Again.

/gets_coat.
Previous Topic: Refcursor with returning resultset
Next Topic: pass parameter into the Procedure , which are passing into the Cursor for loop in the Another Proced
Goto Forum:
  


Current Time: Thu Sep 18 15:13:24 CDT 2014

Total time taken to generate the page: 0.09561 seconds