title: Can I replace the column value while selecting and then use it for comparison with a column value from another table?
body: <p>Basically, I want to compare two columns of two different (let's call them source and destination) tables but while selecting from first table, I want to replace the value to something else because source table might have some garbage value which should be sanitised (replaced with a valid value based on some conditions) before I actually compare it with the second table. For example,</p>
  <p>Table 1</p>
  <pre><code>First_Name: &quot;Aman&quot;
  Roll_Number: &quot;00000&quot;
  
  First_Name: &quot;Raju&quot;
  Roll_Number: &quot;13456&quot;
  </code></pre>
  <p>Table 2</p>
  <pre><code>Name: &quot;Aman&quot;
  Roll_Number: &quot;11223&quot;
  
  Name: &quot;Raju&quot;
  Roll_Number: &quot;13456&quot;
  </code></pre>
  <p>I tried using this query:</p>
  <pre><code>select t1.First_Name, t1.Roll_Number, t2.Name, t2.Roll_Number, 
          CASE WHEN (t1. Roll_Number is Null or t1.Roll_Number = ' ' 
                      or t1.Roll_Number = '' or t1.PARTNER_ID = '00000') 
              THEN '11223' 
              ELSE t1.Roll_Number 
          END AS ROLL_NUM_TO_BE_COMPARED 
  from SOURCE_STUDENT t1, DESTINATION_STUDENT t2 
  where t1.First_Name = t2.Name 
  and (t1.Roll_Number != t2.Roll_Number OR t2.Roll_Number != ROLL_NUM_TO_BE_COMPARED);
  </code></pre>
  <p>Context:
  Data is getting migrated from source table to destination table by some service. There should be a script which compares the data (source vs destination). Migration logic already considers &quot;00000&quot; as &quot;11223&quot; and hence the comparison logic should also consider the same. Instead of updating the source, I want to just replace garbage with &quot;11223&quot; and then compare it with destination to get the real mismatched values.</p>