在 UNION ALL 后截断的 Teradata 字符串 [英] Teradata string truncated after UNION ALL

问题描述

我有一个带有 UNION 子句的查询.其中一个字段是一个普通的硬编码字符串.UNION 之后的语句中的字符串被截断以匹配 UNION 之前字段的字符串长度.听起来很混乱?这是一个例子.

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

然而，输出是

abc
def

有什么想法吗?

我目前知道的解决方法是让带有较长字符串的 SELECT 语句出现在 UNION 之前.即

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?

推荐答案

采用第一个数据类型，但您可以将其转换为所需的数据类型，例如在这种情况下转换为 char(6)，否则该列将保留为 char(3)

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;

这篇关于在 UNION ALL 后截断的 Teradata 字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！