Data type conversion in IBM DB2: BIGINT to VARCHAR

Question

I am writing a request to do something. But it does not work the way I want:

select CORR_ID from TABLE1 where CORR_ID not in (select id from TABLE2) 

The problem is that TABLE2.id is long, while TABLE1.CORR_ID is a string.

So how can I make it work?

PS: I am using IBM UDB.

Answer 1

Ok, 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 quite intriguing: you cannot cast BIGINT on a VARCHAR, but:

• you can use bigint for char
• and you can use CHAR TO VARCHAR

This is funny!

Answer 2

DB2 allows you to compare a VARCHAR and CHAR column without additional casting, so all you really need to do is specify a number.

SELECT corr_id FROM table1 WHERE corr_id NOT IN (SELECT CHAR (id) FROM table2)

Answer 3

You must be able to display the selected id column according to the corr_id data type

select CORR_ID from table 1 where CORR_ID is not enabled (select cast (id as varchar) from TABLE2)