Teradata string truncated after UNION ALL

问题:

I have a query with a UNION clause. One of the field is a plain hardcoded string. The string in the statement after UNION gets truncated to match the string length of the field before the UNION. Sounds confusing? Here's an example.

SELECT 'abc' as xxx 
FROM tbl1
UNION ALL
select 'defghi' as xxx
FROM tbl2;

For the above query, I would expect the output to be

abc
defghi

However, the output is

abc
def

Any thoughts?

EDIT : The workaround, I am aware of currently is to have the SELECT statement with the longer string appear before the UNION. i.e

SELECT 'defghi' as xxx 
FROM tbl2
UNION ALL
select 'abc' as xxx
FROM tbl1;

This would give me the expected output. But is there a better alternative?

回答1:

The first datatype is taken, but you can cast that to your desired datatype like to a char(6) in that case, else the column will remain char(3)

SELECT CAST('abc' as char(6)) as xxx 
FROM tbl1
UNION ALL
select 'defghi' as xxx
FROM tbl2;