Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2011
    Posts
    10

    Unanswered: Concatenating sub selects

    Hi all,

    DB2 noob here. Sorry, afraid I don't have DB2 version number or anything, hoping this can be answered without it.

    I want to concatenate two subselects to use in a WHERE clause. So, join or union is appropriate.

    Here is my data

    Table 1
    Column1,Column2
    ABC,123

    Table 2
    Column1, Column 2
    Hello, ABC123

    I need to retrieve column1 from table 2 where the column2 is a concatenation of column1 and 2 from table 1.

    what ive tried:

    SELECT COLUMN1 FROM TABLE2 WHERE COLUMN2 IN
    ((SELECT COLUMN1 FROM TABLE1 WHERE BLA BLA)
    UNION
    (SELECT COLUMN2 FROM TABLE1 WHERE BLA BLA))

    SELECT COLUMN1 FROM TABLE2 WHERE COLUMN2 IN
    ((SELECT COLUMN1 FROM TABLE1 WHERE BLA BLA)||(SELECT COLUMN2 FROM TABLE1 WHERE BLA BLA))

    Any help much appreciated.

    cheers
    Paul

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Code:
    select t2.col1 from t1,t2 where t2.col2=t1.col1||t1.col2
    Quote Originally Posted by P4ul View Post
    Hi all,

    DB2 noob here. Sorry, afraid I don't have DB2 version number or anything, hoping this can be answered without it.

    I want to concatenate two subselects to use in a WHERE clause. So, join or union is appropriate.

    Here is my data

    Table 1
    Column1,Column2
    ABC,123

    Table 2
    Column1, Column 2
    Hello, ABC123

    I need to retrieve column1 from table 2 where the column2 is a concatenation of column1 and 2 from table 1.

    what ive tried:

    SELECT COLUMN1 FROM TABLE2 WHERE COLUMN2 IN
    ((SELECT COLUMN1 FROM TABLE1 WHERE BLA BLA)
    UNION
    (SELECT COLUMN2 FROM TABLE1 WHERE BLA BLA))

    SELECT COLUMN1 FROM TABLE2 WHERE COLUMN2 IN
    ((SELECT COLUMN1 FROM TABLE1 WHERE BLA BLA)||(SELECT COLUMN2 FROM TABLE1 WHERE BLA BLA))

    Any help much appreciated.

    cheers
    Paul
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try this:

    Code:
    select column1 from table_2 where column2 in (select column1||column2 from table_1 where ...)
    Andy

  4. #4
    Join Date
    Jul 2011
    Posts
    10
    Thanks Guys. Both methods work. Do you know which would be best from a performance point of view?

    Cheers
    Paul

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    It depends. You can EXPLAIN both statements and see that way. If I had to guess, I would say the join would work better.

    Andy

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    @Paul if you do not have any other join conditions, both options may be equally bad. With a concat function in the join condition, indexes will not be used.

    hth
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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