title: Using Case Statement to Compare and Exclude
body: <p>(Working in Teradata) I've been tasked with comparing the values of any of 3 specific columns to 3 other specific columns. I would include the record if a value from one of the initial 3 specified columns match the value of any of the second set of 3 columns.</p>
  
  <p>Columns 
  Diag_Prefix, Diag2_Prefix, and Diag3_Prefix
  are being compared to
  Xref_Diag_Prefix, Xref_Dia2g_Prefix , Xref_Diag2_Prefix</p>
  
  <p>So, as an example, if Diag_Prefix contains value A10 and that value is also contained in Xref_Diag_Prefix, OR Xref_Dia2g_Prefix, OR Xref_Diag2_Prefix then I would keep the record, otherwise I would exclude it.</p>
  
  <p>I believe I have this part down as follows:</p>
  
  <pre><code>AND (CASE WHEN Diag_Prefix = Xref_Diag_Prefix OR Diag_Prefix = Xref_Diag2_Prefix OR Diag_Prefix = Xref_Diag3_Prefix
      OR Diag2_Prefix = Xref_Diag_Prefix OR Diag2_Prefix = Xref_Diag2_Prefix OR Diag2_Prefix = Xref_Diag3_Prefix
      OR Diag3_Prefix = Xref_Diag_Prefix OR Diag3_Prefix = Xref_Diag2_Prefix OR Diag3_Prefix = Xref_Diag3_Prefix
      THEN 'I' ELSE 'E' END) = 'I'
  </code></pre>
  
  <p>My problem is that some of the values in these columns are 'NULL' and have been populated with the value of 'UNK' in such cases. These records are being included with my CASE statement above, so I'm trying to change the UNK values into something unique for each field, as follows:</p>
  
  <pre><code>    /* Converting UNK values to exclude from subsequent comparison case statement */
      AND (CASE WHEN Diag_Prefix = 'UNK' THEN 'UNKD1' ELSE Diag_Prefix END)
      AND (CASE WHEN Diag2_Prefix = 'UNK' THEN 'UNKD2' ELSE Diag2_Prefix END)
      AND (CASE WHEN Diag3_Prefix = 'UNK' THEN 'UNKD3' ELSE Diag3_Prefix END)
      AND (CASE WHEN Xref_Diag_Prefix = 'UNK' THEN 'UNKX1' ELSE Xref_Diag_Prefix END)
      AND (CASE WHEN Xref_Diag2_Prefix = 'UNK' THEN 'UNKX2' ELSE Xref_Diag2_Prefix END)
      AND (CASE WHEN Xref_Diag3_Prefix = 'UNK' THEN 'UNKX3' ELSE Xref_Diag3_Prefix END)
  </code></pre>
  
  <p>But this is giving me an error that states: "...expected something like a 'SUCCEEDS', 'MEETS', 'PRECEDES', 'IN', or 'CONTAINS' keyword between ')' and the 'AND' keyword." </p>
  
  <p>I'm not sure if it would be easier to just create a VT somehow. Any help would be greatly appreciated.</p>