PO to AP reserve invoice count

[irtazamalick]

i have this query please check i want total ap reserve invoice count in report and i have this query i am facing issue with joins please check

select OPOR.CardCode, OPOR.CardName, OPOR.U_ReferenceNo, OPOR.NumAtCard, OPOR.DocNum, count(OPCH.DocNum) over(partition by OPCH.DocNum) as "Ap reserve",

OPOR.U_ShipmentTime, OPOR.U_DealType,OPOR.DocDate, OPOR.U_Origin, OPOR.U_DealQuantity, OPOR.U_ContainerNo, POR1.ItemCode, POR1.Dscription, POR1.Price, por1.U_CopperRate,

POR1.U_AluminiumRate, POR1.U_IronRate, POR1.U_CopperPer, por1.U_AluminiumPer, POR1.U_LeadPer,POR1.U_IronPer, POR1.U_PlasticPer

from OPOR inner join POR1 on OPOR.DocEntry = POR1.DocEntry left join PCH1 on PCH1.BaseEntry = POR1.DocEntry LEFT JOIN

OPCH ON PCH1.BaseEntry = OPCH.DocEntry

[mgregur]

Hi,

try with the following:

select OPOR.CardCode, OPOR.CardName, OPOR.U_ReferenceNo, OPOR.NumAtCard, OPOR.DocNum, count(OPCH.DocNum) over(partition by OPCH.DocNum) as "Ap reserve",
OPOR.U_ShipmentTime, OPOR.U_DealType,OPOR.DocDate, OPOR.U_Origin, OPOR.U_DealQuantity, OPOR.U_ContainerNo, POR1.ItemCode, POR1.Dscription, POR1.Price, por1.U_CopperRate,
POR1.U_AluminiumRate, POR1.U_IronRate, POR1.U_CopperPer, por1.U_AluminiumPer, POR1.U_LeadPer,POR1.U_IronPer, POR1.U_PlasticPer
from OPOR
inner join POR1 on OPOR.DocEntry = POR1.DocEntry
left join PCH1 on PCH1.BaseEntry = POR1.DocEntry AND PCH1.BaseLine = POR1.LineNum AND PCH1.BaseType = POR1.ObjType
LEFT JOIN OPCH ON PCH1.DocEntry = OPCH.DocEntry

BR,

Matija