ahmed salah

ahmed salah

  • 1.6k
  • 530
  • 121.9k

Query not return any records although it give me result but

Mar 11 2018 8:34 PM
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 .
  1. SELECT        TrxInvH.Trxtype, TrxInvH.TrxYear, TrxInvH.TrxSerial, TrxInvH.TrxDate, Items.ItemAraName, Items.ItemLatName, Units.UnitLatName,   
  2.                                                     Stores.StoreAraName, Stores.StoreLatName, Units.UnitAraName, TrxInvF.displayQty, TrxInvF.Quantity, TrxInvF.Price, TrxInvF.Displayprice,   
  3.                                                     trxtypeConfig.TrxArbName, trxtypeConfig.TrxEngName, TrxInvF.ItemCode, trxtypeConfig.BranchCode, Stores.StoreLatName AS Expr1,   
  4.                                                     Stores.StoreAraName AS TPSTOREARA, CASE WHEN TrxInvF.Account = '' OR  
  5.                                                     TrxInvF.Account IS NULL THEN TrxInvH.AccountID ELSE TrxInvF.Account END AS AccountCode,  
  6.                                                         (SELECT        AccAraName  
  7.                                                           FROM            Accounts  
  8.                                                           WHERE        (AccCode = (CASE WHEN TrxInvF.Account = '' OR  
  9.                                                                                     TrxInvF.Account IS NULL THEN TrxInvH.AccountID ELSE TrxInvF.Account END))) AS AccaraName,  
  10.                                                         (SELECT        AccEngName  
  11.                                                           FROM            Accounts AS Accounts_3  
  12.                                                           WHERE        (AccCode = (CASE WHEN TrxInvF.Account = '' OR  
  13.                                                                                     TrxInvF.Account IS NULL THEN TrxInvH.AccountID ELSE TrxInvF.Account END))) AS AccLatName, TrxInvF.SubLdgCode1,  
  14.                                                         (SELECT        SubLdgAraName  
  15.                                                           FROM            AllSubLedgerCode  
  16.                                                           WHERE        (TrxInvF.SubLdgCodeType1 = SubLdgTypeCode) AND (TrxInvF.SubLdgCode1 = SubLdgCode) AND (TrxInvF.BranchCode = BranchCode))   
  17.                                                     AS SubAraName1,  
  18.                                                         (SELECT        SubLdgLatName  
  19.                                                           FROM            AllSubLedgerCode AS AllSubLedgerCode_26  
  20.                                                           WHERE        (TrxInvF.SubLdgCodeType1 = SubLdgTypeCode) AND (TrxInvF.SubLdgCode1 = SubLdgCode) AND (TrxInvF.BranchCode = BranchCode))   
  21.                                                     AS SubLatName1, TrxInvF.SubLdgCode2,  
  22.                                                         (SELECT        SubLdgAraName  
  23.                                                           FROM            AllSubLedgerCode AS AllSubLedgerCode_25  
  24.                                                           WHERE        (TrxInvF.SubLdgCodeType2 = SubLdgTypeCode) AND (TrxInvF.SubLdgCode2 = SubLdgCode) AND (TrxInvF.BranchCode = BranchCode))   
  25.                                                     AS SubAraName2,  
  26.                                                         (SELECT        SubLdgLatName  
  27.                                                           FROM            AllSubLedgerCode AS AllSubLedgerCode_24  
  28.                                                           WHERE        (TrxInvF.SubLdgCodeType2 = SubLdgTypeCode) AND (TrxInvF.SubLdgCode2 = SubLdgCode) AND (TrxInvF.BranchCode = BranchCode))   
  29.                                                     AS SubLatName2, TrxInvF.SubLdgCode3,  
  30.                                                         (SELECT        SubLdgAraName  
  31.                                                           FROM            AllSubLedgerCode AS AllSubLedgerCode_23  
  32.                                                           WHERE        (TrxInvF.SubLdgCodeType3 = SubLdgTypeCode) AND (TrxInvF.SubLdgCode3 = SubLdgCode) AND (TrxInvF.BranchCode = BranchCode))   
  33.                                                     AS SubAraName3,  
  34.                                                         (SELECT        SubLdgLatName  
  35.                                                           FROM            AllSubLedgerCode AS AllSubLedgerCode_22  
  36.                                                           WHERE        (TrxInvF.SubLdgCodeType3 = SubLdgTypeCode) AND (TrxInvF.SubLdgCode3 = SubLdgCode) AND (TrxInvF.BranchCode = BranchCode))   
  37.                                                     AS SubLatName3, TrxInvF.SubLdgCode4,  
  38.                                                         (SELECT        SubLdgAraName  
  39.                                                           FROM            AllSubLedgerCode AS AllSubLedgerCode_21  
  40.                                                           WHERE        (TrxInvF.SubLdgCodeType4 = SubLdgTypeCode) AND (TrxInvF.SubLdgCode4 = SubLdgCode) AND (TrxInvF.BranchCode = BranchCode))   
  41.                                                     AS SubAraName4,  
  42.                                                         (SELECT        SubLdgLatName  
  43.                                                           FROM            AllSubLedgerCode AS AllSubLedgerCode_20  
  44.                                                           WHERE        (TrxInvF.SubLdgCodeType4 = SubLdgTypeCode) AND (TrxInvF.SubLdgCode4 = SubLdgCode) AND (TrxInvF.BranchCode = BranchCode))   
  45.                                                     AS SubLatName4, TrxInvF.SubLdgCode5,  
  46.                                                         (SELECT        SubLdgAraName  
  47.                                                           FROM            AllSubLedgerCode AS AllSubLedgerCode_19  
  48.                                                           WHERE        (TrxInvF.SubLdgCodeType5 = SubLdgTypeCode) AND (TrxInvF.SubLdgCode5 = SubLdgCode) AND (TrxInvF.BranchCode = BranchCode))   
  49.                                                     AS SubAraName5,  
  50.                                                         (SELECT        SubLdgLatName  
  51.                                                           FROM            AllSubLedgerCode AS AllSubLedgerCode_18  
  52.                                                           WHERE        (TrxInvF.SubLdgCodeType5 = SubLdgTypeCode) AND (TrxInvF.SubLdgCode5 = SubLdgCode) AND (TrxInvF.BranchCode = BranchCode))   
  53.                                                     AS SubLatName5, trxtypeConfig.CalcAvgPrice, trxtypeConfig.CalcFifoPrice, Items.AvgPrice1, Items.AvgPrice2, Items.TotalVal1, Items.TotalVal2,   
  54.                                                     Items.TotalQty1, Items.TotalQty2, TrxInvF.CostAverage, TrxInvH.createuserid, TrxInvH.CreateDateAndTime, TrxInvH.UserId, TrxInvH.DateAndTime  
  55.                           FROM            TrxInvH INNER JOIN  
  56.                                                     TrxInvF ON TrxInvH.BranchCode = TrxInvF.BranchCode AND TrxInvH.Trxtype = TrxInvF.Trxtype AND TrxInvH.TrxYear = TrxInvF.TrxYear AND   
  57.                                                     TrxInvH.TrxSerial = TrxInvF.TrxSerial INNER JOIN  
  58.                                                     trxtypeConfig ON TrxInvF.BranchCode = trxtypeConfig.BranchCode AND TrxInvF.Trxtype = trxtypeConfig.trxtypecode INNER JOIN  
  59.                                                     Units ON TrxInvF.Unitcode = Units.UnitCode INNER JOIN  
  60.                                                     Items ON TrxInvF.ItemCode = Items.ItemCode INNER JOIN  
  61.                                                     Stores ON TrxInvF.StoreId = Stores.StoreCode AND TrxInvF.BranchCode = Stores.BranchCode LEFT OUTER JOIN  
  62.                                                     AllSubLedgerCode AS allsubledgercode_4 ON TrxInvF.SubLdgCode4 = allsubledgercode_4.SubLdgCode AND   
  63.                                                     TrxInvF.SubLdgCodeType4 = allsubledgercode_4.SubLdgTypeCode AND TrxInvF.SubLdgBranch4 = allsubledgercode_4.BranchCode LEFT OUTER JOIN  
  64.                                                     AllSubLedgerCode AS allsubledgercode_3 ON TrxInvF.SubLdgCode3 = allsubledgercode_3.SubLdgCode AND   
  65.                                                     TrxInvF.SubLdgCodeType3 = allsubledgercode_3.SubLdgTypeCode AND TrxInvF.SubLdgBranch3 = allsubledgercode_3.BranchCode LEFT OUTER JOIN  
  66.                                                     AllSubLedgerCode AS allsubledgercode_2 ON TrxInvF.SubLdgBranch2 = allsubledgercode_2.BranchCode AND   
  67.                                                     TrxInvF.SubLdgCodeType1 = allsubledgercode_2.SubLdgTypeCode AND TrxInvF.SubLdgCode2 = allsubledgercode_2.SubLdgCode LEFT OUTER JOIN  
  68.                                                     AllSubLedgerCode AS AllSubLedgerCode_17 ON TrxInvF.SubLdgCode1 = AllSubLedgerCode_17.SubLdgCode AND   
  69.                                                     TrxInvF.SubLdgCodeType1 = AllSubLedgerCode_17.SubLdgTypeCode AND   
  70.                                                     TrxInvF.SubLdgBranch1 = AllSubLedgerCode_17.BranchCode LEFT OUTER JOIN  
  71.                                                     AllSubLedgerCode AS allsubledgercode_1 ON TrxInvH.BranchCode = allsubledgercode_1.BranchCode AND   
  72.                                                     TrxInvH.TargetType = allsubledgercode_1.SubLdgTypeCode AND TrxInvH.TargetCode = allsubledgercode_1.SubLdgCode LEFT OUTER JOIN  
  73.                                                     AllSubLedgerCode AS allsubledgercode_5 ON TrxInvF.SubLdgCode5 = allsubledgercode_5.SubLdgCode AND   
  74.                                                     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   
 

Answers (2)