gpt4 book ai didi

sql - SQL 中 IF 的正确用法

转载 作者:行者123 更新时间:2023-12-04 20:53:10 25 4
gpt4 key购买 nike

我正在尝试在下面运行以下 SQL 语句

    select  FB.FundingSourceID,
FB.FiscalYearID,
(FB.FundingBudget-CA.CurrentAmount-DE.CFOApproved) as Amount from
(select
FundingSourceID,
FiscalYearID,
SUM(COALESCE(Amount,0)) as FundingBudget
from [cap].[FactFundingSourceBudget]
WHERE IsDeleted = 0
group by FundingSourceID, FiscalYearID) AS FB

LEFT JOIN (
select
FundingSourceID,
FiscalYearID,
SUM(COALESCE([Current],0)) as CurrentAmount
from [cap].[FactSampledFunding] F
WHERE F.IsDeleted = 0
group by FundingSourceID, FiscalYearID) AS CA on CA.FundingSourceID = FB.FundingSourceID and CA.FiscalYearID = FB.FiscalYearID

LEFT JOIN (
IF EXISTS (SELECT FundingSourceID, FiscalYearID, SUM(COALESCE(PF.Amount,0))AS CFOApproved FROM [cap].[FactProjectFunding] PF
INNER JOIN [cap].[DimProject] DP ON DP.ProjectID = PF.ProjectID
INNER JOIN [dbo].[WFToken] WT ON DP.MemberGUID = WT.EntityGUID
INNER JOIN [dbo].[WFWorkflowStep] WFT ON WFT.WorkflowStepGUID = WT.WorkflowStepGUID
WHERE PF.IsDeleted = 0 AND WFT.Name IN ('Cancer Center Review', 'Outpatient Center Review', 'Corporate EVP CFO Review', 'Corporate EVP Review', 'Capital Committee Review')
GROUP BY FundingSourceID, FiscalYearID)
SELECT FundingSourceID, FiscalYearID, SUM(COALESCE(PF.Amount,0))AS CFOApproved FROM [cap].[FactProjectFunding] PF
INNER JOIN [cap].[DimProject] DP ON DP.ProjectID = PF.ProjectID
INNER JOIN [dbo].[WFToken] WT ON DP.MemberGUID = WT.EntityGUID
INNER JOIN [dbo].[WFWorkflowStep] WFT ON WFT.WorkflowStepGUID = WT.WorkflowStepGUID
WHERE PF.IsDeleted = 0 AND WFT.Name IN ('Cancer Center Review', 'Outpatient Center Review', 'Corporate EVP CFO Review', 'Corporate EVP Review', 'Capital Committee Review')
GROUP BY FundingSourceID, FiscalYearID
ELSE
SELECT 0 AS [FundingSourceID], 0 as [FiscalYearID], 0 as [CFOApproved]
) AS DE on DE.FundingSourceID = FB.FundingSourceID and DE.FiscalYearID = FB.FiscalYearID
WHERE
FB.FundingSourceID != 1

我得到的错误是:

Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'IF'.
Msg 102, Level 15, State 1, Line 34
Incorrect syntax near ')'.

这可能是由于 Exists 语句的使用不正确,请问有人能帮帮我吗?

最佳答案

如前所述,您不能在查询中使用 IF 语句。您正在寻找的是一种在您的子查询产生 NULL 结果时处理您的 AMOUNT 计算的方法。下面使用ISNULL表达式来处理这个问题:

SELECT
FB.FundingSourceID,
FB.FiscalYearID,
( FB.FundingBudget - ISNULL(CA.CurrentAmount, 0) - ISNULL(DE.CFOApproved, 0) ) AS Amount
FROM
(
SELECT
FundingSourceID,
FiscalYearID,
SUM(COALESCE(Amount, 0)) AS FundingBudget
FROM
[cap].[FactFundingSourceBudget]
WHERE
IsDeleted = 0
GROUP BY
FundingSourceID,
FiscalYearID
) AS FB
LEFT JOIN (
SELECT
FundingSourceID,
FiscalYearID,
SUM(COALESCE([Current], 0)) AS CurrentAmount
FROM
[cap].[FactSampledFunding] F
WHERE
F.IsDeleted = 0
GROUP BY
FundingSourceID,
FiscalYearID
) AS CA
ON CA.FundingSourceID = FB.FundingSourceID
AND CA.FiscalYearID = FB.FiscalYearID
LEFT JOIN (
SELECT
FundingSourceID,
FiscalYearID,
SUM(COALESCE(PF.Amount, 0)) AS CFOApproved
FROM
[cap].[FactProjectFunding] PF
INNER JOIN [cap].[DimProject] DP
ON DP.ProjectID = PF.ProjectID
INNER JOIN [dbo].[WFToken] WT
ON DP.MemberGUID = WT.EntityGUID
INNER JOIN [dbo].[WFWorkflowStep] WFT
ON WFT.WorkflowStepGUID = WT.WorkflowStepGUID
WHERE
PF.IsDeleted = 0
AND WFT.Name IN ( 'Cancer Center Review',
'Outpatient Center Review',
'Corporate EVP CFO Review',
'Corporate EVP Review',
'Capital Committee Review' )
GROUP BY
FundingSourceID,
FiscalYearID
) AS DE
ON DE.FundingSourceID = FB.FundingSourceID
AND DE.FiscalYearID = FB.FiscalYearID
WHERE
FB.FundingSourceID != 1

关于sql - SQL 中 IF 的正确用法,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/30719346/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com