title: Nateeza Aginity workbench - concatenating two columns in a join
body: <p>Hi All currently I have an issue where i have two take two columns in table A, combine them and compare in join against one column in table B. The two columns in table A are defined as characters(3) and Charters(5), while the column in table B is defined as varchar(600)</p>
  
  <p>My existing query looks something like this but it is not working</p>
  
  <pre><code>SELECT DISTINCT A.CLIENT_NO, TRIM(A.BRANCH_NO)||TRIM(A.ACCT_NO) as 
  ACCOUNT_NUMBER, B.ID1 from TABLEA A
  LEFT JOIN TABLE B ON CONCAT(TRIM(A.BRANCH_NO), TRIM(A.ACCT_NO)) = B.ACCT_NUM
  </code></pre>
  
  <p>The above does not work, also i have tried</p>
  
  <pre><code>SELECT DISTINCT A.CLIENT_NO, TRIM(A.BRANCH_NO)||TRIM(A.ACCT_NO) as 
  ACCOUNT_NUMBER, B.ID1 from TABLEA A
  LEFT JOIN TABLE B ON TRIM(A.BRANCH_NO)+TRIM(A.ACCT_NO) = B.ACCT_NUM
  </code></pre>
  
  <p>I have also tried explicit type casting and that didnt work either</p>
  
  <pre><code>SELECT DISTINCT A.CLIENT_NO, TRIM(A.BRANCH_NO)||TRIM(A.ACCT_NO) as 
      ACCOUNT_NUMBER, B.ID1 from TABLEA A
      LEFT JOIN TABLE B ON CAST(TRIM(A.BRANCH_NO) AS VARCHAR(600))+CAST(TRIM(A.ACCT_NO)AS VARCHAR(600)) = B.ACCT_NUM
  </code></pre>
  
  <p>Can some one please as to what I am missing ? Or Am i just concatenating the two columns incorrectly. The error i keep getting is to use explicit cast error or function concat cannot be identified. </p>
  
  <p>ANy help would be greatly appreciated.</p>