0 Replies Latest reply on Jan 23, 2013 8:58 AM by Gingernut

    SQL JOIN problems

    Gingernut

      I am trying to do a SQL report and have been successful with this

       

       

      // Name: Commission reports Colour

      // Date: 1/23/2013

      ExecuteSQL ("SELECT
      ''||invline."Date" ,
      invline.Model ,
      invline."_InvNo" ,
      invline."Col Meter Used" ,
      m.Name , etc etc for various fields in the Machines (m) and InvoiceLine (invline) tables

       

      FROM

      CustomerInvoiceLineItem invline

      INNER JOIN Machine m ON invline."_Mach ID No"=m."_ID"

      WHERE

      "Sales Rep" LIKE ? AND NominalCode LIKE ? AND MonthYearDate LIKE ? AND "BW Meter Used" > ?

      ORDER BY

      m.ContractType ASC , m.Name";"|";"";

      "POB";

      "1MR";

      "February 2012";

      0)

       

      But I also want to add the field from Invoice which denotes whether the invoice is paid or not IE Paid Status so I thought I can add another JOIN between Invoice and InvoiceLine and Select Paid Status field from Invoice -like this

       

       

      // Name: Commission reports Colour

      // Date: 1/23/2013

      ExecuteSQL ("SELECT ''||invline."Date" , invline.Model , invline."_InvNo" , invline."Col Meter Used" , invline.CopyCostColour , invline."Commission Min Colour" , invline."Commission Amt Col" , m.Name , invline."Commission Rate Colour" , m."Sales Rep" , invline."BW Meter Used" , invline.CopyCostBlack , invline."Commission Min Black" , invline."Commission Amt Blk" , invline."Commission Rate Black" , m.PeriodicAverageCopiesUSedBlack , m.PeriodicAverageCopiesUSedColour ,
      ci.PaidStatus **************************** new line for field

       

       

       

       

      FROM

      CustomerInvoiceLineItem invline

      INNER JOIN Machine m ON invline."_Mach ID No"=m."_ID"
      INNER JOIN CustomerInvoiceLineItem invline ON ci."_InvNo"=invline."_InvNo" ***************************** new line

       

       

       

       

      WHERE

      "Sales Rep" LIKE ? AND NominalCode LIKE ? AND MonthYearDate LIKE ? AND "BW Meter Used" > ?

      ORDER BY

      m.ContractType ASC , m.Name";"|";"";

      "POB";

      "1MR";

      "February 2012";

      0)

       

      But this does not work

      Can anyone please help me? Beverly???

       

      PS have also tried adding in the table alias in the WHERE clause still no joy (remembered something Wim showed me previously)

      // Name: Commission reports Colour

      // Date: 1/23/2013

      ExecuteSQL ("SELECT ''||invline."Date" , invline.Model , invline."_InvNo" , invline."Col Meter Used" , invline.CopyCostColour , invline."Commission Min Colour" , invline."Commission Amt Col" , m.Name , invline."Commission Rate Colour" , m."Sales Rep" , invline."BW Meter Used" , invline.CopyCostBlack , invline."Commission Min Black" , invline."Commission Amt Blk" , invline."Commission Rate Black" , m.PeriodicAverageCopiesUSedBlack , m.PeriodicAverageCopiesUSedColour , ci.PaidStatus

      FROM

      CustomerInvoiceLineItem invline

      INNER JOIN Machine m ON invline."_Mach ID No"=m."_ID" JOIN CustomerInvoiceLineItem invline ON ci."_InvNo"=invline."_InvNo"

      WHERE invline.NominalCode LIKE ? AND invline.MonthYearDate LIKE ? AND invline."BW Meter Used" > ? AND m."Sales Rep" LIKE ?

       

       

      ORDER BY

      m.ContractType ASC , m.Name";"|";"";

      "1MR";

      "February 2012";

      0;

      "POB")