title: WHILE / LOOP inside CASE expression SQL - ORACLE
body: <p>I am testing a select statement containing a CASE expression and I need to add a WHILE / LOOP inside one of the WHEN conditions.</p>
  
  <p>The logic from this statement will eventually be included inside a PL/SQL script but I want to know it´s working 100% before I include it in the procedural.</p>
  
  <p>The logic serves to query a zip/postal code inside an XML package found in a clob column.</p>
  
  <p>INTENDED ACTION:</p>
  
  <p>if ZIP > 5 digits then remove everything after 5th char.</p>
  
  <p>if ZIP &lt; 5 digits then add '0' until the length reaches 5 char.</p>
  
  <p>else do nothing.</p>
  
  <p>WHAT IS NEEDED:</p>
  
  <p>The logic that adds a '0' using contact() function needs to reiterate until the new zip reaches a length of 5 char. As it is, the query only adds up to one '0', but this needs to repeat as many times as required for total char length to = '5'. My attempts at adding a LOOP or a WHILE all result in incorrect formating according to Oracle SQL.</p>
  
  <p>QUERY:</p>
  
  <pre><code>SELECT ORDER_NUMBER, 
  EXTRACTVALUE(xmltype(PACKAGE), '/Package/fCustomerVO/invoiceAddress/zip') as "ZIPS_INV",
  LENGTH(EXTRACTVALUE(xmltype(PACKAGE), '/Package/fCustomerVO/invoiceAddress/zip')) as "ZIPS_LEN",
  CASE
  /* add digits to zip WHILE/LOOP NEEDED HERE */
    WHEN LENGTH(EXTRACTVALUE(xmltype(PACKAGE), '/Package/fCustomerVO/invoiceAddress/zip')) &lt; 5 
      THEN concat(EXTRACTVALUE(xmltype(PACKAGE), '/Package/fCustomerVO/invoiceAddress/zip'), '0')
  /* subtract digits to zip */
    WHEN LENGTH(EXTRACTVALUE(xmltype(PACKAGE), '/Package/fCustomerVO/invoiceAddress/zip')) &gt; 5 
      THEN substr(EXTRACTVALUE(xmltype(PACKAGE), '/Package/fCustomerVO/invoiceAddress/zip'), 1, 5)    
    ELSE 
      'correct format'
  END as "FIXED_ZIP"
  FROM quarantine;
  </code></pre>