type conversion

ahmedwaseem2000 · #1 (**permalink**) 05-25-07, 11:40

Hi ,

Can any one tell me how to convert varchar to integer in the sub query.

i have tried the below code and it's not working.

Code:

select * from Table1 where COL1 NOT IN (SELECT COL2 AS INTEGER FROM Table2 WHERE CODE_CATG_N='XXXX')

Quote:

col1 is smallint and col2 is varchar

the error it is giving is

Quote:

SQL0401N The data types of the operands for the operation "IN" are not

n_i · #2 (**permalink**) 05-25-07, 12:14

That would be CAST() or INTEGER(), not "COL2 AS INTEGER".

stolze · #3 (**permalink**) 05-25-07, 13:33

The current syntax doesn't trigger a syntax error because "COL2 AS INTEGER" is allowed - it just renames the column from COL2 to the name INTEGER.