Selecting values based on other column

laknar · #1 (**permalink**) 03-24-09, 09:01

Code:

--------------------------------------------------
-- Create Table TEST
--------------------------------------------------
Create table TEST (
    COLUMN1                            VARCHAR(50)                     ,
    COLUMN2                            VARCHAR(50)                     
     ) 
in USERSPACE1   ;


insert into TEST values ('A','A,B');
insert into TEST values ('B','B,C');

select * from where Column1 in(''''||REPLACE(column2,',',''',''')||'''');

how to select records based on column2

note:- Column2 contains list of values seperated by comma

but its giving the below error.

SQL0401N The data types of the operands for the operation "IN" are not compatible. SQLSTATE=42818

ARWinner · #2 (**permalink**) 03-24-09, 09:57

Try the LOCATE function.

Andy

laknar · #3 (**permalink**) 03-24-09, 22:57

column1 column2
a a,b,c,d,e
b f,g,h,i,j,k

i used locate and it worked for two values

select * from test where Column1 in(LEFT(COLUMN2,LOCATE(',',COLUMN2)-1),RIGHT(COLUMN2,LOCATE(',',COLUMN2)-1))

how can i select when column2 contains multiple values seperated by comma

really dont know how many values seperated by comma.

i have to give during runtime.