OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query (Oracle 11g )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Query [message #640654]

Sun, 02 August 2015 23:23

kr1347
Messages: 16
Registered: September 2014

Junior Member

Hi Team ,

I am trying to figure out how to populate the below NULL values with 1.245 for dates from 07-OCT-14 to 29-SEP-14
then from 26-SEP-14 to 28-JUL-14 it will be 1.447\

This means if the date is less than or equal to the given date then use the value of max effective date which is less than the given date

we could select the last available index_ratio value for given security_alias and effective date <=p.effective_date , so in other words we will need to modify the sql to return from the subquery the index ratio value identified for the maximum available effective date assuming that this effective date is less or equal position effective date

/forum/fa/12781/0/

Attachment: Capture1.PNG
(Size: 61.26KB, Downloaded 1046 times)

Report message to a moderator

Re: SQL Query [message #640656 is a reply to message #640654]

Sun, 02 August 2015 23:34

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

why don't you issue two UPDATE statements?

Report message to a moderator

Re: SQL Query [message #640657 is a reply to message #640656]

Sun, 02 August 2015 23:35

kr1347
Messages: 16
Registered: September 2014

Junior Member

This is not one time .. dates will change and values changes every time we run

Report message to a moderator

Re: SQL Query [message #640658 is a reply to message #640657]

Sun, 02 August 2015 23:38

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

kr1347 wrote on Sun, 02 August 2015 21:35

This is not one time .. dates will change and values changes every time we run

post SQL & results that show the appropriate date range & values to be used.

How will you & I know what correct date range & value has been posted?

Report message to a moderator

Re: SQL Query [message #640659 is a reply to message #640654]

Sun, 02 August 2015 23:56

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

From your previous topic:

Lalit Kumar B wrote on Wed, 17 September 2014 17:17

Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Michel Cadot wrote on Mon, 22 September 2014 21:42

Michel Cadot wrote on Thu, 18 September 2014 20:43
Michel Cadot wrote on Wed, 17 September 2014 17:18

...
If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.
Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
...

Report message to a moderator

Re: SQL Query [message #640660 is a reply to message #640659]

Mon, 03 August 2015 00:11

kr1347
Messages: 16
Registered: September 2014

Junior Member

I meant In the table Each Security_Alias has different dates and index values

Report message to a moderator

Re: SQL Query [message #640661 is a reply to message #640660]

Mon, 03 August 2015 00:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

kr1347 wrote on Sun, 02 August 2015 22:11

I meant In the table Each Security_Alias has different dates and index values

post SQL & results that show the appropriate date range & values to be used.

How will you & I know what correct date range & value has been posted?

Report message to a moderator

Re: SQL Query [message #640676 is a reply to message #640654]

Mon, 03 August 2015 08:19

kr1347
Messages: 16
Registered: September 2014

Junior Member

Query
-----
select ab.security_alias,
ab.index_ratio,
ab.effective_date
from securitydbo.security_analytics_fi ab
where ab.security_alias = 123627
order by ab.effective_date desc

Below should be the output

/forum/fa/12783/0/

Attachment: Capture1.PNG
(Size: 27.26KB, Downloaded 1142 times)

Report message to a moderator

Re: SQL Query [message #640677 is a reply to message #640676]

Mon, 03 August 2015 08:22

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Mon, 03 August 2015 06:56

From your previous topic:

Lalit Kumar B wrote on Wed, 17 September 2014 17:17
Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Michel Cadot wrote on Mon, 22 September 2014 21:42

Michel Cadot wrote on Thu, 18 September 2014 20:43
Michel Cadot wrote on Wed, 17 September 2014 17:18

...
If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.
Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
...

Report message to a moderator

Re: SQL Query [message #640682 is a reply to message #640676]

Mon, 03 August 2015 09:32

bugfox
Messages: 18
Registered: October 2010

Junior Member

select security_alias,
       coalesce(index_ratio, lead(index_ratio ignore nulls) over (order by effective_date)) index_ratio,
       effective_date
from securitydbo.security_analytics_fi  
order by effective_date

Report message to a moderator

Previous Topic:	problem in record type
Next Topic:	Update table procedure through pl sql using double loop

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 10:14:16 CDT 2024