All Forums UDA
Catalina 1 post Joined 01/07
09 Jan 2007
Untranslatable Text in column will not work with aggregate functions within case statement

I am receiving a 6706: The string contains and untranslatable characterHere is my SQL:Select CASE WHEN TRIM(PH_AREA_CD) IS NULL THEN '?' ELSE TRIM(PH_AREA_CD) END from ENTPRIL_PRD_VIEWS_SEC.PH group by 1 ORDER BY 1The column PH_AREA_CD is a CHAR(3) and some of the rows in the table contain the value of ''. If I perform a simple select of the value it gives me the same results.SELECT ''How can I get my statement to work.

Fred 1096 posts Joined 08/04
10 Jan 2007

Perhaps add a condition using TRANSLATE_CHK function, such asSelect CASE WHEN PH_AREA_CD IS NULL THEN '?' WHEN TRANSLATE_CHK(PH_AREA_CD USING LATIN_TO_UNICODE) = 0 THEN TRIM(PH_AREA_CD) ELSE 'BAD' END from ENTPRIL_PRD_VIEWS_SEC.PHgroup by 1 ORDER BY 1;Don't use TRIM in the NULL test or inside TRANSLATE_CHK or it will fail when it encounters the invalid character.

You must sign in to leave a comment.