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 ;
- Use a FROM Clause query to build the main dataset (aliased as t).
- Then I join GLF1 to t on OrgBudgetId and the Date (calculated with MAX in the sub-query).
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.