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

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  March 11, 2018 at 7:44 pm

  #329548

  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

• pietlinden

  SSC Guru

  Points: 63516

  More actions

  March 11, 2018 at 9:49 pm

  #1982725

  Do the databases have exactly the same tables and records?

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  March 12, 2018 at 1:19 am

  #1982731

  yes

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  March 13, 2018 at 10:52 am

  #1982979

  pietlinden - Sunday, March 11, 2018 9:49 PM

  Do the databases have exactly the same tables and records?

  If that's actually true, then either there's row-level security on the server you get no records from (a permissions issue), or the query you run on both databases is in some way different between the two databases.   Alternatively, your initial premise is false.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• ZZartin

  SSC-Dedicated

  Points: 30921

  More actions

  March 13, 2018 at 11:00 am

  #1982982

  You've a ton of subqueries and joins in there, start with one table then add the joins back in one at a time.  That should give you an idea of where the queries start to run different between the two databases.

• MMartin1

  One Orange Chip

  Points: 27799

  More actions

  March 13, 2018 at 11:01 am

  #1982984

  pietlinden - Sunday, March 11, 2018 9:49 PM

  Do the databases have exactly the same tables and records?

  ahmed_elbarbary.2010 - Monday, March 12, 2018 1:19 AM

  yes

  I wonder, how have you verified this?

  ----------------------------------------------------

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  March 13, 2018 at 11:09 am

  #1982988

  This query has 16 separate references to the AllSubLedgerCode table, which is likely to be more of a problem for you in the future than being unable to figure out why it won't run in a second environment.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  March 13, 2018 at 11:12 am

  #1982993

  ahmed_elbarbary.2010 - Sunday, March 11, 2018 7:44 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 .

  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

  The above is really a pain to try and read.  I reformatted the code for others that may want to look at it:
  
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;