default characterset

Database
Enthusiast

default characterset

I changed the user default characterset to LATIN. But whenever I am using CREATE TABLE AS statement, the default characterset for string fields are still unicode. For example:

create table test as
(
select year, state, county,
'abc' as flg
from table1
group by 1,2,3,4) with data
unique primary index (year, state, county, flg);

show table test;

The field 'flg' is
flg VARCHAR(3) CHARACTER SET UNICODE NOT CASESPECIFIC

How can I change the default characterset to 'LATIN'
3 REPLIES
Teradata Employee

Re: default characterset

String literals are always Unicode strings in the Teradata database. The user default characterset does not apply to string literals.

You could add the char latin column after the 'create table as' with an 'alter table' using a default value of 'abc'.

--i18nguy
Enthusiast

Re: default characterset

One trick is to use the translate function in the select to explicitly state the fields as LATIN
Teradata Employee

Re: default characterset

The following code forces the character set to LATIN:

CREATE TABLE DB.ANDY_TEMP AS (
SELECT PRD_ID
,AV_BAL
,TRANSLATE((CASE
WHEN AV_BAL > 10000
THEN 'RICH'
WHEN AV_BAL < 10000
THEN 'NOT SO RICH'
ELSE 'NO BALANCE'
END) AS UNICODE_TO_LATIN) AS BALANCE_BAND
FROM BALANCE_TABLE
)
WITH DATA AND STATISTICS
UNIQUE PRIMARY INDEX (PRD_ID)
;

One question though is to the processing overhead of the translation. Does anyone know how much this sort of operation adds to the processing?

Thanks!!!

Andy