Q

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 

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close