Teradata-比较Varchar和十进制 [英] Teradata - Comparing Varchar to decimal

问题描述

一般来说，我对 Teradata 和 SQL 还是很陌生.我需要通过合并三个表中的数据来创建一个表.我能够成功加入其中的两个.我无法正确编写第三个表的连接条件.这是代码:

I am very new to Teradata and SQL in general. I need to create a table by combining data from three tables. I was able to successfully join two of them. I am not able to write the joining condition for the third table properly. Here is the code:

select s.cola, s.colb, 
t.colc, t.cold,
u.cole, u.colf, u.colg, u.colh, u.coli, u.colj, u.colk, u.coll
from table1 s 
inner join table2 t
on s.colb = t.colc
inner join table3 u
on t.cold = cast(u.colm as decimal)
order by 3
where substr(cast(s.cola as varchar(10)),6,2) = 11 and substr(cast(s.cola as varchar(10)),1,4) = 2017 and substr(cast(s.cola as varchar(10)),9,2) between 06 and 10

我得到的错误是:

[Teradata数据库] [2620]格式或数据包含错误字符.

我认为问题出在以下一行:t.cold = cast(u.colm为十进制)上的 . u.colm 的类型为 VARCHAR(50)，而 t.cold 的类型为 DECIMAL(10，0).我相信我已正确投放.请帮助.谢谢.

I think the problem is with the line: on t.cold = cast(u.colm as decimal). The u.colm is of type VARCHAR(50) while t.cold is of type DECIMAL(10, 0). I believe I have casted it properly. Please help.Thanks in advance.

推荐答案

u.colm 中存在一些错误数据.

There's some bad data in u.colm.

根据您的Teradata版本，您可以使用

Depending on your Teradata release you can check it using

WHERE u.colm > '' AND TRYCAST(u.colm as decimal(10,0)) ISNULL

或

WHERE u.colm > '' AND TO_NUMBER(u.colm) IS NULL

您也可以在加入条件中使用它们，例如

You can also use those in the join-condition, e.g.

on t.cold = trycast(u.colm as decimal(10,0))

别忘了添加小数点的精度，因为它默认为(5,0).

Don't forget to add the precision of the decimal, as it defaults to (5,0).

您的WHERE_condition很奇怪， s.cola 的数据类型是什么?似乎是一个带有日期 yyyy-mm-dd 的字符串.尝试

Your WHERE_condition is strange, what's the datatype of s.cola? Seems it's a string with a date yyyy-mm-dd in it. Try

WHERE trycast(s.cola as date) between date '2017-11-06' and date '2017-11-10' 

最后， ORDER BY 应该放在 WHERE 之后.

这篇关于Teradata-比较Varchar和十进制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！