Datatype conversion in IBM DB2: BIGINT to VARCHAR

Question

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.

So how can I make it work?

PS: I'm using IBM UDB.

Title phrasing is very important if you want to attract people to your question. "My query is broken" is a kind of title most people will ignore. However, if your title indicates you have a specific problem people will be more likely to take a closer look. — Welbog, Jun 24 '09 at 14:19

score 17 · Accepted Answer · answered Jun 24 '09 at 14:36

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:

this is ridiculous!

score 2 · Answer 2 · answered Jun 24 '09 at 18:46

2

DB2 allows a VARCHAR and CHAR column to be compared without additional casting, so all you really need to do is cast the number.

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

answered Jun 24 '09 at 18:46

Fred Sobotka

score 1 · Answer 3 · answered Jun 24 '09 at 14:34

1

You should be able to cast the selected id column to match the data type of corr_id

select CORR_ID from TABLE1 where CORR_ID not in (select cast(id as varchar) from TABLE2)

answered Jun 24 '09 at 14:34

GregA100k

much better than my answer. I'd vote it up if I had 2 more rep points – GregA100k Jun 24 '09 at 18:30

3 Answers3