special characters

Database
Enthusiast

special characters

Hi, I have a dilemma…

I have a table A that has some special characters in a column aa. When inserting into table B, the following SQL gives the untranslatable character error

Insert into B.bb
sel
CASE
WHEN A.aa IS NULL
OR A.aa = ''
THEN '*'
ELSE A.aa
END AS A.aa
from A

The column DDL is VARCHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC

In both that A and B tables.

However, If I skip this test and just insert the column A.aa as it is, I can do something like this later:

Insert into B.bb
sel A.aa from A ;

update B.bb
set B.bb = '*'
where B.bb is null or B.bb = '';

2 REPLIES
Enthusiast

Re: special characters


hi,
i could insert. please check in both source and target table it is specifed as CHARACTER SET LATIN NOT CASESPECIFIC.

CREATE TABLE testa
,FALLBACK
,NO BEFORE JOURNAL
,NO AFTER JOURNAL
(
col1 VARCHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC,
col2 VARCHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC

)
PRIMARY INDEX( col1 );

CREATE TABLE testb
,FALLBACK
,NO BEFORE JOURNAL
,NO AFTER JOURNAL
(
col1 VARCHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC,
col2 VARCHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC

)
PRIMARY INDEX( col1 );

Insert into testb
sel
CASE
WHEN col1 IS NULL
OR col1 = ' '
THEN '*'
ELSE col1
END AS DES
,col2
from testa;

sel * from testa

col1 col2
2 ab-inito
1 Teradata
? Oracle
java

SEL * FROM testb;

col1 col2
2 ab-inito
1 Teradata
* java
* Oracle
Enthusiast

Re: special characters

Hi Meena, thanks for your reply!
The issue I am expreiencing is little different. I made sure that both source and target table has the column specified as VARCHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC.

I am able to do what you have doen. The issue arrises when we have some special characters which in my case was some russian symbols..

I cannot reproduce the error now since we just corrected the data at source.

Thanks though! :-)