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?
,Median( cast( myCol1 as DECIMAL(18,3)) ) as myCol1
,Median( cast( myCol2 as DECIMAL(18,3)) ) as myCol2
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?
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.
Looks like the problem is the data.
Sometimes myCol1='0.0000' and sometimes myCol2 ='0.0000'.
If I add myCol1 and myCol2 to the GROUP BY would that make the results wrong?
,Median( cast( myCol1 as DECIMAL(18,3)) ) as MedianCol1
,Median( cast( myCol2 as DECIMAL(18,3)) ) as MedianCol2
Group by myDate, CompanyName,myCol1,myCol2