CASE Statement returns error - COALESCE does not!!!

[TheMartian]

When running an INSERT/SELECT with a number of CASE statements in the SELECT clause an error '6706 Untranslatable Character' occurs. This happens in both Queryman and a Mainframe BTEQ script. All the data being processed is within existing Teradata tables i.e. no load files are being used.

Changing the CASE statements to COALESCE works fine in both instances. The FROM clause in the query includes a derived table which uses CASE statements as well

The SQL which fails is:
INSERT Mynewtable
(
column_1,
column_2,
column_3
column_4
.........
)
SELECT column_a
,column_b
,column_c
,(CASE
WHEN column_d IS NULL THEN 'N'
ELSE column_d END)
.......

Whilst the following does not return an error:
INSERT Mynewtable
(
column_1,
column_2,
column_3,
column_4
.........
)
SELECT column_a
,column_b
,column_c
,COALESCE(Column_d, 'N')
.......

Also whilst investigating this error it was noticed that a mainframe BTEQ EXPORT using only the SELECT portion of the code put two 'rogue' bytes in the output file before the field which used the first CASE statement i.e. between column_c and column_d. These do not appear when either the CASE statement is completely removed or replaced by COALESCE.

Anyone know why the CASE statement fails?

I have used this construct in the past with no problems. Obviously I have a workaround which works, but out of curiousity I'd like to know what the problem is with using CASE in this instance.

Thanks
Martin

 

[tdgulbra]

What type is column_d? The two "rogue" bytes sound like the length bytes for a varchar field/column.

Perhaps it is a typo, but I noticed in the first Insert statement a comma missing between column_3 and column_4.

I guess the other standard questions apply:
Which database release are you on?
Which client release?
Have you looked in the knowledge base?

I don't work for NCR, I have just heard the questions plenty of times.

Tony

 

[dnoeth]

This sounds like a bug. If you're not running on the latest patch level, you probably should open an incident.

When you use a string-literal, the datatype is a VARCHAR and that's the reason for the two 'rogue' bytes, it's a word indicating the string-length. If it's not supposed to be different for a COALESCE.

Just run the select with a sample 1 and a TYPE(CASE...), TYPE(COALESCE...) and look for differences.

Dieter

 

[TheMartian]

Tony - The data type of column_d is CHAR(1). The missing comma is indeed a typo, but well spotted anyway!

The former would appear to be where the problem lies. I hadn't realised the data type applied by the CASE statement would be VARCHAR. I'd assumed it would take the data type of the column used in the statement.

Dieter - Thanks for pointing this out .
I'll try the TYPE ... SQL you supply when I get a mo.

I'll also try redefining column_d as VARCHAR(1)and see if the CASE works. Although this seems like a waste of space!
Another option I'll look at is defining the data type of the string literal in the CASE like so:

,(CASE
WHEN column_d IS NULL THEN 'N'(CHAR(1))
ELSE column_d END)

Thanks for the help guys.
Martin