|
|
|
Forum Newbie
Group: Forum Members
Last Login: 6/27/2006 10:38:00 AM
Posts: 3,
Visits: 1
|
|
I have encountered numerous problems due to differing character sets in my database. The biggest issue encountered is a failure in Informatica ETL when attempting to export data from Teradata. LATIN is the default character set; however certain instances exist where columns default to UNICODE instead.
CREATE TABLE DATABASE.TABLE_A, NO FALLBACK AS
( SELECT 'YES' AS DUMMY_FLAG
FROM TABLE_B
)WITH DATA
or
CREATE TABLE DATABASE.TABLE_A, NO FALLBACK AS
( SELECT CASE WHEN A IS NULL
THEN 'YES'
ELSE 'NO'
END AS DUMMY_FLAG
FROM TABLE_B
)WITH DATA
In certain scripts when a literal is used in the select statement, the column defaults to UNICODE. I need to know how to convert the existing columns back to LATIN, and what can be done to prevent this in the future.
Thanks
Brad
FAST / CHEAP / GOOD PICK ANY TWO!
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Today @ 7:20:23 AM
Posts: 533,
Visits: 282
|
|
SQL Reference: Data Types and Literals
Chapter 2: Data Literals
Character Data Literals
The data type and server character set of a character literal is always VARCHAR(n)
CHARACTER SET UNICODE, where n is the length of the literal.
SELECT
'YES' AS DUMMY_FLAG_U,
type (dummy_flag_U),
TRANSLATE('YES' USING UNICODE_TO_LATIN) AS DUMMY_FLAG,
type (dummy_flag);
Dieter
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 6/27/2006 10:38:00 AM
Posts: 3,
Visits: 1
|
|
Thank you Dieter, that is exactly what I needed. This will resolve any future occurances (after I update hundreds of scripts of course).
As for the existing tables, is there a way to ALTER the character set in a table, or is rebuilding every table using this TRANSLATE function the only way? This will work, however many of my tables are just massive.
Thanks again
FAST / CHEAP / GOOD PICK ANY TWO!
|
|
|
|