0 Replies Latest reply: Oct 23, 2012 7:38 PM by user12021881 RSS

    Reg SQL logic!

    user12021881
      Can anyone help me by explaining how the following SQL works.
      The section Highlighted and underlined.

      WITH
      SAWITH0 AS (select sum(case when T102174.GROUP_ACCOUNT_NUM = 'LT DEBT' then T134466.BALANCE_LOC_AMT end ) as c13,
      sum(case when T102174.GROUP_ACCOUNT_NUM = 'ST BORR' then T134466.BALANCE_LOC_AMT end ) as c14,
      sum(case when T102174.GROUP_ACCOUNT_NUM = 'CMMN STOCK' then T134466.BALANCE_LOC_AMT end ) as c15,
      sum(case when T102174.GROUP_ACCOUNT_NUM = 'PREF STOCK' then T134466.BALANCE_LOC_AMT end ) as c16,
      sum(case when T102174.GROUP_ACCOUNT_NUM = 'RET EARNING' then T134466.BALANCE_LOC_AMT end ) as c17,
      sum(case when T102174.GROUP_ACCOUNT_NUM = 'OTHER EQUITY' then T134466.BALANCE_LOC_AMT end ) as c18,
      T156337.ROW_WID as c19
      from
      W_MCAL_DAY_D T156337 /* Dim_W_MCAL_DAY_D_Fiscal_Day */ ,
      W_GL_GROUP_ACCOUNT_D T102174 /* Dim_W_GL_GROUP_ACCOUNT_D */ ,
      W_GL_BALANCE_A T134466 /* Fact_Agg_W_GL_BALANCE_A */
      where ( T102174.ROW_WID = T134466.GROUP_ACCT_WID and T134466.BALANCE_DT_WID = T156337.ROW_WID and
      (T102174.GROUP_ACCOUNT_NUM in ('CMMN STOCK', 'LT DEBT', 'OTHER EQUITY', 'PREF STOCK', 'RET EARNING', 'ST BORR')) )
      group by T156337.ROW_WID),
      SAWITH1 AS (select LAST_VALUE(D1.c13 IGNORE NULLS) OVER ( ORDER BY D1.c19 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as c7,
      LAST_VALUE(D1.c14 IGNORE NULLS) OVER ( ORDER BY D1.c19 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as c8,
      LAST_VALUE(D1.c15 IGNORE NULLS) OVER ( ORDER BY D1.c19 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as c9,
      LAST_VALUE(D1.c16 IGNORE NULLS) OVER ( ORDER BY D1.c19 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as c10,
      LAST_VALUE(D1.c17 IGNORE NULLS) OVER ( ORDER BY D1.c19 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as c11,
      LAST_VALUE(D1.c18 IGNORE NULLS) OVER ( ORDER BY D1.c19 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as c12
      from
      SAWITH0 D1),
      SAWITH2 AS (select max(D1.c7) as c1,
      max(D1.c8) as c2,
      max(D1.c9) as c3,
      max(D1.c10) as c4,
      max(D1.c11) as c5,
      max(D1.c12) as c6
      from
      SAWITH1 D1)
      __select D1.c1 as c1, D1.c2 as c2 from ( select distinct 0 as c1,+__
      case  when nvl(D1.c3 -1 , 0) + nvl(D1.c4 * -1 , 0) + nvl(D1.c5 * -1 , 0) + nvl(D1.c6 * -1 , 0) in (0)*+__*
      *__or nvl(D1.c3 -1 , 0) + nvl(D1.c4 * -1 , 0) + nvl(D1.c5 * -1 , 0) + nvl(D1.c6 * -1 , 0) is null*+__*
      __then 0 else (case  when D1.c1 -1 is null then 0 else D1.c1 * -1 end + case when D1.c2 * -1 is null then 0 else D1.c2 * -1 end )*+__
      __*/ nullif( nvl(D1.c3 * -1 , 0) nvl(D1.c4 * -1 , 0) + nvl(D1.c5 * -1 , 0) + nvl(D1.c6 * -1 , 0), 0) end as c2*+__
      from
      SAWITH2 D1 ) D1 where rownum <= 65001;

      Thanks in advance!
      Nikki.

      Edited by: user12021881 on Oct 23, 2012 5:37 PM

      Edited by: user12021881 on Oct 23, 2012 5:38 PM