CODEWITHSUNDEEP
sqldb2udb
jrharshath
jrharshath

Reputation: 26583

Datatype conversion in IBM DB2: BIGINT to VARCHAR

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.

Upvotes: 6

Views: 52444

Answers (3)

Fred Sobotka
Fred Sobotka

Reputation: 5332

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 )

Upvotes: 2

jrharshath
jrharshath

Reputation: 26583

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:

  • you can cast a BIGINT to CHAR
  • and you can cast a CHAR TO VARCHAR

this is ridiculous!

Upvotes: 17

GregA100k
GregA100k

Reputation: 1395

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)

Upvotes: 1

Related Questions