0 Replies Latest reply: Sep 4, 2012 2:41 AM by 958439 RSS

    Need SQL Query

    958439
      Problem in achieveing previous records

      Given Table A:

      ACCT_NO             DATE          AMT
      A001,          01-AUG-12,      5000
      A002,          02-AUG-12,     6000
      A003,          (Null),          (Null)          
      A004,          03-AUG-12,     7000


      Required Table B:

      ACCT_NO             DATE          AMT

      A001,          01-AUG-12,     5000
      A002,          02-AUG-12,     6000
      A003,          02-AUG-12,     6000
      A004,          03-AUG-12,     7000


      Notice when Date Column is Null then it will bring previous Date
      Inshort, my only requirement is to create SQL query which will check if any column has Null values so it will bring
      previous value of that particular columns

      SQL query which i wrote is:

      SELECT
      ACCT_NO ,
      DATE,
      NVL2(DATE,DATE,LAG(DATE) OVER(PARTITION BY ACCT_NO ORDER BY DATE))
      NVL2(AMT,AMT,LAG(AMT) OVER(PARTITION BY ACCT_NO ORDER BY AMT))
      FROM TABLE A;

      Edited by: 955436 on Sep 4, 2012 12:34 AM