0 Replies Latest reply: Mar 7, 2012 11:09 AM by Xavier Van hoorebeke RSS

    Check individual lines against the interval

      What is the fastest way to check a set of individual data lines against a from to range ?

       

      Batch:

      LOAD

           batch_number,

           invoice_number_from,

           invoice_number_to

      resident batch_header;

       

      Invoices:

      LOAD

           batch_number,

           invoice_number,

           ...

      resident invoice_header;

       

      Table Batch contains a range of invoices asked with a from and to invoice number. 1 row per batch_number

      Table Invoices containts my actual invoices. 1 row per batch_number / invoice_number

      However, for some reason, table Invoices sometimes does not contain some invoices as they cannot be processed by the interface process.

       

      How can I easily script this ? I have the invoice_number_to and invoice_number_from in table Batch and I want to check which invoice numbers in between the range cannot be found in the table Invoices. Below is an example

       

      Table Batch :

      batch_number     invoice_number_from     invoice_number_to

      123456               12568                           12574    

       

       

      Table Invoices:

      batch_number     invoice_number

      123456               12568

      123456               12569

      123456               12571

      123456               12572

      123456               12573

       

      The result I want to get is a table with 12570 and 12574 as these numbers are within the interval but cannot be found in table Invoices

      I guess the easiest way might be to transform the Table Batch into a multi row table and then check the generated invoice numbers against the Invoices table, but what is the easiest way to do this ?