3 Replies Latest reply: Feb 3, 2014 1:48 PM by whiteline _ RSS

    can you help me with two complex queries?

    Marko Rakar

      can you help me with two complex queries? (complex at least to me).

       

      Here is the problem (simplified, but this is more or less database I have):

       

      Invoice_header:

      Load * INLINE

      [

          Invoice_ID, Name, Total_amount

          1,   Zeus, 1000,

          2,   Thor,  1500,

          3,   Venus, 2700,   ];

        

      Invoice_items:

      Load * INLINE

      [

          Invoice_ID, item_name, Item_price, Item_color, Item_list_price

          1,   wheel, 500, black, 550

          1,   seat, 500, black, 550

          2,   chain, 700, black, 700

          2,   tire, 800, white,  800 ];

       

      I need to make two queries which will help me find following:

      • I want to find all invoices where we have mismatch between item colors; idea is to find all invoices which were issued but they have different color defined for items); rule is that all items should have same color (color is not defined in invoice header, although I see that I can find a way to define correct color for each invoice)
      • I want to find a way to find all invoices where total amount in header is not the same like sum of items list price (I was thinking of creating running totals of items list price somehow but failed to find a proper way)

       

      This is not actual database structure but I can derive data in this way (this structure is derived from multiple databases).

       

      Any ideas, pointers?

       

      thanks

       

      p.s. I know this is not a standard query or even a situation which should happen in the first place, but what I am trying to do is to find if the database was tampered with and I have to reconstruct results and compare it with what was supposed to happen