- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Query returning no data in SAP Business One using Table Relationship
This looks like an issue with Join in queries. Try replacing Inner join with Left join like this. I ran this query and it is working fine:
select T0.DocNum as 'Payment Number',T0.DocDate 'Payment Date',T0.CardCode, T0.CardName 'Customer Name',T1.BankCode 'Bankcode',T3.BankName 'Bank Name', T2.Phone1 , T0.CreditSum, T0.CashSum, T0.TrsfrSum, t0.CheckSum, t1.CheckNum as 'Check Number', t1.DueDate as 'check date', t6.VoucherNum as 'Voucher Number', t0.TrsfrRef as 'Transfer No', t0.TrsfrDate AS 'Transfer Date', ousr.USER_code as 'user code', T5.DocNum, t11.U_P_BuildingName as 'Building Name', CASE when T5.DocNum is null then 'On Account' else 'Paid For Invoice' END AS 'Payment Status', CASE when T5.DocStatus = 'O' then 'Open' else 'Closed' END AS ' Invoice Status', T4.SumApplied as 'Amount Paid on Invoice',T9.U_FloorNo,T5.U_UnitCode,T5.U_Type, t0.DocTotal as 'Payment Total',t5.DocTotal as'Invoice Total' , t8.City, t0.Comments as'Remarks' from ORCT T0 left join rct1 T1 on T0.DocNum=T1.DocNum left join ocrd T2 on T2.CardCode=T0.CardCode left outer join ODSC T3 on T3.BankCode=T0.BankCode left join RCT2 T4 on T0.DocNum = T4.DocNum left join RCT3 T6 on T0.DocNum = T6.DocNum left join OINV T5 on T4.DocEntry = T5.DocEntry and T5.ObjType = T4.InvType left join oitm t11 on t5.u_unitcode = t11.ItemCode LEFT JOIN OWHS T8 ON T11.U_P_BuildingNum = T8.WhsCode LEFT JOIN [dbo].[@AUND] T9 ON T5.[U_UnitCode] = T9.[Code] INNER JOIN OSLP T10 ON T5.[SlpCode] = T10.[SlpCode] inner join ousr on ousr.USERID = t0.usersign where T4.InvType <> '14' and T0.[Canceled] = 'N' and t0.docnum=200001
Advertisements