Error in Purchase Register Query

[Former Member]

Hello All..

I made a Query of Purchase Register and its working fine when my Invoice is of One item But when i am taking two or three items then its showing wrong result of all the taxes..

Please Sort it out as soon as possible

ELECT M.DocNum AS 'AP Inv. #', M.DocDate as 'Date', M.CardName as 'Vendor Name',M.NumAtCard as 'Bill No. & Dt.',L.ItemCode, L.Dscription,

(Select Sum(LineTotal) FROM PCH1 L Where L.DocEntry=M.DocEntry) as 'Base Amt.(Rs.)',

(SELECT Sum(TaxSum) FROM PCH4 where statype=-90 and DocEntry=M.DocEntry) as 'ED (Rs.)',

(SELECT Sum(TaxSum) FROM PCH4 where statype=7 and DocEntry=M.DocEntry) as 'ACD (Rs.)',

(SELECT Sum(TaxSum) FROM PCH4 where statype=-60 and DocEntry=M.DocEntry) as 'EDCS (Rs.)',

(SELECT Sum(TaxSum) FROM PCH4 where statype=-55 and DocEntry=M.DocEntry) as 'HECS (Rs.)',

(SELECT Sum(TaxSum) FROM PCH4 where statype=1 and DocEntry=M.DocEntry) as ' VAT (Rs.) ',

(SELECT Sum(TaxSum) FROM PCH4 where statype=4 and DocEntry=M.DocEntry) as ' CST (Rs.) ',

(SELECT Sum(TaxSum) FROM PCH4 where statype=10 and DocEntry=M.DocEntry) as ' CVD (Rs.) ',

(SELECT Sum(TaxSum) FROM PCH4 where statype=5 and DocEntry=M.DocEntry) as ' Ser.Tax (Rs.) ',

(SELECT Sum(TaxSum) FROM PCH4 where statype=6 and DocEntry=M.DocEntry) as 'CS on Ser.Tax (Rs.)',

(SELECT Sum(TaxSum) FROM PCH4 where statype=8 and DocEntry=M.DocEntry) as 'HECS_ST (Rs.)',

(Select Sum(LineTotal) From PCH3 Q Where Q.DocEntry=M.DocEntry) AS 'Freight (Rs.)',

M.WTSum AS 'TDS (Rs.)',

M.DocTotal as 'Total (Rs.)'

FROM OPCH M LEFT OUTER JOIN PCH1 L on L.DocEntry=M.DocEntry

LEFT OUTER JOIN PCH4 T on T.DocEntry=L.DocEntry and L.LineNum=T.LineNum

LEFT OUTER JOIN PCH5 J ON M.DocEntry = J.AbsEntry

LEFT OUTER JOIN PCH3 Q ON M.DocEntry = Q.DocEntry

WHERE (M.DocDate >= '[%0]' AND M.DocDate <= '[%1]')

GROUP BY

M.DocNum,M.DocDate,M.CardName,M.NumAtCard,M.DocEntry,M.DiscSum,M.WTSum,M.DocTotal,L.ItemCode,L.Dscription

ORDER BY

M.DocNum,M.DocDate,M.CardName,M.NumAtCard,M.DocEntry,M.DiscSum,M.WTSum,M.DocTotal

[Former Member]

Closed Myself

[Former Member]

Hi,

You may try to include and L.LineNum=LineNum to all your SELECT tax columns to see.

Thanks,

Gordon

[Former Member]

Hi Gordon..

Can you explain with any example..

Thanks

[Former Member]

Add to the lines like this:

(SELECT Sum(TaxSum) FROM PCH4 where statype=-90 and DocEntry=M.DocEntry) as 'ED (Rs.)',

[Former Member]

HII..

I did but still showing the same error..

[Former Member]

Omit L from (Select Sum(LineTotal) FROM PCH1 L Where L.DocEntry=M.DocEntry) as 'Base Amt.(Rs.)'