1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

please help me to write query

Discussion in 'SQL PL/SQL' started by sanjana, Feb 27, 2014.

  1. sanjana

    sanjana Active Member

    Messages:
    2
    Likes Received:
    1
    Trophy Points:
    65
    Hi All,

    My requirement is little tricky. can you please help me to write query?

    Id col1
    1 12
    2 16
    3 null
    4 20
    5 45
    6 null
    7 25
    9 25
    10 12

    requirement is to fill value in place of null.logic is:
    null=previous record value+next record value/2

    out put :

    Id col1
    1 12
    2 16
    3 18 ---16+20/2
    4 20
    5 45
    6 35 ---45+25/2
    7 25
    9 25
    10 12
     
  2. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi ,

    Try with below query . Its look lengthy . There may be simple solution . . .

    Code (SQL):

    SELECT id,col1,DECODE(a.col1,NULL, (
                                   ( SELECT b.col1
                                    FROM   xx_temp b
                                    WHERE  b.id = (
                                                       SELECT MAX(c.id)
                                                       FROM   xx_temp c
                                                       WHERE  c.id < a.id
                                                       AND    c.col1 IS NOT NULL
                                                   ))
                                    + (SELECT d.col1
                                    FROM   xx_temp d
                                    WHERE  d.id = (
                                                       SELECT MIN(e.id)
                                                       FROM   xx_temp e
                                                       WHERE  e.id > a.id
                                                       AND    e.col1 IS NOT NULL
                                                   ))            
                               )/2
                         ,a.col1
                 ) val
    FROM  xx_temp a
     

    Regards
    Sambasiva Reddy.K
     
  3. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Code (SQL):
    CREATE TABLE a ( id1 NUMBER,col1 NUMBER);
    Code (SQL):
    INSERT INTO a VALUES(1,12);
    INSERT INTO a VALUES(2,16);
    INSERT INTO a VALUES(3,NULL);
    INSERT INTO a VALUES(4,20);
    INSERT INTO a VALUES(5,45);
    INSERT INTO a VALUES(6,NULL);
    INSERT INTO a VALUES(7,25);
    INSERT INTO a VALUES(9,25);
    INSERT INTO a VALUES(10,12);
    Code (SQL):
    SELECT * FROM a;
    Code (SQL):
    SELECT id1,
      col1 ,
      DECODE(col1,NULL,((lead(col1,1) OVER (ORDER BY id1))+(lag(col1,1) OVER (ORDER BY id1)))/2,col1)new1
    FROM a;
     
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Code (SQL):
    CREATE TABLE a ( id1 NUMBER,col1 NUMBER);
    Code (SQL):
    INSERT INTO a VALUES(1,12);
    INSERT INTO a VALUES(2,16);
    INSERT INTO a VALUES(3,NULL);
    INSERT INTO a VALUES(4,20);
    INSERT INTO a VALUES(5,45);
    INSERT INTO a VALUES(6,NULL);
    INSERT INTO a VALUES(7,25);
    INSERT INTO a VALUES(9,25);
    INSERT INTO a VALUES(10,12);
    Code (SQL):
    SELECT * FROM a;
    Code (SQL):
    SELECT id1,
      col1 ,
      DECODE(col1,NULL,((lead(col1,1) OVER (ORDER BY id1))+(lag(col1,1) OVER (ORDER BY id1)))/2,col1)new1
    FROM a;
    please let us know the status...
     
  5. sanjana

    sanjana Active Member

    Messages:
    2
    Likes Received:
    1
    Trophy Points:
    65
    Thanks a ton to Sambasiva Reddy.K and Kiran!!!

    kiran your query is so simple and working fine for me. now i can implement this logic into my real reaquirement.
     
    kiran.marla likes this.