Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2008
    Posts
    94

    Unanswered: Selecting values based on other column

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try the LOCATE function.

    Andy

  3. #3
    Join Date
    Jul 2008
    Posts
    94
    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.

  4. #4
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    Quote Originally Posted by laknar
    i used locate and it worked for two values
    That is because LOCATE function returns the location of the first occurrence of the matching string

    Quote Originally Posted by laknar
    how can i select when column2 contains multiple values seperated by comma
    In such a scenario what should be the output if there are multiple occurrences. An example would help us in a long way.
    IBM Certified Database Associate, DB2 9 for LUW

  5. #5
    Join Date
    Dec 2005
    Posts
    273
    I think, ARWinner meant something like:

    SELECT * FROM test
    WHERE LOCATE (',' CONCAT COL1 CONCAT ',' , ',' CONCAT COL2 CONCAT ',') > 0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •