Can't use MAX function in the WHERE clause

I am getting an error saying that a group function is not allowed on the line where I am using the MAX function in the WHERE clause. I wanted the max(date) and also the description from same table GLFEED_RUNS, which is joined to another table ORG_BUDGET. The foreign key is ORG_BUDGET_ID. The query is as below:
SELECT 
   BS4.ORG_BUDGET_ID AS "OrgBudgetId",
   BS4.ORG_BUDGET_NAME AS "OrgName",
   MAX(BS3.GLFEED_RUN_DATE) AS "Date",
   (SELECT GLF1.DESCRIPTION AS "Description" 
      FROM PB_GLFEED_RUNS GLF1 
     WHERE GLF1.ORG_BUDGET_ID=BS4.ORG_BUDGET_ID 
       AND GLF1.GLFEED_RUN_DATE = MAX(BS3.GLFEED_RUN_DATE)) AS "Description" 
  FROM
   PB_ORG_BUDGETS BS4,PB_GLFEED_RUNS BS3 
 WHERE BS4.ORG_BUDGET_ID=BS3.ORG_BUDGET_ID(+) 
 GROUP BY BS4.ORG_BUDGET_NAME,BS4.ORG_BUDGET_ID

    Requires Free Membership to View

    Login

You cannot ever use an aggregate function in a WHERE clause. You must use a HAVING clause or a sub-query to get the value you want. Also, you've got another problem that will turn up once you fix the MAX function location and that problem is that you are retrieving the Description column but not including it in the GROUP BY. All non-aggregate columns in a SELECT statement must be included in the GROUP BY clause.

So, here's my attempt at re-writing your query (it may still need tweaking as I had no way to test it)... 

SELECT OrgBudgetId, OrgName, Date, GLF1.DESCRIPTION AS "Description" 
  FROM PB_GLFEED_RUNS GLF1 ,
		(SELECT BS4.ORG_BUDGET_ID AS "OrgBudgetId",
				BS4.ORG_BUDGET_NAME AS "OrgName",
				MAX(BS3.GLFEED_RUN_DATE) AS "Date",
		   FROM PB_ORG_BUDGETS BS4,PB_GLFEED_RUNS BS3 
		 WHERE BS4.ORG_BUDGET_ID=BS3.ORG_BUDGET_ID(+) 
		 GROUP BY BS4.ORG_BUDGET_NAME,BS4.ORG_BUDGET_ID ) t
 WHERE GLF1.ORG_BUDGET_ID = t.OrgBudgetId
   AND GLF1.GLFEED_RUN_DATE = t.Date ;
Here's the idea:
  1. Use a FROM Clause query to build the main dataset (aliased as t).
  2. Then I join GLF1 to t on OrgBudgetId and the Date (calculated with MAX in the sub-query).
I think this should work, but, as I mentioned above, you may have to work with it a bit to get it exactly the way you want your output to be.

For More Information


This was first published in November 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top