Ask the Expert

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: