lundi 8 juin 2015

Correct Usage of IF Exists in SQL

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 ?

Aucun commentaire:

Enregistrer un commentaire