AP INVOICE

[Former Member]

hai

SELECT T0.DocNum,T0.TaxDate,T1.AcctCode, T1.AcctName, T1.Descrip,T1.SumApplied FROM dbo.OVPM T0 INNER JOIN VPM4 T1 ON T0.DocEntry = T1.DocNum WHERE T0.DocType ='a' and T0.TaxDate >='%0' AND T0.TaxDate <='%1' and T1.AcctCode='OTH10002'

SELECT T0.DocNum,t0.taxdate,t1.acctcode,T1.Dscription,T1.Price, T0.DocTotal FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.TaxDate >='%0' AND T0.TaxDate <='%1' and T1.AcctCode='oth10002'

Above both query i want mingle how do i do ???? i changed my query inner join and left join but some column data is empty.

[Former Member]

Hi,

Try:

SELECT T0.DocNum,T0.TaxDate,T1.AcctCode, T1.AcctName, T1.Descrip,T1.GrossAmnt,T1.SumApplied

FROM dbo.OVPM T0
INNER JOIN VPM4 T1 ON T0.DocNum = T1.DocNum
WHERE T0.DocType ='a' and T0.TaxDate >='[%0]' AND T0.TaxDate <='[%1]' and T1.AcctCode='[%2]'

UNION ALL

SELECT T0.DocNum,t0.taxdate,t1.acctcode,T2.Acctname,T1.Dscription,T1.Price, T0.DocTotal
FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OACT T2 ON T2.AcctCode=T1.Account
WHERE T0.TaxDate >='[%0]' AND T0.TaxDate <='[%1]' and T2.AcctCode='[%2]'

Thanks,

Gordon

[jitin_chawla]

Hi,

Can you let us know which columns are coming as NULL.

Check this further :

Select distinct T0.DocNum,T0.TaxDate,T1.AcctCode, T1.AcctName, T1.Descrip,T1.SumApplied,
T2.DocNum,t2.taxdate,t3.acctcode,T3.Dscription,T3.Price, T2.DocTotal 
from OVPM t0 inner join OPCH t2 on t2.ReceiptNum = t0.DocNum
inner join VPM4 t1 on t0.DocEntry = t1.DocNum
Left join PCH1 t3 on t3.DocEntry = t2.DocEntry
where T0.DocType ='a' and T0.TaxDate >='[%0]' AND T0.TaxDate <='[%1]' 
and T1.AcctCode='OTH10002'
and T2.TaxDate >='[%2]' AND T2.TaxDate <='[%3]' and T3.AcctCode='oth10002' 

Kind Regards,

Jitin

SAP Business One Forum Team

[former_member196081]

Hi ,

try below

SELECT T0.DocNum,T0.TaxDate,T1.AcctCode, T1.AcctName, T1.Descrip,T1.SumApplied FROM dbo.OVPM T0 INNER JOIN VPM4 T1 ON T0.DocEntry = T1.DocNum WHERE T0.DocType ='a' and T0.TaxDate >=[%0] AND T0.TaxDate <=[%1] and T1.AcctCode=[%2]
union all
SELECT T0.DocNum,t0.taxdate,t1.acctcode,T1.Dscription,T1.Price, T0.DocTotal FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.TaxDate >=[%0] AND T0.TaxDate <=[%1] and T1.AcctCode=[%2]

[Former Member]

hai

while run your query some error came 1). "[Microsoft][SQL Server Native Client 10.0][SQL Server]Error converting data type nvarchar to numeric. 'Received Alerts' (OAIB)"