CODEWITHSUNDEEP
sqlsql-servert-sqlif-statementexists
Emaad Shehzzad
Emaad Shehzzad

Reputation: 35

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 ?

Upvotes: 3

Views: 901

Answers (1)

JohnS
JohnS

Reputation: 2052

As noted you cannot use the IF statement inside a query. What you are looking for is a way of handling you AMOUNT calculation whenever your sub-queries yield a NULL result. The following uses the ISNULL expression to handle this problem:

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

Upvotes: 5

Related Questions