title: Append a zero to value if necessary in SQL statement DB2
body: <p>I have a complex SQL statement that I need to match up two table based on a join.  The the intial part of the complex query has a location number that is stored in a table as a Smallint and the second table has the Store number stored as a CHAR(4).  I have been able to cast the smallint to a char(4) like this:</p>
  
  <pre><code>CAST(STR_NBR AS CHAR(4)) AND LOCN_NBR
  </code></pre>
  
  <p>The issue is that because the Smallint suppresses the leading '0' the join returns null values from the right hand side of the LEFT OUTER JOIN.</p>
  
  <p>Example</p>
  
  <pre><code>Table set A(Smallint)             Table Set B (Char(4))
  |   96     |                      |  096     |
  |   97     |                      |  097     |
  |   99     |                      |  099     |
  |  100     |  &lt;- These return  -&gt; |  100     |
  |  101     |  &lt;- These return  -&gt; |  101     |
  |  102     |  &lt;- These return  -&gt; |  102     |
  </code></pre>
  
  <p>I need to add make it so that they all return, but since it is in a join statement how do you append a zero to the beginning and in certain conditions and not in others?</p>