Wants customer refernce number field in Sales Invoice Query

[Former Member]

Hello Experts,

Below is the query for which i wants customer reference number field..

Please help

SELECT 1, T4.SeriesName, T0.DOCNUM AS 'Invoice No.', T0.DocEntry 'Actual Invoice No.', (Case When T0.DocStatus='C' Then 'Closed' Else 'Open' End) 'Status', T0.jrnlMemo as 'Document Status', T0.DOCDATE, T0.DocDueDate, T0.CARDCODE AS 'Customer Code', T0.CARDNAME AS 'Customer Name', T1.ITEMCODE, T1.SubCatNum 'BP Catalogue No.', T1.FreeTxt AS 'Free Text', T1.DSCRIPTION AS 'Item Description', T1.QUANTITY AS 'Ordered Quantity', T1.PriceBefDi 'Price Before Discount', T1.DiscPrcnt, T1.Price 'Price After Discount', T0.DocCur, (T1.QUANTITY-T1.OPENQTY) AS 'Received Quantity', T1.OPENQTY AS 'Balanced Quantity', T0.DOCTOTAL-T0.VATSUM AS 'Amount Before Tax', T0.VATSUM AS 'Tax', T0.DOCTOTAL AS 'Net Amount After Tax', T3.SLPNAME AS 'Buyer Name' FROM OINV T0, INV1 T1, OCRD T2, OSLP T3, NNM1 T4 WHERE T0.DOCENTRY=T1.DOCENTRY AND T0.CARDCODE=T2.CARDCODE AND T0.SLPCODE=T3.SLPCODE AND T0.Series=T4.Series And T0.DOCDATE>='[%0]' AND T0.DOCDATE<='[%1]'

Regards

Malay Gandhi

[mgregur · Accepted solution]

Hi,

I would suggest you go over some of the free online SQL courses (w3schools for example). The query you provided is highly unoptimized and it's a wonder that it works on larger datasets.

Try this:

SELECT 1, T4.SeriesName, T0.DOCNUM AS 'Invoice No.', T0.DocEntry 'Actual Invoice No.', (Case When T0.DocStatus='C' Then 'Closed' Else 'Open' End) 'Status', T0.jrnlMemo as 'Document Status', T0.DOCDATE, T0.DocDueDate, T0.CARDCODE AS 'Customer Code', T0.CARDNAME AS 'Customer Name', T0.NUMATCARD AS 'Customer ref.no.', T1.ITEMCODE, T1.SubCatNum 'BP Catalogue No.', T1.FreeTxt AS 'Free Text', T1.DSCRIPTION AS 'Item Description', T1.QUANTITY AS 'Ordered Quantity', T1.PriceBefDi 'Price Before Discount', T1.DiscPrcnt, T1.Price 'Price After Discount', T0.DocCur, (T1.QUANTITY-T1.OPENQTY) AS 'Received Quantity', T1.OPENQTY AS 'Balanced Quantity', T0.DOCTOTAL-T0.VATSUM AS 'Amount Before Tax', T0.VATSUM AS 'Tax', T0.DOCTOTAL AS 'Net Amount After Tax', T3.SLPNAME AS 'Buyer Name' 
FROM OINV T0
INNER JOIN INV1 T1 ON T0.DOCENTRY=T1.DOCENTRY
INNER JOIN OCRD T2 ON T0.CARDCODE=T2.CARDCODE
LEFT JOIN OSLP T3 ON T0.SLPCODE=T3.SLPCODE
INNER JOIN NNM1 T4 ON T0.Series=T4.Series 
WHERE T0.DOCDATE>='[%0]' AND T0.DOCDATE<='[%1]'

BR,

Matija

[Former Member · Accepted solution]

Thanks sir...

It works..

Regards

Malay