title: Merge and match data in Oracle
body: <p>I am trying to collect data from 2 separate schemas. Due to the complexity of a singular query that would obtain all of the data would crash the database server, I made separate tables and filled them with the all of the data that I need. Now I just need to merge the data and match the columns so that I can remove any duplicate rows later. Below are the two queries that I created to return results that I am looking for. My problem is that when I run the second query, I get the following error :</p>
  
  <pre><code>   Error at Command Line:7 Column:4
     Error report:
     SQL Error: ORA-00904: "BOF_FOR_CHUCKSIMPSON"."BATCH_ID": invalid identifier
     00904. 00000 -  "%s: invalid identifier"
     *Cause:    
     *Action
  </code></pre>
  
  <p>Can someone help me to understand what is going on?</p>
  
  <p>Query 1</p>
  
  <pre><code> CREATE TABLE BOF_FOR_CHUCKSIMPSON AS
      SELECT 
      bbp.SUBCAR "Treadwell", HMM.LADLE "BOTTLE CAR",
      bbp.BATCH_ID "Batch ID",
      TO_CHAR(bbp.START_POUR, 'yyyy/mm/dd hh24:MI:ss') "Start Pour Time",
      TO_CHAR(bbp.END_POUR, 'yyyy/mm/dd hh24:MI:ss') "End Pour Time",
      TO_CHAR(hmm.sched_cast_date, 'yyyy/mm/dd hh24:MI:ss') "Sched Cast Date"
      FROM bof_batch_pour bbp, rouge.hmm_iron_cast_ladle@idbs1 hmm
      WHERE bbp.subcar=hmm.ladle
      AND TO_CHAR(hmm.sched_cast_DATE, 'yyyymmddhh24MIss') = TO_CHAR(bbp.SCHED_CAST_DATE, 'yyyymmddhh24MIss')
      AND bbp.START_POUR&gt;='25-MAY-11';
      CREATE TABLE BOF_FOR_CHUCKSIMPSON2 AS
      SELECT bofcs.BATCH_ID "Batch ID", bofcs.sample_time "Sample Time",
      bcs.SILICON "Si", bcs.SULPHUR S, bcs.MANGANESE "Mn", bcs.PHOSPHORUS P,
      bofcs.TEMPERATURE "Temperature"
      FROM bof_chem_sample bcs, bof_celox_sample bofcs
      WHERE bofcs.SAMPLE_CODE                                   = bcs.SAMPLE_CODE
      AND bofcs.BATCH_ID                                        = bcs.BATCH_ID
      AND bofcs.TEMPERATURE                                    &gt; 0
      AND bcs.SAMPLE_CODE                                       = 'D1'
      AND bofcs.SAMPLE_CODE                                   = bcs.SAMPLE_CODE
      AND bofcs.sample_time&gt;'25-may-11'
      AND bofcs.sample_time&lt;sysdate
  </code></pre>
  
  <p>Query 2</p>
  
  <pre><code>    merge into bof_FOR_cHUCKSIMPSON2
      using bof_FOR_cHUCKSIMPSON
      on (bof_FOR_cHUCKSIMPSON.batch_id=bof_FOR_cHUCKSIMPSON2.batch_id)
      when matched
      then
       update
      SET bof_FOR_cHUCKSIMPSON.BATCH_ID = bof_FOR_cHUCKSIMPSON2.bATCH_ID
      WHEN NOT MATCHED
         THEN
            INSERT (bof_FOR_cHUCKSIMPSON2.bATCH_ID
                VALUES (99999);
  </code></pre>