cast(to_number) causes error

Database
Enthusiast

cast(to_number) causes error

When I do Cast To_number on Field1 in the query I get an error "selected non-aggregate values must be part of the associated group". Field1 is Char(5) datatype and the actual values in the field are numbers.

Whereas when I run the query with just Field1 the query runs successfully. What could be the issue? Help pls.

 

SELECT
cast(TO_NUMBER(Field1 as integer) as Field1
,FIELD_CD
,sum(1)
from Table 1
group by 1,2

3 REPLIES 3
Teradata Employee

Re: cast(to_number) causes error

Hi.

 

The SQL statement is syntactically incorrect.

 

Please, post the REAL SQL statement and the table definition.

 

Cheers.

 

Carlos.

Ambassador

Re: cast(to_number) causes error

This should not cause a 3504, can you show the actual query?

Btw, TryCast(Field1 as int) is a shorter way to get the same result

 

Enthusiast

Re: cast(to_number) causes error

This is how my query is due to security reasons I cannot share the original query.