Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2006
    Location
    Columbus, OH
    Posts
    69

    Unanswered: Joining on concatenation of fields

    AIX 5L
    DB2 8.2

    Table A looks like this

    Column1 Column2 Column3
    123141 1 1
    123142 1 2

    Table B looks like this

    Column1 ....
    12314111
    12314212


    The primary key of Table B is all three columns in TableA. So I'm trying to figure an easy way to join these two tables using this information.

    Thanks,

    Charlie

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    SELECT ...
    from tablea as a
    inner join tableb as b on (b.column1 = a.column1||columns2||column3)
    where ..

    Andy

  3. #3
    Join Date
    Jul 2008
    Posts
    94
    try this

    SELECT ...
    from tablea as a
    inner join tableb as b on (b.column1 = column1||columns2||column3)
    where ..

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by ARWinner
    SELECT ...
    from tablea as a
    inner join tableb as b on (b.column1 = a.column1||column2||column3)
    where ..
    This *might* give incorrect results (viz. empty result table), depending on the datatypes of the columns; the above solution only works when a.column1 is either of datatype VARCHAR(n) or CHAR(6), and a.column2 is of datatype VARCHAR(n) or CHAR(1).
    (Presuming that all data in your table is similar in width to what you example tells us ...)
    Three alternative solutions (without the above restrictions):
    Code:
    SELECT ...
    from a inner join b on b.column1 = rtrim(a.column1)||rtrim(column2)||column3
    where ..
    (Now, the restriction being that b.column1 does not have embedded blanks...)
    Code:
    SELECT ...
    from a inner join b on
          substr(b.column1,1,6) = a.column1 AND
          substr(b.column1,7,1) =   column2 AND
          substr(b.column1,8,1) =   column3
    where ..
    Finally, you may consider adding three (redundant) columns to table b, containing those substr()s; this might give a much more performant query (depending, of course, on the sizes of the two tables).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Sep 2006
    Location
    Columbus, OH
    Posts
    69
    Thanks for the response. I got the concatenation query to work. However, I found this function is time costly to the overall query. I think I might add a column or table and perform the concatenation at INSERT instead.

Posting Permissions

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