Query returning no data in SAP Business One using Table Relationship

When queries return no data in SAP Business One, this is often caused by incorrect table relationships, particularly when using INNER JOIN statements. The issue typically occurs when one of the joined tables contains no matching records, causing the entire result set to be empty.

Solution: Replace INNER JOIN with LEFT JOIN

The most effective solution is to replace INNER JOIN with LEFT JOIN for optional relationships. This ensures that records from the main table are returned even when related tables have no matching data.

Example Query

Here's a corrected payment report query that handles missing table relationships properly −

SELECT T0.DocNum AS 'Payment Number',
       T0.DocDate AS 'Payment Date',
       T0.CardCode,
       T0.CardName AS 'Customer Name',
       T1.BankCode AS 'Bankcode',
       T3.BankName AS '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]
LEFT 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

Key Changes Made

The critical modifications include −

• LEFT JOIN usage: Most table relationships use LEFT JOIN to preserve main records even when related data is missing
• INNER JOIN retention: Only essential relationships like user information remain as INNER JOIN
• Proper table aliases: Consistent capitalization and naming for better readability

This approach ensures your query returns data from the primary payment table (ORCT) even when optional related tables contain no matching records, effectively resolving the "no data returned" issue.