title: Teradata - Adding preceding zero and casting as varchar(50)
body: <p>I have the following query:</p>
  
  <pre><code>SELECT
    s.cola, s.colb, t.colc, t.cold, u.cole, u.colf, u.colg, u.colh, u.coli, u.colj, u.colk, u.coll 
  FROM table1 s
  INNER JOIN table2 t
    ON s.colb = t.colc
  INNER JOIN table3 u
    ON u.colm = CAST(t.cold AS varchar(50))
  WHERE cast(s.cola as date) between date '2017-11-06' and date '2017-11-10'
  ORDER BY 3
  </code></pre>
  
  <p>Here, in the last joining condition, <code>u.colm</code> is of type <code>varchar(50)</code> and <code>t.cold</code> is of type <code>decimal(10, 0)</code>. I cannot cast <code>u.colm</code> as <code>decimal(10, 0)</code> because some of the outdated values in that column are not purely numbers. Now, if I run the query as shown above, it will return a blank table because the common values in <code>u.colm</code> has a preceding/leading zero while <code>t.cold</code> doesn't have that zero. I tried the following:</p>
  
  <p>1)  <code>on u.colm = '0' + cast(t.cold as varchar(50))</code></p>
  
  <p>This gave the error: 
  <code>[Teradata Database] [2620] The format or data contains a bad character.</code> </p>
  
  <p>2) <code>on u.colm = right('0000000000' + cast(t.cold as varchar(50)), 50)</code></p>
  
  <p>This gave the error:
  <code>[Teradata Database] [9881] Function 'TD_RIGHT' called with an invalid number or type of parameters</code></p>
  
  <p>The question, answers and comments in <a href="https://stackoverflow.com/a/47928199/4834108">link1</a> will provide some more context. I am pretty new to Teradata and don't have much idea on how to resolve this issue. Kindly help.</p>