Untranslatable Text in column will not work with aggregate functions within case statement

UDA

Untranslatable Text in column will not work with aggregate functions within case statement

I am receiving a 6706: The string contains and untranslatable character

Here 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 1

The 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.
1 REPLY
Teradata Employee

Re: Untranslatable Text in column will not work with aggregate functions within case statement

Perhaps add a condition using TRANSLATE_CHK function, such as

Select
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.PH
group 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.