enable group by in the query

[Former Member]

Hi,

I have object with the below definition.

case when PROD_TYPE.PROD_TYPE_ID = 14 THEN AVG(PROD.REVENUE) ELSE 0 END

the query is generated as below.

select case when PROD_TYPE.PROD_TYPE_ID = 14 THEN AVG(PROD.REVENUE) ELSE 0 END

from PROD_TYPE inner join PROD on (PROD.PROD_TYPE_ID = PROD_TYPE.PROD_TYPE_ID)

On parsing this object the below error message appears.

Parse failed: Exception: DBD, [Teradata][ODBC Teradata Driver][Teradata Database] Selected non-aggregate values must be part of the associated group. State: HY000

For this error is anything has to be added in PRM file? If yes please let me know what is that parameter.

[Former Member]

Hi Lavanya,

Try this:

AVG(case when PROD_TYPE.PROD_TYPE_ID = 14 THEN PROD.REVENUE ELSE 0 END)

This should work.

[Former Member]

Hi Aris,

I removed the else part and the below one is returning correct results.

AVG(case when PROD_TYPE.PROD_TYPE_ID = 14 THEN PROD.REVENUE END)