SQL IBM DB2 中的数据类型转换：BIGINT 到 VARCHAR
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1038670/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Datatype conversion in IBM DB2: BIGINT to VARCHAR
提问by jrharshath
I'm writing a query to do some stuff. But its not working the way I want it to:
我正在写一个查询来做一些事情。但它没有按照我想要的方式工作：
select CORR_ID from TABLE1
where CORR_ID not in (select id from TABLE2)
The problem is, TABLE2.id is a long, while TABLE1.CORR_ID is a string.
问题是，TABLE2.id 是一个长的，而 TABLE1.CORR_ID 是一个字符串。
So how can I make it work?
那么我怎样才能让它工作呢？
PS: I'm using IBM UDB.
PS：我正在使用 IBM UDB。
回答by jrharshath
Okay, I found a method:
好的，我找到了一个方法：
select CORR_ID from TABLE1 where CORR_ID not in
(select CAST( CAST(id AS CHAR(50)) AS VARCHAR(50) ) from TABLE2)
This is pretty intriguing: You can't cast a BIGINT to VARCHAR, but:
这非常有趣：您不能将 BIGINT 转换为 VARCHAR，但是：
- you can cast a BIGINT to CHAR
- and you can cast a CHAR TO VARCHAR
- 您可以将 BIGINT 转换为 CHAR
- 您可以将 CHAR 转换为 VARCHAR
this is ridiculous!
这是荒唐的！
回答by Fred Sobotka
DB2 allows a VARCHAR and CHAR column to be compared without additional casting, so all you really need to do is cast the number.
DB2 允许比较 VARCHAR 和 CHAR 列而无需额外的转换，因此您真正需要做的就是转换数字。
SELECT corr_id FROM table1 WHERE corr_id NOT IN (SELECT CHAR( id ) FROM table2 )
SELECT corr_id FROM table1 WHERE corr_id NOT IN (SELECT CHAR( id ) FROM table2 )
回答by GregA100k
You should be able to cast the selected id column to match the data type of corr_id
您应该能够转换所选的 id 列以匹配 corr_id 的数据类型
select CORR_ID from TABLE1 where CORR_ID not in (select cast(id as varchar) from TABLE2)
select CORR_ID from TABLE1 where CORR_ID not in (select cast(id as varchar) from TABLE2)
