{"id":73,"date":"2011-09-24T18:14:36","date_gmt":"2011-09-24T18:14:36","guid":{"rendered":"http:\/\/miguelontheweb.com\/wordpress\/?p=73"},"modified":"2011-10-01T01:53:45","modified_gmt":"2011-10-01T01:53:45","slug":"how-to-make-your-periods-into-rows-on-the-grant-ledger-table","status":"publish","type":"post","link":"http:\/\/miguelontheweb.com\/wordpress\/?p=73","title":{"rendered":"FRRGRNL How to query periods as rows.Simple Hack"},"content":{"rendered":"<p>If you ever had to write a query against the grant ledger (FRRGRNL) or even the operating ledger(FGBOPAL) I\u2019m 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.<\/p>\n<p>Wouldn\u2019t it be nice if you can just say something like?<\/p>\n<p><!--more--><\/p>\n<pre class=\"brush: sql; gutter: true\">SELECT   fund, \n\n             org , \n\n             prog, \n\n             activity,\n\n             location \n\nFROM     frrgrnl\n\nWHERE   period = '05' \n\nAND       frrgrnl_grnt_yr = '10';<\/pre>\n<p>&nbsp;<\/p>\n<p>well friends here is a way that might let you do that.<\/p>\n<p>just create a view that unions all that colums as periods and then just label then with their corresponding periods.<\/p>\n<p>Here is the code to do exactly that.<\/p>\n<pre class=\"brush: sql; gutter: true\">CREATE OR REPLACE FORCE VIEW FVGRANT_TOTALS_PERIOD\n(SELECT --m.frrgrnl_grnt_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_code,\n    m.frrgrnl_Acct_code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE,\n    '00' \"PERIOD\",\n    \/*period 00*\/\n    SUM(m.frrgrnl_00_adopt_bud + m.frrgrnl_00_bud_adjt) BUDGET,\n    SUM(m.frrgrnl_00_ytd_actv) frrgrnl_ytd_actv ,\n    SUM(m.frrgrnl_00_bud_rsrv) frrgrnl_bud_rsrv ,\n    SUM(m.frrgrnl_00_encumb) frrgrnl_encumb ,\n    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\n  FROM frrgrnl m--, FTVACCT\n  WHERE frrgrnl_acct_code NOT LIKE '5%'\n    --AND FTVACCT_ACCT_CODE=FRRGRNL_ACCT_CODE\n  GROUP BY m.frrgrnl_Acct_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_Code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE\n  UNION ALL\n  SELECT --m.frrgrnl_grnt_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_code,\n    m.frrgrnl_Acct_code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE,\n    '01' \"PERIOD\",\n    \/*period 1*\/\n    SUM(m.frrgrnl_01_adopt_bud + m.frrgrnl_01_bud_adjt) BUDGET,\n    SUM(m.frrgrnl_01_ytd_actv) frrgrnl_ytd_actv ,\n    SUM(m.frrgrnl_01_bud_rsrv) frrgrnl_bud_rsrv ,\n    SUM(m.frrgrnl_01_encumb) frrgrnl_encumb ,\n    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\n  FROM frrgrnl m--, FTVACCT\n  WHERE frrgrnl_acct_code NOT LIKE '5%'\n    --AND FTVACCT_ACCT_CODE=FRRGRNL_ACCT_CODE\n  GROUP BY m.frrgrnl_Acct_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_Code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE\n  UNION ALL\n  SELECT --m.frrgrnl_grnt_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_code,\n    m.frrgrnl_Acct_code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE,\n    '02' \"PERIOD\",\n    \/*period 2*\/\n    SUM(m.frrgrnl_02_adopt_bud + m.frrgrnl_02_bud_adjt) BUDGET,\n    SUM(m.frrgrnl_02_ytd_actv) frrgrnl_ytd_actv ,\n    SUM(m.frrgrnl_02_bud_rsrv) frrgrnl_bud_rsrv ,\n    SUM(m.frrgrnl_02_encumb) frrgrnl_encumb ,\n    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\n  FROM frrgrnl m--, FTVACCT\n  WHERE frrgrnl_acct_code NOT LIKE '5%'\n    --AND FTVACCT_ACCT_CODE=FRRGRNL_ACCT_CODE\n  GROUP BY m.frrgrnl_Acct_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_Code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE\n  UNION ALL\n  SELECT --m.frrgrnl_grnt_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_code,\n    m.frrgrnl_Acct_code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE,\n    '03' \"PERIOD\",\n    \/*period 3*\/\n    SUM(m.frrgrnl_03_adopt_bud + m.frrgrnl_03_bud_adjt) BUDGET,\n    SUM(m.frrgrnl_03_ytd_actv) frrgrnl_ytd_actv ,\n    SUM(m.frrgrnl_03_bud_rsrv) frrgrnl_bud_rsrv ,\n    SUM(m.frrgrnl_03_encumb) frrgrnl_encumb ,\n    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\n  FROM frrgrnl m--, FTVACCT\n  WHERE frrgrnl_acct_code NOT LIKE '5%'\n    --AND FTVACCT_ACCT_CODE=FRRGRNL_ACCT_CODE\n  GROUP BY m.frrgrnl_Acct_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_Code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE\n  UNION ALL\n  SELECT --m.frrgrnl_grnt_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_code,\n    m.frrgrnl_Acct_code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE,\n    '04' \"PERIOD\",\n    \/*period 4*\/\n    SUM(m.frrgrnl_04_adopt_bud + m.frrgrnl_04_bud_adjt) BUDGET,\n    SUM(m.frrgrnl_04_ytd_actv) frrgrnl_ytd_actv ,\n    SUM(m.frrgrnl_04_bud_rsrv) frrgrnl_bud_rsrv ,\n    SUM(m.frrgrnl_04_encumb) frrgrnl_encumb ,\n    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\n  FROM frrgrnl m--, FTVACCT\n  WHERE frrgrnl_acct_code NOT LIKE '5%'\n    --AND FTVACCT_ACCT_CODE=FRRGRNL_ACCT_CODE\n  GROUP BY m.frrgrnl_Acct_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_Code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE\n  UNION ALL\n  SELECT --m.frrgrnl_grnt_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_code,\n    m.frrgrnl_Acct_code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE,\n    '05' \"PERIOD\",\n    \/*period 05*\/\n    SUM(m.frrgrnl_05_adopt_bud + m.frrgrnl_05_bud_adjt) BUDGET,\n    SUM(m.frrgrnl_05_ytd_actv) frrgrnl_ytd_actv ,\n    SUM(m.frrgrnl_05_bud_rsrv) frrgrnl_bud_rsrv ,\n    SUM(m.frrgrnl_05_encumb) frrgrnl_encumb ,\n    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\n  FROM frrgrnl m--, FTVACCT\n  WHERE frrgrnl_acct_code NOT LIKE '5%'\n    --AND FTVACCT_ACCT_CODE=FRRGRNL_ACCT_CODE\n  GROUP BY m.frrgrnl_Acct_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_Code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE\n  UNION ALL\n  SELECT --m.frrgrnl_grnt_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_code,\n    m.frrgrnl_Acct_code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE,\n    '06' \"PERIOD\",\n    \/*period 06*\/\n    SUM(m.frrgrnl_06_adopt_bud + m.frrgrnl_06_bud_adjt) BUDGET,\n    SUM(m.frrgrnl_06_ytd_actv) frrgrnl_ytd_actv ,\n    SUM(m.frrgrnl_06_bud_rsrv) frrgrnl_bud_rsrv ,\n    SUM(m.frrgrnl_06_encumb) frrgrnl_encumb ,\n    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\n  FROM frrgrnl m--, FTVACCT\n  WHERE frrgrnl_acct_code NOT LIKE '5%'\n    --AND FTVACCT_ACCT_CODE=FRRGRNL_ACCT_CODE\n  GROUP BY m.frrgrnl_Acct_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_Code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE\n  UNION ALL\n  SELECT --m.frrgrnl_grnt_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_code,\n    m.frrgrnl_Acct_code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE,\n    '07' \"PERIOD\",\n    \/*period 1*\/\n    SUM(m.frrgrnl_07_adopt_bud + m.frrgrnl_07_bud_adjt) BUDGET,\n    SUM(m.frrgrnl_07_ytd_actv) frrgrnl_ytd_actv ,\n    SUM(m.frrgrnl_07_bud_rsrv) frrgrnl_bud_rsrv ,\n    SUM(m.frrgrnl_07_encumb) frrgrnl_encumb ,\n    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\n  FROM frrgrnl m--, FTVACCT\n  WHERE frrgrnl_acct_code NOT LIKE '5%'\n    --AND FTVACCT_ACCT_CODE=FRRGRNL_ACCT_CODE\n  GROUP BY m.frrgrnl_Acct_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_Code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE\n  UNION ALL\n  SELECT --m.frrgrnl_grnt_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_code,\n    m.frrgrnl_Acct_code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE,\n    '08' \"PERIOD\",\n    \/*period 08*\/\n    SUM(m.frrgrnl_08_adopt_bud + m.frrgrnl_08_bud_adjt) BUDGET,\n    SUM(m.frrgrnl_08_ytd_actv) frrgrnl_ytd_actv ,\n    SUM(m.frrgrnl_08_bud_rsrv) frrgrnl_bud_rsrv ,\n    SUM(m.frrgrnl_08_encumb) frrgrnl_encumb ,\n    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\n  FROM frrgrnl m--, FTVACCT\n  WHERE frrgrnl_acct_code NOT LIKE '5%'\n    --AND FTVACCT_ACCT_CODE=FRRGRNL_ACCT_CODE\n  GROUP BY m.frrgrnl_Acct_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_Code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE\n  UNION ALL\n  SELECT --m.frrgrnl_grnt_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_code,\n    m.frrgrnl_Acct_code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE,\n    '09' \"PERIOD\",\n    \/*period 09*\/\n    SUM(m.frrgrnl_09_adopt_bud + m.frrgrnl_09_bud_adjt) BUDGET,\n    SUM(m.frrgrnl_09_ytd_actv) frrgrnl_ytd_actv ,\n    SUM(m.frrgrnl_09_bud_rsrv) frrgrnl_bud_rsrv ,\n    SUM(m.frrgrnl_09_encumb) frrgrnl_encumb ,\n    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\n  FROM frrgrnl m--, FTVACCT\n  WHERE frrgrnl_acct_code NOT LIKE '5%'\n    --AND FTVACCT_ACCT_CODE=FRRGRNL_ACCT_CODE\n  GROUP BY m.frrgrnl_Acct_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_Code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE\n  UNION ALL\n  SELECT --m.frrgrnl_grnt_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_code,\n    m.frrgrnl_Acct_code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE,\n    '10' \"PERIOD\",\n    \/*period 10*\/\n    SUM(m.frrgrnl_10_adopt_bud + m.frrgrnl_10_bud_adjt) BUDGET,\n    SUM(m.frrgrnl_10_ytd_actv) frrgrnl_ytd_actv ,\n    SUM(m.frrgrnl_10_bud_rsrv) frrgrnl_bud_rsrv ,\n    SUM(m.frrgrnl_10_encumb) frrgrnl_encumb ,\n    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\n  FROM frrgrnl m--, FTVACCT\n  WHERE frrgrnl_acct_code NOT LIKE '5%'\n    --AND FTVACCT_ACCT_CODE=FRRGRNL_ACCT_CODE\n  GROUP BY m.frrgrnl_Acct_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_Code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE\n  UNION ALL\n  SELECT --m.frrgrnl_grnt_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_code,\n    m.frrgrnl_Acct_code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE,\n    '11' \"PERIOD\",\n    \/*period 11*\/\n    SUM(m.frrgrnl_11_adopt_bud + m.frrgrnl_11_bud_adjt) BUDGET,\n    SUM(m.frrgrnl_11_ytd_actv) frrgrnl_ytd_actv ,\n    SUM(m.frrgrnl_11_bud_rsrv) frrgrnl_bud_rsrv ,\n    SUM(m.frrgrnl_11_encumb) frrgrnl_encumb ,\n    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\n  FROM frrgrnl m--, FTVACCT\n  WHERE frrgrnl_acct_code NOT LIKE '5%'\n    --AND FTVACCT_ACCT_CODE=FRRGRNL_ACCT_CODE\n  GROUP BY m.frrgrnl_Acct_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_Code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE\n  UNION ALL\n  SELECT --m.frrgrnl_grnt_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_code,\n    m.frrgrnl_Acct_code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE,\n    '12' \"PERIOD\",\n    \/*period 12*\/\n    SUM(m.frrgrnl_12_adopt_bud + m.frrgrnl_12_bud_adjt) BUDGET,\n    SUM(m.frrgrnl_12_ytd_actv) frrgrnl_ytd_actv ,\n    SUM(m.frrgrnl_12_bud_rsrv) frrgrnl_bud_rsrv ,\n    SUM(m.frrgrnl_12_encumb) frrgrnl_encumb ,\n    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\n  FROM frrgrnl m--, FTVACCT\n  WHERE frrgrnl_acct_code NOT LIKE '5%'\n    --AND FTVACCT_ACCT_CODE=FRRGRNL_ACCT_CODE\n  GROUP BY m.frrgrnl_Acct_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_Code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE\n  UNION ALL\n  SELECT --m.frrgrnl_grnt_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_code,\n    m.frrgrnl_Acct_code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE,\n    '13' \"PERIOD\",\n    \/*period 13*\/\n    SUM(m.frrgrnl_13_adopt_bud + m.frrgrnl_13_bud_adjt) BUDGET,\n    SUM(m.frrgrnl_13_ytd_actv) frrgrnl_ytd_actv ,\n    SUM(m.frrgrnl_13_bud_rsrv) frrgrnl_bud_rsrv ,\n    SUM(m.frrgrnl_13_encumb) frrgrnl_encumb ,\n    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\n  FROM frrgrnl m--, FTVACCT\n  WHERE frrgrnl_acct_code NOT LIKE '5%'\n    --AND FTVACCT_ACCT_CODE=FRRGRNL_ACCT_CODE\n  GROUP BY m.frrgrnl_Acct_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_Code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE\n  UNION ALL\n  SELECT --m.frrgrnl_grnt_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_code,\n    m.frrgrnl_Acct_code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE,\n    '14' \"PERIOD\",\n    \/*period 14*\/\n    SUM(m.frrgrnl_14_adopt_bud + m.frrgrnl_14_bud_adjt) BUDGET,\n    SUM(m.frrgrnl_14_ytd_actv) frrgrnl_ytd_actv ,\n    SUM(m.frrgrnl_14_bud_rsrv) frrgrnl_bud_rsrv ,\n    SUM(m.frrgrnl_14_encumb) frrgrnl_encumb ,\n    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\n  FROM frrgrnl m--, FTVACCT\n  WHERE frrgrnl_acct_code NOT LIKE '5%'\n  GROUP BY m.frrgrnl_Acct_code,\n    m.frrgrnl_grnt_code,\n    m.frrgrnl_fund_Code,\n    m.frrgrnl_orgn_code,\n    m.FRRGRNL_PROG_CODE,\n    m.frrgrnl_grnt_yr,\n    m.FRRGRNL_ACTV_CODE,\n    m.FRRGRNL_LOCN_CODE\n  );<\/pre>\n<p>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 &#8220;6120&#8221;, 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.<br \/>\nMiguel<\/p>\n","protected":false},"excerpt":{"rendered":"<p>If you ever had to write a query against the grant ledger (FRRGRNL) or even the operating ledger(FGBOPAL) I\u2019m 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 &hellip; <\/p>\n<p class=\"link-more\"><a href=\"http:\/\/miguelontheweb.com\/wordpress\/?p=73\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;FRRGRNL How to query periods as rows.Simple Hack&#8221;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[7,6,8],"class_list":["post-73","post","type-post","status-publish","format-standard","hentry","category-sungardbanner","tag-columns-into-rows","tag-frrgrnl","tag-preiods-into-rows"],"_links":{"self":[{"href":"http:\/\/miguelontheweb.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/73","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/miguelontheweb.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/miguelontheweb.com\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/miguelontheweb.com\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/miguelontheweb.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=73"}],"version-history":[{"count":25,"href":"http:\/\/miguelontheweb.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/73\/revisions"}],"predecessor-version":[{"id":87,"href":"http:\/\/miguelontheweb.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/73\/revisions\/87"}],"wp:attachment":[{"href":"http:\/\/miguelontheweb.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=73"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/miguelontheweb.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=73"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/miguelontheweb.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=73"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}