title: Complex Case Statement in Oracle SQL
body: <p>I have a scenario where i have 4 columns - SUBBRAND, COLLECTIONS, PLCYCLE, COLORFAMILY and i have to assign priority to records:</p>
  
  <ol>
  <li>If all 4 of them have valid values(not blank or not null) - Priority '1' ;</li>
  <li>If any 3 of them have valid values(not blank or not null) - Priority '2' ;</li>
  <li>If any 2 of them have valid values(not blank or not null) - Priority '3' ;</li>
  <li>If any 1 of them have valid values(not blank or not null) - Priority '4' .</li>
  </ol>
  
  <p>Is writing a case statement with all possible combinations the only way to get this done or is there any simple way of doing it ?</p>
  
  <p>sample code:</p>
  
  <pre><code>CASE WHEN SUBBRAND IS NOT NULL AND COLLECTIONS IS NOT NULL 
       AND PLCYCLE IS NOT NULL AND COLORFAMILY  IS NOT NULL
       THEN 1 AS PRIORITY,
       WHEN SUBBRAND IS NOT NULL AND COLLECTIONS IS NOT NULL 
       AND PLCYCLE IS NOT NULL OR
       WHEN COLLECTIONS IS NOT NULL AND PLCYCLE IS NOT NULL 
       AND COLORFAMILY IS NOT NULL OR
       WHEN PLCYCLE IS NOT NULL AND COLORFAMILY IS NOT NULL
       AND WHEN SUBBRAND IS NOT NULL OR
       so on....
       THEN 2 AS PRIORITY
       and other conditions..
  END PRIORITY
  </code></pre>
  
  <p>Thanks in Advance for your Help !</p>