title: Determining Depreciation by Fiscal Year in PeopleSoft Query Manager
body: <p>I apologize if this has been covered somewhere, but I haven't found anything that quite meets my specific needs. In PeopleSoft Query Manager, we are trying to build a report that projects depreciation 5 or so years into the future. We want the columns to be the fiscal year and we want the returned values to be the annual depreciation by asset by business unit.</p>
  
  <p>What we've tried so far is creating an aggregate expression that will sum the asset depreciation. This simple expression works in and of itself as it sums the monthly depreciation by asset. However, what we'd like to do is create 5+ columns with 5+ of these aggregate sum expressions, based on the fiscal year. So the first "numerical data" column would show 2019's depreciation, the second would show 2020's and so on. </p>
  
  <p>Now, let me provide a disclaimer and say I'm an accountant and not a code person, so I'm less than a novice here. But we've tried embedding the aggregate sum function with case or decode and we have been unable to get it to work with any such conditional type functions. However, surely there must be a way? (For potential further clarity, if we had the data in excel we would use something like =SUMIFS(Depreciation column, fiscal year column, 2019) and then repeat that for the remaining years. We want to get this same result directly from the query though).</p>
  
  <p>Below, we have two codes. The simple one that works but doesn't provide what we need exactly and the one with conditional functions that we can't quite get to work.</p>
  
  <pre><code>`SELECT A.BUSINESS_UNIT, A.ASSET_ID, sum( A.DEPR) 
   FROM PS_DEPR_RPT A 
   WHERE ( A.BOOK = 'CORPORATE' 
   AND A.BUSINESS_UNIT = '50226') 
   GROUP BY  A.BUSINESS_UNIT,  A.ASSET_ID 
   HAVING ( sum( A.DEPR) &lt;&gt; 0)`
  
  `SELECT A.BUSINESS_UNIT, A.ASSET_ID, CASE
   WHEN  A.FISCAL_YEAR = 2019 THEN sum( A.DEPR)
   END 
   FROM PS_DEPR_RPT A 
   WHERE ( A.BOOK = 'CORPORATE' 
   AND A.BUSINESS_UNIT = '50226') 
   GROUP BY  A.BUSINESS_UNIT,  A.ASSET_ID 
   HAVING ( CASE
   WHEN  A.FISCAL_YEAR = 2019 THEN sum( A.DEPR)
   END &lt;&gt; 0)`
  </code></pre>
  
  <p>The first one works fine but does not have the necessary fiscal year information. Now, yes, we can use the fiscal year field as a criteria and then build an excel file using sumifs or other such formulas. But we really would like to not have to manipulate the data in excel, and it seems like we should be able to create this in PeopleSoft Query Manager. </p>
  
  <p>Obviously, the second one looks off (I imagine people who are knowledgeable about SQL are banging their heads), and we get this error:</p>
  
  <p>Error in running query because of SQL Error, Code=979, Message=ORA-00979: not a GROUP BY expression (50,380)</p>
  
  <p>(In my preliminary research, it seems like the group by expression error was a common recurrence when using these conditional functions with aggregate functions).</p>
  
  <p>Lastly, please keep in mind that I personally don't have the ability to create SQL that I could put into the system. The SQL is what query manager generates and my only ability to change it is by using expressions and criteria changes.</p>
  
  <p>EDIT for current results and preferred results:</p>
  
  <p><a href="https://i.stack.imgur.com/vPq0z.png" rel="nofollow noreferrer">What It Looks Like As Is</a></p>
  
  <p><a href="https://i.stack.imgur.com/9bLX0.png" rel="nofollow noreferrer">What We Want It To Look Like</a></p>
  
  <p>Thanks in advance!</p>