Query not return any records although it give me result but

Question

Problem How to trace this query to know the reason of why it return null records . query below work in database and give me results but not give me any result in another database How to debug or trace it to know why it not give me any result . SELECT TrxInvH.Trxtype, TrxInvH.TrxYear, TrxInvH.TrxSerial, TrxInvH.TrxDate, Items.ItemAraName, Items.ItemLatName, Units.UnitLatName, Stores.StoreAraName, Stores.StoreLatName, Units.UnitAraName, TrxInvF.displayQty, TrxInvF.Quantity, TrxInvF.Price, TrxInvF.Displayprice, trxtypeConfig.TrxArbName, trxtypeConfig.TrxEngName, TrxInvF.ItemCode, trxtypeConfig.BranchCode, Stores.StoreLatName AS Expr1, Stores.StoreAraName AS TPSTOREARA, CASE WHEN TrxInvF.Account = '' OR TrxInvF.Account IS NULL THEN TrxInvH.AccountID ELSE TrxInvF.Account END AS AccountCode, ( SELECT AccAraName FROM Accounts WHERE (AccCode = ( CASE WHEN TrxInvF.Account = '' OR TrxInvF.Account IS NULL THEN TrxInvH.AccountID ELSE TrxInvF.Account END ))) AS AccaraName, ( SELECT AccEngName FROM Accounts AS Accounts_3 WHERE (AccCode = ( CASE WHEN TrxInvF.Account = '' OR TrxInvF.Account IS NULL THEN TrxInvH.AccountID ELSE TrxInvF.Account END ))) AS AccLatName, TrxInvF.SubLdgCode1, ( SELECT SubLdgAraName FROM AllSubLedgerCode WHERE (TrxInvF.SubLdgCodeType1 = SubLdgTypeCode) AND (TrxInvF.SubLdgCode1 = SubLdgCode) AND (TrxInvF.BranchCode = BranchCode)) AS SubAraName1, ( SELECT SubLdgLatName FROM AllSubLedgerCode AS AllSubLedgerCode_26 WHERE (TrxInvF.SubLdgCodeType1 = SubLdgTypeCode) AND (TrxInvF.SubLdgCode1 = SubLdgCode) AND (TrxInvF.BranchCode = BranchCode)) AS SubLatName1, TrxInvF.SubLdgCode2, ( SELECT SubLdgAraName FROM AllSubLedgerCode AS AllSubLedgerCode_25 WHERE (TrxInvF.SubLdgCodeType2 = SubLdgTypeCode) AND (TrxInvF.SubLdgCode2 = SubLdgCode) AND (TrxInvF.BranchCode = BranchCode)) AS SubAraName2, ( SELECT SubLdgLatName FROM AllSubLedgerCode AS AllSubLedgerCode_24 WHERE (TrxInvF.SubLdgCodeType2 = SubLdgTypeCode) AND (TrxInvF.SubLdgCode2 = SubLdgCode) AND (TrxInvF.BranchCode = BranchCode)) AS SubLatName2, TrxInvF.SubLdgCode3, ( SELECT SubLdgAraName FROM AllSubLedgerCode AS AllSubLedgerCode_23 WHERE (TrxInvF.SubLdgCodeType3 = SubLdgTypeCode) AND (TrxInvF.SubLdgCode3 = SubLdgCode) AND (TrxInvF.BranchCode = BranchCode)) AS SubAraName3, ( SELECT SubLdgLatName FROM AllSubLedgerCode AS AllSubLedgerCode_22 WHERE (TrxInvF.SubLdgCodeType3 = SubLdgTypeCode) AND (TrxInvF.SubLdgCode3 = SubLdgCode) AND (TrxInvF.BranchCode = BranchCode)) AS SubLatName3, TrxInvF.SubLdgCode4, ( SELECT SubLdgAraName FROM AllSubLedgerCode AS AllSubLedgerCode_21 WHERE (TrxInvF.SubLdgCodeType4 = SubLdgTypeCode) AND (TrxInvF.SubLdgCode4 = SubLdgCode) AND (TrxInvF.BranchCode = BranchCode)) AS SubAraName4, ( SELECT SubLdgLatName FROM AllSubLedgerCode AS AllSubLedgerCode_20 WHERE (TrxInvF.SubLdgCodeType4 = SubLdgTypeCode) AND (TrxInvF.SubLdgCode4 = SubLdgCode) AND (TrxInvF.BranchCode = BranchCode)) AS SubLatName4, TrxInvF.SubLdgCode5, ( SELECT SubLdgAraName FROM AllSubLedgerCode AS AllSubLedgerCode_19 WHERE (TrxInvF.SubLdgCodeType5 = SubLdgTypeCode) AND (TrxInvF.SubLdgCode5 = SubLdgCode) AND (TrxInvF.BranchCode = BranchCode)) AS SubAraName5, ( SELECT SubLdgLatName FROM AllSubLedgerCode AS AllSubLedgerCode_18 WHERE (TrxInvF.SubLdgCodeType5 = SubLdgTypeCode) AND (TrxInvF.SubLdgCode5 = SubLdgCode) AND (TrxInvF.BranchCode = BranchCode)) AS SubLatName5, trxtypeConfig.CalcAvgPrice, trxtypeConfig.CalcFifoPrice, Items.AvgPrice1, Items.AvgPrice2, Items.TotalVal1, Items.TotalVal2, Items.TotalQty1, Items.TotalQty2, TrxInvF.CostAverage, TrxInvH.createuserid, TrxInvH.CreateDateAndTime, TrxInvH.UserId, TrxInvH.DateAndTime FROM TrxInvH INNER JOIN TrxInvF ON TrxInvH.BranchCode = TrxInvF.BranchCode AND TrxInvH.Trxtype = TrxInvF.Trxtype AND TrxInvH.TrxYear = TrxInvF.TrxYear AND TrxInvH.TrxSerial = TrxInvF.TrxSerial INNER JOIN trxtypeConfig ON TrxInvF.BranchCode = trxtypeConfig.BranchCode AND TrxInvF.Trxtype = trxtypeConfig.trxtypecode INNER JOIN Units ON TrxInvF.Unitcode = Units.UnitCode INNER JOIN Items ON TrxInvF.ItemCode = Items.ItemCode INNER JOIN Stores ON TrxInvF.StoreId = Stores.StoreCode AND TrxInvF.BranchCode = Stores.BranchCode LEFT OUTER JOIN AllSubLedgerCode AS allsubledgercode_4 ON TrxInvF.SubLdgCode4 = allsubledgercode_4.SubLdgCode AND TrxInvF.SubLdgCodeType4 = allsubledgercode_4.SubLdgTypeCode AND TrxInvF.SubLdgBranch4 = allsubledgercode_4.BranchCode LEFT OUTER JOIN AllSubLedgerCode AS allsubledgercode_3 ON TrxInvF.SubLdgCode3 = allsubledgercode_3.SubLdgCode AND TrxInvF.SubLdgCodeType3 = allsubledgercode_3.SubLdgTypeCode AND TrxInvF.SubLdgBranch3 = allsubledgercode_3.BranchCode LEFT OUTER JOIN AllSubLedgerCode AS allsubledgercode_2 ON TrxInvF.SubLdgBranch2 = allsubledgercode_2.BranchCode AND TrxInvF.SubLdgCodeType1 = allsubledgercode_2.SubLdgTypeCode AND TrxInvF.SubLdgCode2 = allsubledgercode_2.SubLdgCode LEFT OUTER JOIN AllSubLedgerCode AS AllSubLedgerCode_17 ON TrxInvF.SubLdgCode1 = AllSubLedgerCode_17.SubLdgCode AND TrxInvF.SubLdgCodeType1 = AllSubLedgerCode_17.SubLdgTypeCode AND TrxInvF.SubLdgBranch1 = AllSubLedgerCode_17.BranchCode LEFT OUTER JOIN AllSubLedgerCode AS allsubledgercode_1 ON TrxInvH.BranchCode = allsubledgercode_1.BranchCode AND TrxInvH.TargetType = allsubledgercode_1.SubLdgTypeCode AND TrxInvH.TargetCode = allsubledgercode_1.SubLdgCode LEFT OUTER JOIN AllSubLedgerCode AS allsubledgercode_5 ON TrxInvF.SubLdgCode5 = allsubledgercode_5.SubLdgCode AND TrxInvF.SubLdgCodeType5 = allsubledgercode_5.SubLdgTypeCode AND TrxInvF.SubLdgBranch5 = allsubledgercode_5.BranchCode WHERE 1 = 1 AND (dbo.TrxInvF.BranchCode in ( 1)) And ( TrxInvH.CreateDateAndTime Between '2018/01/01' And '2018/03/12' or TrxInvH.DateAndTime Between '2018/01/01' And '2018/03/12' or TrxInvH.TrxDate Between '2018/01/01' And '2018/03/12' ) AND (TrxInvH.CreateUserId= 'Admin' or TrxInvH.UserId = 'Admin' ) AND 1=1

Shahriar Nikkhah · Answer

this can be easy if you know how and i agree with Nitin. so what i generally do is replace line 1 to 54 with SELECT TrxInvH.* In SSMS copy the query and.... Steps ... --Run the query (F5), no results go to next line --Comment line 74 from the WHERE clause --Run the query (F5), no results go to next line Else line 74 is causing the issue --Comment line 72 LEFT POUTET JOIN --Run the query (F5), no results go to next line Else line 72 is causing the issue . . . Comment out each JOIN one by one and then press F5 in SSMS you will finally get some results and that point you can say that you have found the issue , keep that line commented and uncomment the ones that you havd commented before, may a combination of 2 JOIN might be causing the issue, anyways i assue you get the idea From a data/quey design perspective your query is tooooooo big and long you will have to break it, and i dont see an optimized query --I see over 11 joins and 6 of them are Left joins -- too many CASE clause in the SELECT statment -- I see 1 = 1 in the where clause twice, not sure why ,, i use 1=1 when i am doing some tests or POC, not in production and lots more Good luck

Nitin Sontakke · Answer

No expertise is needed to say that it is indeed a data issue, assuming it is working in another database as you mention it. There is no easy way to determine where it is going wrong, considering the way it is written. Not a very good way, I must add. If it is a Microsoft SQL Server, I never understood why some developers insist on returning everything in a single select statement. Anyway. I suggest you start with investigating INNER JOIN data because that's the one which limits the return. And also keep on commenting WHERE clause and you will probably know too.