If you ever had to write a query against the grant ledger (FRRGRNL) or even the operating ledger(FGBOPAL) I’m sure you have come across this little challenge. You have to start writing a massive decode statement to make your query get the right column. This can be challenging to say the least or a bit of a pain.
Wouldn’t it be nice if you can just say something like?
SELECT fund, org , prog, activity, location FROM frrgrnl WHERE period = '05' AND frrgrnl_grnt_yr = '10';
well friends here is a way that might let you do that.
just create a view that unions all that colums as periods and then just label then with their corresponding periods.
Here is the code to do exactly that.
CREATE OR REPLACE FORCE VIEW FVGRANT_TOTALS_PERIOD (SELECT --m.frrgrnl_grnt_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_code, m.frrgrnl_Acct_code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE, '00' "PERIOD", /*period 00*/ SUM(m.frrgrnl_00_adopt_bud + m.frrgrnl_00_bud_adjt) BUDGET, SUM(m.frrgrnl_00_ytd_actv) frrgrnl_ytd_actv , SUM(m.frrgrnl_00_bud_rsrv) frrgrnl_bud_rsrv , SUM(m.frrgrnl_00_encumb) frrgrnl_encumb , SUM(m.frrgrnl_00_adopt_bud + m.frrgrnl_00_bud_adjt - m.frrgrnl_00_ytd_actv - m.frrgrnl_00_bud_rsrv - m.frrgrnl_00_encumb)Grant_total_period FROM frrgrnl m--, FTVACCT WHERE frrgrnl_acct_code NOT LIKE '5%' --AND FTVACCT_ACCT_CODE=FRRGRNL_ACCT_CODE GROUP BY m.frrgrnl_Acct_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_Code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE UNION ALL SELECT --m.frrgrnl_grnt_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_code, m.frrgrnl_Acct_code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE, '01' "PERIOD", /*period 1*/ SUM(m.frrgrnl_01_adopt_bud + m.frrgrnl_01_bud_adjt) BUDGET, SUM(m.frrgrnl_01_ytd_actv) frrgrnl_ytd_actv , SUM(m.frrgrnl_01_bud_rsrv) frrgrnl_bud_rsrv , SUM(m.frrgrnl_01_encumb) frrgrnl_encumb , SUM(m.frrgrnl_01_adopt_bud + m.frrgrnl_01_bud_adjt - m.frrgrnl_01_ytd_actv - m.frrgrnl_01_bud_rsrv - m.frrgrnl_01_encumb)Grant_total_period FROM frrgrnl m--, FTVACCT WHERE frrgrnl_acct_code NOT LIKE '5%' --AND FTVACCT_ACCT_CODE=FRRGRNL_ACCT_CODE GROUP BY m.frrgrnl_Acct_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_Code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE UNION ALL SELECT --m.frrgrnl_grnt_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_code, m.frrgrnl_Acct_code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE, '02' "PERIOD", /*period 2*/ SUM(m.frrgrnl_02_adopt_bud + m.frrgrnl_02_bud_adjt) BUDGET, SUM(m.frrgrnl_02_ytd_actv) frrgrnl_ytd_actv , SUM(m.frrgrnl_02_bud_rsrv) frrgrnl_bud_rsrv , SUM(m.frrgrnl_02_encumb) frrgrnl_encumb , SUM(m.frrgrnl_02_adopt_bud + m.frrgrnl_02_bud_adjt - m.frrgrnl_02_ytd_actv - m.frrgrnl_02_bud_rsrv - m.frrgrnl_02_encumb)Grant_total_period FROM frrgrnl m--, FTVACCT WHERE frrgrnl_acct_code NOT LIKE '5%' --AND FTVACCT_ACCT_CODE=FRRGRNL_ACCT_CODE GROUP BY m.frrgrnl_Acct_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_Code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE UNION ALL SELECT --m.frrgrnl_grnt_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_code, m.frrgrnl_Acct_code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE, '03' "PERIOD", /*period 3*/ SUM(m.frrgrnl_03_adopt_bud + m.frrgrnl_03_bud_adjt) BUDGET, SUM(m.frrgrnl_03_ytd_actv) frrgrnl_ytd_actv , SUM(m.frrgrnl_03_bud_rsrv) frrgrnl_bud_rsrv , SUM(m.frrgrnl_03_encumb) frrgrnl_encumb , SUM(m.frrgrnl_03_adopt_bud + m.frrgrnl_03_bud_adjt - m.frrgrnl_03_ytd_actv - m.frrgrnl_03_bud_rsrv - m.frrgrnl_03_encumb)Grant_total_period FROM frrgrnl m--, FTVACCT WHERE frrgrnl_acct_code NOT LIKE '5%' --AND FTVACCT_ACCT_CODE=FRRGRNL_ACCT_CODE GROUP BY m.frrgrnl_Acct_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_Code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE UNION ALL SELECT --m.frrgrnl_grnt_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_code, m.frrgrnl_Acct_code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE, '04' "PERIOD", /*period 4*/ SUM(m.frrgrnl_04_adopt_bud + m.frrgrnl_04_bud_adjt) BUDGET, SUM(m.frrgrnl_04_ytd_actv) frrgrnl_ytd_actv , SUM(m.frrgrnl_04_bud_rsrv) frrgrnl_bud_rsrv , SUM(m.frrgrnl_04_encumb) frrgrnl_encumb , SUM(m.frrgrnl_04_adopt_bud + m.frrgrnl_04_bud_adjt - m.frrgrnl_04_ytd_actv - m.frrgrnl_04_bud_rsrv - m.frrgrnl_04_encumb)Grant_total_period FROM frrgrnl m--, FTVACCT WHERE frrgrnl_acct_code NOT LIKE '5%' --AND FTVACCT_ACCT_CODE=FRRGRNL_ACCT_CODE GROUP BY m.frrgrnl_Acct_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_Code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE UNION ALL SELECT --m.frrgrnl_grnt_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_code, m.frrgrnl_Acct_code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE, '05' "PERIOD", /*period 05*/ SUM(m.frrgrnl_05_adopt_bud + m.frrgrnl_05_bud_adjt) BUDGET, SUM(m.frrgrnl_05_ytd_actv) frrgrnl_ytd_actv , SUM(m.frrgrnl_05_bud_rsrv) frrgrnl_bud_rsrv , SUM(m.frrgrnl_05_encumb) frrgrnl_encumb , SUM(m.frrgrnl_05_adopt_bud + m.frrgrnl_05_bud_adjt - m.frrgrnl_05_ytd_actv - m.frrgrnl_05_bud_rsrv - m.frrgrnl_05_encumb)Grant_total_period FROM frrgrnl m--, FTVACCT WHERE frrgrnl_acct_code NOT LIKE '5%' --AND FTVACCT_ACCT_CODE=FRRGRNL_ACCT_CODE GROUP BY m.frrgrnl_Acct_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_Code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE UNION ALL SELECT --m.frrgrnl_grnt_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_code, m.frrgrnl_Acct_code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE, '06' "PERIOD", /*period 06*/ SUM(m.frrgrnl_06_adopt_bud + m.frrgrnl_06_bud_adjt) BUDGET, SUM(m.frrgrnl_06_ytd_actv) frrgrnl_ytd_actv , SUM(m.frrgrnl_06_bud_rsrv) frrgrnl_bud_rsrv , SUM(m.frrgrnl_06_encumb) frrgrnl_encumb , SUM(m.frrgrnl_06_adopt_bud + m.frrgrnl_06_bud_adjt - m.frrgrnl_06_ytd_actv - m.frrgrnl_06_bud_rsrv - m.frrgrnl_06_encumb)Grant_total_period FROM frrgrnl m--, FTVACCT WHERE frrgrnl_acct_code NOT LIKE '5%' --AND FTVACCT_ACCT_CODE=FRRGRNL_ACCT_CODE GROUP BY m.frrgrnl_Acct_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_Code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE UNION ALL SELECT --m.frrgrnl_grnt_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_code, m.frrgrnl_Acct_code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE, '07' "PERIOD", /*period 1*/ SUM(m.frrgrnl_07_adopt_bud + m.frrgrnl_07_bud_adjt) BUDGET, SUM(m.frrgrnl_07_ytd_actv) frrgrnl_ytd_actv , SUM(m.frrgrnl_07_bud_rsrv) frrgrnl_bud_rsrv , SUM(m.frrgrnl_07_encumb) frrgrnl_encumb , SUM(m.frrgrnl_07_adopt_bud + m.frrgrnl_07_bud_adjt - m.frrgrnl_07_ytd_actv - m.frrgrnl_07_bud_rsrv - m.frrgrnl_07_encumb)Grant_total_period FROM frrgrnl m--, FTVACCT WHERE frrgrnl_acct_code NOT LIKE '5%' --AND FTVACCT_ACCT_CODE=FRRGRNL_ACCT_CODE GROUP BY m.frrgrnl_Acct_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_Code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE UNION ALL SELECT --m.frrgrnl_grnt_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_code, m.frrgrnl_Acct_code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE, '08' "PERIOD", /*period 08*/ SUM(m.frrgrnl_08_adopt_bud + m.frrgrnl_08_bud_adjt) BUDGET, SUM(m.frrgrnl_08_ytd_actv) frrgrnl_ytd_actv , SUM(m.frrgrnl_08_bud_rsrv) frrgrnl_bud_rsrv , SUM(m.frrgrnl_08_encumb) frrgrnl_encumb , SUM(m.frrgrnl_08_adopt_bud + m.frrgrnl_08_bud_adjt - m.frrgrnl_08_ytd_actv - m.frrgrnl_08_bud_rsrv - m.frrgrnl_08_encumb)Grant_total_period FROM frrgrnl m--, FTVACCT WHERE frrgrnl_acct_code NOT LIKE '5%' --AND FTVACCT_ACCT_CODE=FRRGRNL_ACCT_CODE GROUP BY m.frrgrnl_Acct_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_Code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE UNION ALL SELECT --m.frrgrnl_grnt_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_code, m.frrgrnl_Acct_code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE, '09' "PERIOD", /*period 09*/ SUM(m.frrgrnl_09_adopt_bud + m.frrgrnl_09_bud_adjt) BUDGET, SUM(m.frrgrnl_09_ytd_actv) frrgrnl_ytd_actv , SUM(m.frrgrnl_09_bud_rsrv) frrgrnl_bud_rsrv , SUM(m.frrgrnl_09_encumb) frrgrnl_encumb , SUM(m.frrgrnl_09_adopt_bud + m.frrgrnl_09_bud_adjt - m.frrgrnl_09_ytd_actv - m.frrgrnl_09_bud_rsrv - m.frrgrnl_09_encumb)Grant_total_period FROM frrgrnl m--, FTVACCT WHERE frrgrnl_acct_code NOT LIKE '5%' --AND FTVACCT_ACCT_CODE=FRRGRNL_ACCT_CODE GROUP BY m.frrgrnl_Acct_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_Code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE UNION ALL SELECT --m.frrgrnl_grnt_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_code, m.frrgrnl_Acct_code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE, '10' "PERIOD", /*period 10*/ SUM(m.frrgrnl_10_adopt_bud + m.frrgrnl_10_bud_adjt) BUDGET, SUM(m.frrgrnl_10_ytd_actv) frrgrnl_ytd_actv , SUM(m.frrgrnl_10_bud_rsrv) frrgrnl_bud_rsrv , SUM(m.frrgrnl_10_encumb) frrgrnl_encumb , SUM(m.frrgrnl_10_adopt_bud + m.frrgrnl_10_bud_adjt - m.frrgrnl_10_ytd_actv - m.frrgrnl_10_bud_rsrv - m.frrgrnl_10_encumb)Grant_total_period FROM frrgrnl m--, FTVACCT WHERE frrgrnl_acct_code NOT LIKE '5%' --AND FTVACCT_ACCT_CODE=FRRGRNL_ACCT_CODE GROUP BY m.frrgrnl_Acct_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_Code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE UNION ALL SELECT --m.frrgrnl_grnt_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_code, m.frrgrnl_Acct_code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE, '11' "PERIOD", /*period 11*/ SUM(m.frrgrnl_11_adopt_bud + m.frrgrnl_11_bud_adjt) BUDGET, SUM(m.frrgrnl_11_ytd_actv) frrgrnl_ytd_actv , SUM(m.frrgrnl_11_bud_rsrv) frrgrnl_bud_rsrv , SUM(m.frrgrnl_11_encumb) frrgrnl_encumb , SUM(m.frrgrnl_11_adopt_bud + m.frrgrnl_11_bud_adjt - m.frrgrnl_11_ytd_actv - m.frrgrnl_11_bud_rsrv - m.frrgrnl_11_encumb)Grant_total_period FROM frrgrnl m--, FTVACCT WHERE frrgrnl_acct_code NOT LIKE '5%' --AND FTVACCT_ACCT_CODE=FRRGRNL_ACCT_CODE GROUP BY m.frrgrnl_Acct_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_Code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE UNION ALL SELECT --m.frrgrnl_grnt_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_code, m.frrgrnl_Acct_code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE, '12' "PERIOD", /*period 12*/ SUM(m.frrgrnl_12_adopt_bud + m.frrgrnl_12_bud_adjt) BUDGET, SUM(m.frrgrnl_12_ytd_actv) frrgrnl_ytd_actv , SUM(m.frrgrnl_12_bud_rsrv) frrgrnl_bud_rsrv , SUM(m.frrgrnl_12_encumb) frrgrnl_encumb , SUM(m.frrgrnl_12_adopt_bud + m.frrgrnl_12_bud_adjt - m.frrgrnl_12_ytd_actv - m.frrgrnl_12_bud_rsrv - m.frrgrnl_12_encumb)Grant_total_period FROM frrgrnl m--, FTVACCT WHERE frrgrnl_acct_code NOT LIKE '5%' --AND FTVACCT_ACCT_CODE=FRRGRNL_ACCT_CODE GROUP BY m.frrgrnl_Acct_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_Code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE UNION ALL SELECT --m.frrgrnl_grnt_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_code, m.frrgrnl_Acct_code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE, '13' "PERIOD", /*period 13*/ SUM(m.frrgrnl_13_adopt_bud + m.frrgrnl_13_bud_adjt) BUDGET, SUM(m.frrgrnl_13_ytd_actv) frrgrnl_ytd_actv , SUM(m.frrgrnl_13_bud_rsrv) frrgrnl_bud_rsrv , SUM(m.frrgrnl_13_encumb) frrgrnl_encumb , SUM(m.frrgrnl_13_adopt_bud + m.frrgrnl_13_bud_adjt - m.frrgrnl_13_ytd_actv - m.frrgrnl_13_bud_rsrv - m.frrgrnl_13_encumb)Grant_total_period FROM frrgrnl m--, FTVACCT WHERE frrgrnl_acct_code NOT LIKE '5%' --AND FTVACCT_ACCT_CODE=FRRGRNL_ACCT_CODE GROUP BY m.frrgrnl_Acct_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_Code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE UNION ALL SELECT --m.frrgrnl_grnt_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_code, m.frrgrnl_Acct_code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE, '14' "PERIOD", /*period 14*/ SUM(m.frrgrnl_14_adopt_bud + m.frrgrnl_14_bud_adjt) BUDGET, SUM(m.frrgrnl_14_ytd_actv) frrgrnl_ytd_actv , SUM(m.frrgrnl_14_bud_rsrv) frrgrnl_bud_rsrv , SUM(m.frrgrnl_14_encumb) frrgrnl_encumb , SUM(m.frrgrnl_14_adopt_bud + m.frrgrnl_14_bud_adjt - m.frrgrnl_14_ytd_actv - m.frrgrnl_14_bud_rsrv - m.frrgrnl_14_encumb)Grant_total_period FROM frrgrnl m--, FTVACCT WHERE frrgrnl_acct_code NOT LIKE '5%' GROUP BY m.frrgrnl_Acct_code, m.frrgrnl_grnt_code, m.frrgrnl_fund_Code, m.frrgrnl_orgn_code, m.FRRGRNL_PROG_CODE, m.frrgrnl_grnt_yr, m.FRRGRNL_ACTV_CODE, m.FRRGRNL_LOCN_CODE );
Now i do understand that there is some performance issue with this solution. We are making the periods into rows and ending up with many more rows then the table currently holds. In other words if you have account “6120”, you essentially are ending up with 15 rows per account due to the fact that every period has now 15 periods 00-14,but thats a small price to pay for much cleaner code.
Miguel