Home » SQL & PL/SQL » SQL & PL/SQL » Urgent Help on Sql Query
Urgent Help on Sql Query [message #196059] Tue, 03 October 2006 23:01 Go to next message
AnilOrafaq
Messages: 12
Registered: May 2006
Junior Member
Hi,
Could you please help me to create a view on the below requirement.

I have the following table with data.

Fundid Dealing Date NAV
FUND1 29-SEP-2006 1.3444
FUND1 02-OCT-2006 1.3416
FUND1 03-OCT-2006 1.3455

The above table will have data only for the valid dealing dates.So, now i need to create view based on the above data
such that it looks like.


Fundid Dealing Date NAV
FUND1 03-OCT-2006 1.3455
FUND1 02-OCT-2006 1.3416
FUND1 01-OCT-2006 1.3416(Coping the 02-OCT-2006 NAV price)
FUND1 30-SEP-2006 1.3416(Coping the 02-OCT-2006 NAV price)
FUND1 29-SEP-2006 1.3444

Please be noted that i can bulid this logic in PL/Sql blocks.But i would like to do it in Sql query.

[Updated on: Wed, 04 October 2006 01:07]

Report message to a moderator

Re: Urgent Help on Sql Query [message #196089 is a reply to message #196059] Wed, 04 October 2006 02:02 Go to previous messageGo to next message
vijayanarayanan
Messages: 11
Registered: September 2006
Location: chennai
Junior Member
TRY THIS :

Laughing create view nav
as select n1.fundid,n1.dealing,n1.date,decode(n1.nav,n2.nav,n2.nav||'(cpy of'||n1.date||'nav price'
from nav_details n1,nav_details n2
where n1.nav=n2.nav
AND N1.PRIMARY_KEY_COLUMN <> N2.PRIMARY_KEY_COLUMN; Laughing
Re: Urgent Help on Sql Query [message #196096 is a reply to message #196089] Wed, 04 October 2006 02:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why would he want to try that - it doesn't work.
It doesn't even use same columns that the OP uses (Hint: there's one column called DEALING DATE, not two called DEALING and DATE)

If this query returns the data you're looking for then you should be able to make a view out of it fairly easily (I'm not guaranteeing the perfromance though....)

create table fund (fundid  varchar2(10), dealing_date  date, nav  number);

insert into fund values ('FUND1', to_date('29-SEP-2006','dd-mon-yyyy'), 1.3444);
insert into fund values ('FUND1', to_date('02-OCT-2006','dd-mon-yyyy'), 1.3416);
insert into fund values ('FUND1', to_date('03-OCT-2006','dd-mon-yyyy'), 1.3455);
insert into fund values ('FUND2', to_date('27-SEP-2006','dd-mon-yyyy'), 1.4565);
insert into fund values ('FUND2', to_date('30-SEP-2006','dd-mon-yyyy'), 1.4602);
insert into fund values ('FUND2', to_date('04-OCT-2006','dd-mon-yyyy'), 1.4632);



select fundid
      ,fund_date
      ,(select distinct first_value(nav) over (order by dealing_date desc)
        from   fund f2
        where  f1.fundid = f2.fundid
        and    f1.fund_date >= f2.dealing_date) nav
from (SELECT distinct 
             fundid
            ,(min_date + level -1) fund_date
      from  (select fundid
                   ,min(dealing_date) min_Date 
                   ,max(dealing_date) max_date 
             from   fund
             group by fundid)
      connect by level <= (max_Date - min_date) +1) f1
order by fundid,fund_date;

FUNDID     FUND_DATE        NAV
---------- --------- ----------
FUND1      29-SEP-06     1.3444
FUND1      30-SEP-06     1.3444
FUND1      01-OCT-06     1.3444
FUND1      02-OCT-06     1.3416
FUND1      03-OCT-06     1.3455
FUND2      27-SEP-06     1.4565
FUND2      28-SEP-06     1.4565
FUND2      29-SEP-06     1.4565
FUND2      30-SEP-06     1.4602
FUND2      01-OCT-06     1.4602
FUND2      02-OCT-06     1.4602
FUND2      03-OCT-06     1.4602
FUND2      04-OCT-06     1.4632
Re: Urgent Help on Sql Query [message #196127 is a reply to message #196096] Wed, 04 October 2006 04:26 Go to previous messageGo to next message
AnilOrafaq
Messages: 12
Registered: May 2006
Junior Member
Hi JRowbottom,
Really wonderful!!!!!....Thanks for your help.


Re: Urgent Help on Sql Query [message #196190 is a reply to message #196089] Wed, 04 October 2006 08:00 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Vijay:

Almost every one of your posts has been misinformation. You should check your facts, read the OP question more carefully or try these queries yorself to see if the ourpur indeed matches the question.
Previous Topic: Cant Find Missing parenthesis
Next Topic: How to run PL/SQL scripts
Goto Forum:
  


Current Time: Sat Dec 10 12:50:17 CST 2016

Total time taken to generate the page: 0.19012 seconds