AnsweredAssumed Answered

SQL JOIN problems

Question asked by Gingernut on Jan 23, 2013

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")

Outcomes