title: USING CASE AS PART OF JOIN ORACLE SQL
body: <p>I have a query in Oracle SQL Developer to grab data from a raw table (all the data on the raw table are varchars) where I need to &quot;clean&quot; the data when I select it.  Have to use a CASE statement to get the store number because sometimes that data isn't in the STORENBR column but can be found in a substring of another column -</p>
  <pre><code>SELECT
      CASE WHEN m.STORENBR = '0' AND (SUBSTR(m.SENDING_QMGR, 1, 5) = 'PDPOS')
          THEN TO_NUMBER((SUBSTR(m.SENDING_QMGR, 8, 4)))
      WHEN m.STORENBR = '0' AND (SUBSTR(m.SENDING_QMGR, 1, 8) = 'PROD_POS')
          THEN TO_NUMBER((SUBSTR(m.SENDING_QMGR, 9, 4)))
      ELSE TO_NUMBER(NVL(m.STORENBR, '0'))  
      END AS STORENBR, 
  TO_NUMBER(NVL(m.CONTROLNBR,'0')) AS CONTROLNBR, 
  TO_NUMBER(NVL(m.LINENBR,'0')) AS LINENBR,
  TO_DATE(m.TRANDATE,'YYYY-MM-DD') AS TRANDATE,
  TO_NUMBER(NVL(m.NUMMISPRINTED,'0.00'),'99.99')  AS NUMMISPRINTED
  FROM MISPRINTS_RAW m
  WHERE TO_DATE(m.TRANDATE,'YYYY-MM-DD') = '15-MAR-21'
  ORDER BY m.STORENBR;
  </code></pre>
  <p>Now I need to also pull an account number from another table (TRANSACTIONS t - not a raw table, so I don't need any CASE or TO_NUMBER to pull data) but I need to join that table on STORENBR, CONTROLNBR, and LINENBR.  So how do I use that CASE statement as part of the join to JOIN m.STORENBR on t.STORENBR?</p>