1 Reply Latest reply: Jul 8, 2011 3:27 PM by Siarhei Karpusenka RSS

    Merging Different Tables

      Dear All,

       

      Kindly give me code for below query. We are working as an intermediate between customer and supplier.

       

      In our system, we have below data for each customer and each supplier..

       

      Customer Table:

      Customer ID     Customer Name     Customer Currency     Customer Country

       

      Where Customer ID is unique.

       

      Supplier Table:

      Supplier ID     Supplier Name     Supplier Currency     Supplier Country

       

      Supplier ID is unique.

       

      Based on prices available from different suppliers on respective brands, we are sending quotation to our customers. These data are saved in below table. Here Our Part NO is unique but can be associated with more than one brand. We can say one part number is having three or more different quality. Example shown below. But there will be only one Our Part NO for each Customer Part NO

       

      Our Part NO    Brand    Quoted Price     Customer Part NO

         ABCXYZ            A                  10                         1234 567

         ABCXYZ            B                  20                         1234 567

         ABCXYZ            C                  30                         1234 567

       

      Quotation Table:

      Customer ID     Our Part NO     Customer Part NO     Brand     Quoted Price

       

      Also, for each supplier, we have purchase cost table.

       

      Supplier Price Table:

      Supplier ID     Our Part NO     Brand     Supplier Part NO     Purchase Cost

       

      Based on our quotations, customers are sending orders as below...

       

      Customer Order Table:

      Customer ID     Customer Sales Order NO     Customer Part NO     Brand     Qty

       

      Finally we are sending orders to our supplier based on customers' orders. We have below data stored for our Purchases/Sales.

       

      Purchase Order Table:

      Purchase Order NO     Supplier ID     Supplier Part NO     Our Part NO     Brand     Qty     Customer ID     Customer Sales Order NO

       

      I need code to get table with following columns.

       

      Final Table:

      Purchase Order NO
      Supplier ID
      Supplier Name
      Supplier Currency
      Supplier Country
      Supplier Part NO
      Our Part NO
      Brand
      Qty
      Purchase Cost
      Purchase Total
      Customer ID
      Customer Name
      Customer Currency
      Customer Country
      Customer Part NO
      Customer Sales Order NO
      Quoted Price
      Invoice Total

       

      Where Purchase Total = Qty * Purchase Cost and Invoice Total = Qty * Quoted Price

       

      Also, if possible, I want unique line number in Final Table.

       

      Thanks in advance.

        • Re: Merging Different Tables

          Look into (Left/Right/etc) Join Load. Depending on if you're missing some data in fields, you might prefer one type of Join over another. Create a new table by loading from a resident table, and then joining the existing ones (2 at a time): if X is the new table, and you need to combine tables A, B, and C into X - join X + A -> XA, XA + B -> XAB, XAB + C -> XABC.

           

          So you load the needed fields from A into X with "Load ...fields... Resident A;" Then, for example, "Left Join (X) Load ...fields... Resident B;", and so forth.

           

          For the fields that are calculated:

          Qty * [Purchase Cost] AS [Purchase Total],

          Qty * [Quoted Price] AS [Invoice Total]

           

          Hope this helps,

           

           

          Siarhei K.