Aggregation/WHERE/HAVING statements in Teradata 13.10 vs 14

Database

Aggregation/WHERE/HAVING statements in Teradata 13.10 vs 14

We have recently upgraded from TD 13.10 to 14. All queries we used to run such as the query below ran fine with no issues in TD 13.10. We clearly have an expression that is dervied from two aggregation functions in the WHERE clause. As I mentioned, this has not been an issue ever with TD 13.10. As long as the expression wasn't the aggregate expression used in the SELECT clause, the query would run. If you were looking for SUM(sales) you would have to put that in the HAVING statement. Since upgrading to TD 14 over the weekend, these queries now fail with the error message "[3569] Improper use of an aggregate function in a WHERE Clause". Was there some sort of update in aggregation or the way WHERE/HAVING clauses are evaulated between 13.10 and 14? I haven't been able to find much in the TD 14 documentation besides a line stating aggregate functions can't be in the WHERE clauses. That doesn't explain why we were able to do in TD 13.10 and suddenly cannot in TD 14. Any insight into this change between the two versions would be greatly appreciated.

SELECT customer_number
, SUM(sales) AS total_sales
, SUM(expenses) AS total_expenses
, total_sales - total_expenses AS net_profit
FROM customers
WHERE net_profit > 0
GROUP BY customer_number

1 REPLY
Junior Supporter

Re: Aggregation/WHERE/HAVING statements in Teradata 13.10 vs 14

Hi.

This query shouldn't run in 13.10 either. You should use HAVING instead.

Please, post a REAL execution.

Cheers.

Carlos.