Median function - Fails on view when trying to cast as decimal

Database
Junior Supporter

Median function - Fails on view when trying to cast as decimal

I have a database table which has four columns and two of the columns are INT. The two INT columns' rows are sometimes empty.

When I try to cast them as DECIMAL(18,3) and then use the Median function I am getting the Error: An unknown character string translation was requested.

Is the problem that the cast function doesn't know what to do with the empty rows because empty becomes zero?

SELECT

 myDate

,CompanyName

,Median( cast( myCol1 as DECIMAL(18,3)) ) as myCol1

,Median( cast( myCol2 as DECIMAL(18,3)) ) as myCol2

FROM myDatabaseName.myTableName

Group by myDate, CompanyName

When I try to do a case statement to check for myCol1='' or myCol1 =null or cast( myCol1 as DECIMAL(18,3)) )='0.000' I get Error: Selected non-aggregate values must be part of the associated group.

Any ideas or suggestions?

4 REPLIES
Junior Supporter

Re: Median function - Fails on view when trying to cast as decimal

Anyone?

Teradata Employee

Re: Median function - Fails on view when trying to cast as decimal

Not sure what you are asking. You say myCol1 / myCol2 are INT but a CAST from INT to DECIMAL would not require string translation.

If you are trying to exclude some rows from the MEDIAN calculation, use WHERE not CASE.

In the unlikely event that you want the missing / NULL values treated as zeros for the purpose of calculating MEDIAN, then show us the failing CASE.

Junior Supporter

Re: Median function - Fails on view when trying to cast as decimal

Looks like the problem is the data.

Sometimes myCol1='0.0000' and sometimes myCol2 ='0.0000'.

Junior Supporter

Re: Median function - Fails on view when trying to cast as decimal

If I add myCol1 and myCol2 to the GROUP BY would that make the results wrong?

SELECT

 myDate

,CompanyName

,Median( cast( myCol1 as DECIMAL(18,3)) ) as MedianCol1

,Median( cast( myCol2 as DECIMAL(18,3)) ) as MedianCol2

FROM myDatabaseName.myTableName

Group by myDate, CompanyName,myCol1,myCol2