查询不返回任何记录，虽然它给我结果但在另一个数据库中 [英] Query not return any records although it give me result but in another database

问题描述

问题



如何跟踪此查询以了解返回空记录的原因。

$ b以下$ b查询在数据库中工作并给我结果但是没有在另一个数据库中给我任何结果



如何调试或跟踪它以了解为什么它不给我任何结果。

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 

我尝试了什么：



查询不返回任何记录，虽然它给我结果但在另一个数据库中

What I have tried:

Query not return any records although it give me result but in another database

推荐答案

对不起，但你的疑问是......丑陋。



我计算了以下表格的出现次数：

Sorry, but your query is... ugly.

I counted the following number of occurrences of tables :

TableName        NoOfFroms NoOfJoins Total
Accounts         2         0         2
AllSubLedgerCode 10        6         16 (!!!)   
TrxInvH          1         0         1
TrxInvF          0         1         1
trxtypeConfig    0         1         1
Units            0         1         1
Items            0         1         1
Stores           0         1         1

这对你意味着什么？由于 AllSubLedgerCode 表被调用了16次，因此该查询的性能远远不是最优的。



删除用于创建单个字段的所有子查询。使用加入 [ ^ ]！

这里是一篇关于联接的优秀文章： SQL联接的可视化表示 [ ^ ]



关于 如何调试或追踪它以了解它为什么不给我任何结果。 ......

按照文章列表进行操作：

使用故障排除和分析跟踪 [ ^ ]

如何：优化SQL查询（提示和技巧） [ ^ ]

查询性能 [ ^ ]

What that means to you? Since a AllSubLedgerCode table is 16. times called, a performance of this query is far, far away from optimal.

Remove all subqueries, which you use to create single field. Use JOIN's[^]!
Here is excelent article about joins: Visual Representation of SQL Joins[^]

As to the "How to debug or trace it to know why it not give me any result."...
Follow throught that list of articles:
Troubleshooting and Analysis with Traces[^]
How To: Optimize SQL Queries (Tips and Techniques)[^]
Query Performance[^]

这篇关于查询不返回任何记录，虽然它给我结果但在另一个数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！