Toad World® Forums

Teradata, ERROR: "Improper use of an aggregate function in a WHERE Clause."


#1

Hi.

I am creating a table and in the select I am creating a SUM(PO.SvcFrom2 - FO.SvcFrom) AS “Days Diff”

Then in my WHERE clause I am trying to use that field for example:

WHERE “Days Diff” > 0

But I am getting DB Error: “Improper use of an aggregate function in a WHERE Clause.”

Is there some way around this or do I need to create another table that is subset using this where clause?

Thank you.

WHP


#2

Depending on what your SQL is trying to accomplish, you have two options:

1. Subquery:

SELECT COL1, DAYS_DIFF
FROM
(SELECT COL1, (COL3-COL2) AS DAYS_DIFF
FROM TABLE1
WHERE COL1 = ‘SOME VALUE’
GROUP BY 1) A
WHERE DAYS_DIFF > 0;

2. Use HAVING:

SELECT COL1, (COL3-COL2) AS DAYS_DIFF
FROM TABLE1
WHERE COL1 = ‘SOME VALUE’
GROUP BY 1
HAVING DAYS_DIFF > 0;

In these examples, both are valid ways to accomplish the same thing and both should be equally efficient (or inefficient depending on the DB). You may need to choose one or the other depending on the rest of the SQL or personal preference.


#3

Thank you.

This answers my question.

WHP