Frank

so the record in question is written via tableupdate or is SET('SQLBUFFERING') ON?

Does c_PaymentsReport exists before the SELECT?

If you simplfy the SELECT (i.e. just getting single records from single tables NOFILTER) do you get the result you expect?

HTH
Agnes

AFK
>Hi Agnes,
>
>>do you use buffering? Are you shure that the INTO clause will nothing mix up?
>>
>
>yes, I use buffering.
>
>>Maybe you provide the full SELECT command.
>>
>
>This is the SQL:
>
>

>m.liPaymentId = c_Payments.iPaymentid && the correct ID is returned here
>
>select Payments.IPAYMENTID, Payments.DPAID, Payments.IPATIENTID, Payments.IPAYTYPEID, Payments.NAMOUNT, ;
>  Payments.CCHEQUENO, Payments.DDEPOSITED, Payments.CPOSTREF, Payments.CRECEIPTNO,;
>  PaymentsDetails.NAMOUNT as PayDetAmount, IPAYDETID, sales.cinvoiceno, Sales.dSale, ;
>  Sales.nVat, Sales.nTotal, Sales.nBalance, PaymentsDetails.nBalDue,;
>  Patients.cSurname, Patients.cFirstName, Patients.cMidName, Patients.cTitle,  atients.cPatientNo, ;
>  PADR(ALLTRIM(Patients.cSurname) + "," + ALLTRIM(Patients.cFirstName) + " " + ALLTRIM(Patients.cMidName), 100) as cPatient,;
>  PayTypes.cDesc as cType, PatientAddresses.cAddress1, PatientAddresses.cAddress2, PatientAddresses.cTown,;
>  PatientAddresses.cPostCode, PatientAddresses.cCountry,;
>  SalesDetails.cCode, SalesDetails.cdesc, SalesDetails.cDiscount, SalesDetails.cIntDesc,;
>  SalesDetails.cVATCode, SalesDetails.iQty, SalesDetails.nAmount as nDetAmount, SalesDetails.nDiscAmt, ;
>  SalesDetails.nPrice, SalesDetails.nVATAmount, SalesDetails.nVATRate, sales.iSaleId,;
>  Sales.cInvoiceTo, Sales.cInvoiceToAddress, Sales.cInvoiceToPhone;
> FROM Payments;
>  inner join PaymentsDetails ON Payments.iPaymentId = PaymentsDetails.iPaymentId;
>  inner join Sales ON PaymentsDetails.iSaleId = sales.iSaleId;
>  inner join SalesDetails ON Sales.iSaleId = salesDetails.iSaleId;
>  INNER JOIN Patients ON Payments.iPatientId = Patients.iPatientId;
>  INNER JOIN Lookups as PayTypes ON Payments.IPAYTYPEID = PayTypes.ILookupID;
>  LEFT OUTER JOIN PatientAddresses ON Payments.iPatientId = PatientAddresses.iPatientid ;
> WHERE Payments.iPaymentId = m.liPaymentId;
> ORDER BY cInvoiceNo ;
> INTO CURSOR c_PaymentsReport
>