I am trying to run the following SQL statement below
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
The error that I get is:
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 ')'.
It is probably due to the incorrect usage of Exists statement, can some help me out please ?
As noted you cannot use the
IF
statement inside a query. What you are looking for is a way of handling youAMOUNT
calculation whenever your sub-queries yield a NULL result. The following uses theISNULL
expression to handle this problem: