All Forums Database
teradatatester 69 posts Joined 01/10
14 Jul 2016
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?
 

teradatatester 69 posts Joined 01/10
15 Jul 2016

Anyone?

Fred 1096 posts Joined 08/04
15 Jul 2016

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.
 

teradatatester 69 posts Joined 01/10
15 Jul 2016

Looks like the problem is the data.
Sometimes myCol1='0.0000' and sometimes myCol2 ='0.0000'.

teradatatester 69 posts Joined 01/10
15 Jul 2016

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

You must sign in to leave a comment.