title: How to write single query instead of using loop in oracle?
body: <p>I would like to write a query based on some conditions as shown below. Could you please help me out in this regard?</p>
  
  <blockquote>
  <pre><code>RecordID          MergeRecordID   Status          StatusTime
  3700432           289015          Invoiced        25-May-09
  3700433           289015          Invoiced        25-May-09
  3700434           289015          Invoiced        08-Dec-09
  3700435           289015          Error           08-Dec-09
  </code></pre>
  </blockquote>
  
  <p>I have a table called <code>billingValues_tbl</code> where RecordID is primary key. Please note that MergeRecordID contains mutiple Record IDs.</p>
  
  <p>Step 1: I would like to sort based on MergeRecordID. In above example we will get 4 records.</p>
  
  <p>Step2: All records should have the Status <code>Invoiced</code>. If the value in column Stattus is other than 'Invoiced', I should skip that mergerecordid i.e. I should not take this Record into consideration. Then I will go for another mergerecordid/recordids combination. I have written query as shown below but its not giving me desired results.
  </p>
  
  <pre><code> SELECT RECORDID,MERGERECORDID,STATUS,STATUSTIME
  
   FROM BE242.BILLINGVALUES_TBL BV1
  
   WHERE BV1.MERGERECORDID IN ( SELECT BV2.MERGERECORDID
  
                                    FROM BILLINGVALUES_TBL BV2
  
                                    WHERE BV2.RECORDID = BV1.RECORDID
  
                                      AND BV2.MERGERECORDID = BV1.MERGERECORDID
  
                                      AND BV2.STATUS IN ('Invoiced', 'Cancelled') )
  
   ORDER BY BV1.MERGERECORDID  DESC;
  </code></pre>
  
  <p>Please any one help me out as soon as possible?</p>