完全外部联接无法按预期工作的问题 [英] Problem with Full Outer Join not working as expected

问题描述

我有一个查询要从一个表中减去当前余额，并从另一个历史表中减去以前的余额.当特定产品没有当前余额但具有以前的余额时，我可以正确减去它...在这种情况下，它将是0-100.

I have a query to subtract current balance from one table with previous balance from another history table. When a particular product has no current balance but has previous balance, I am able to subtract it correctly...in this case it will be like 0 - 100.

但是，当产品具有当前余额但没有以前的余额时，我将无法获得结果.即使我在两个表上都进行了完全外部联接，我的查询甚至没有选择当前余额.

However, when the product has current balance but no previous balance, I am unable to get the result. My query does not even select the current balance even though I have done a full outer join on both tables.

以下是我的查询:

SELECT  DATEPART(yyyy, @ExecuteDate) * 10000 + DATEPART(mm, @ExecuteDate) * 100 + DATEPART(dd, @ExecuteDate) AS Period_Key,
                CASE WHEN GL.GL_Acct_Key IS NULL THEN 0 ELSE GL.GL_Acct_Key END AS GL_Acct_Key,
                CASE WHEN BANK.Bank_Type_Key IS NULL THEN 0 ELSE BANK.Bank_Type_Key END AS Bank_Type_Key,
                CASE WHEN TSC.TSC_Key IS NULL THEN 0 ELSE TSC.TSC_Key END AS TSC_Key,
                ISNULL(FT.CurrentBalance,0) - ISNULL(HIST.CurrentBalance,0) AS Actual_Income_Daily,
                CASE WHEN BR.Branch_Key IS NULL THEN 0 ELSE BR.Branch_Key END AS Branch_Key
        FROM    WSB_Stage.dbo.Stage_TS_Daily_Income_Hist HIST
                FULL OUTER JOIN WSB_Stage.dbo.Stage_TS_Daily_Income FT
                    ON FT.GLAcctID = HIST.GLAcctID AND
                       FT.BankType = HIST.BankType AND
                       FT.BranchNumber = HIST.BranchNumber
                LEFT OUTER JOIN WSB_Mart.dbo.Dim_Branch BR
                    ON HIST.BranchNumber = BR.Branch_Code
                LEFT OUTER JOIN WSB_Mart.dbo.Dim_GL_Acct GL
                    ON HIST.GLAcctID = GL.Acct_Code
                LEFT OUTER JOIN WSB_Mart.dbo.Dim_Bank_Type BANK
                    ON HIST.BankType = BANK.Bank_Type_Code
                LEFT OUTER JOIN WSB_Stage.dbo.Param_Branch_TSC_Map BRTSC
                    ON HIST.BranchNumber = BRTSC.BranchNumber
                LEFT OUTER JOIN WSB_Mart.dbo.Dim_TSC TSC
                    ON BRTSC.RegionCode = TSC.TSC_Code
        WHERE   HIST.TransactionDate = @PreviousDate
            AND GL.Acct_Type_Code = 'Interest'
            AND BANK.Bank_Type_Key = 1

推荐答案

感谢您的帮助，但无法使用下面的答案按我想要的方式工作.最后，我决定走很长一段路，并声明两个临时表来保存当前和以前的余额.我想我想尽可能远离外部联接； p

Thanks for the help but I couldn't get it to work the way I wanted using the below answers. Finally, I decided to go the long way and declare two temporary tables to hold current and previous balances. I think I want to stay as far away from outer joins as possible ;p

代码如下:

INSERT INTO @PreviousGL
    SELECT  GLAcctID,
            BankType,
            BranchNumber,
            ISNULL(CurrentBalance,0) AS Current_Balance
    FROM    WSB_Stage.dbo.Stage_TS_Daily_Income_Hist
    WHERE   TransactionDate = @PreviousDate

INSERT INTO @CurrentGL
SELECT  GLAcctID,
        BankType,
        BranchNumber,
        ISNULL(CurrentBalance,0) AS Current_Balance
FROM    WSB_Stage.dbo.Stage_TS_Daily_Income

INSERT INTO @DailyIncomeGL
SELECT  CASE WHEN CURR.GLAcctID IS NULL THEN PREV.GLAcctID
             WHEN PREV.GLAcctID IS NULL THEN CURR.GLAcctID
             WHEN CURR.GLAcctID IS NULL AND PREV.GLAcctID IS NULL THEN 0
             ELSE CURR.GLAcctID
        END AS GLAcctID,
        CASE WHEN CURR.BankType IS NULL THEN PREV.BankType
             WHEN PREV.BankType IS NULL THEN CURR.BankType
             WHEN CURR.BankType IS NULL AND PREV.BankType IS NULL THEN ''
             ELSE CURR.BankType
        END AS BankType,
        CASE WHEN CURR.BranchNumber IS NULL THEN PREV.BranchNumber
             WHEN PREV.BranchNumber IS NULL THEN CURR.BranchNumber
             WHEN CURR.BranchNumber IS NULL AND PREV.BranchNumber IS NULL THEN 0
             ELSE CURR.BranchNumber
        END AS BranchNumber,
        ISNULL(CURR.CurrentBal,0) - ISNULL(PREV.CurrentBal,0) AS Actual_Income_Daily
FROM    @CurrentGL CURR
        FULL OUTER JOIN @PreviousGL PREV
            ON CURR.GLAcctID = PREV.GLAcctID AND
               CURR.BankType = PREV.BankType AND
               CURR.BranchNumber = PREV.BranchNumber

这篇关于完全外部联接无法按预期工作的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！