4 Replies Latest reply on Apr 7, 2018 5:50 PM by Hina Ahmad

    Custom SQL Query - Is Null resulting in error

    Hina Ahmad

      I have the following Custom SQL Query pulling data in from an Access DB.  It works fine when I run it as below.  However, I want to update the last Where clause to include a Null criteria otherwise it does not include records I want to include.

       

      WHERE (((T_01_FilingStrategy.FilingPlanned)=True) AND ((T_10_Filings.FilingType) Is Null Or (T_10_Filings.FilingType)="Initial"));

       

      Any thing I do to try to update it results in "An Error occurred while communicating with the data source".  Is using null not the appropriate syntax?  What am I doing wrong?

       

      SELECT

          'Actual' AS FlowStatus,
          2 AS ReportingLevel,
          T_10_Filings.FilingStatusCategory AS FilingStatusCategory,
          T_10_Filings.FilingStrategyID AS FilingStrategyID,
          T_10_Filings.RerateRound AS RerateRound,
          T_10_Filings.Product AS Product,
          T_10_Filings.State AS State,
          T_10_Filings.RateStabilization AS RateStabilization,
          T_10_Filings.ActualFilingDate AS DateStratActual,
          T_01_FilingStrategy.StrategyTargetFilingDate AS DateStrategy,
          T_10_Filings.ActualFilingDate AS DateActual,
          T_01_FilingStrategy.AnnualizedPremium AS AnnualizedPremium,
          T_01_FilingStrategy.ExpectedPremiumIncr AS ExpectedPremiumIncr,
          T_01_FilingStrategy.NoofPolicies AS NoofPolicies
      FROM T_10_Filings INNER JOIN T_01_FilingStrategy ON T_10_Filings.FilingStrategyID = T_01_FilingStrategy.FilingStrategyID
      WHERE (((T_10_Filings.FilingType)="Initial") AND ((T_01_FilingStrategy.FilingPlanned)=True))

      UNION

      SELECT
         
          'Planned' as FlowStatus,
         1 as ReportingLevel,
          [T_10_Filings].[FilingStatusCategory] as [FilingStatusCategory],
          [T_01_FilingStrategy].[FilingStrategyID] AS [FilingStrategyID],
          [T_01_FilingStrategy].[RerateRound] AS [RerateRound],
          [T_01_FilingStrategy].[Product] AS [Product],
           [T_01_FilingStrategy].[State] AS [State],
           [T_01_FilingStrategy].[RateStabilization] AS [RateStabilization],
          [T_01_FilingStrategy].[StrategyTargetFilingDate] AS [DateStratActual],
          [T_01_FilingStrategy].[StrategyTargetFilingDate] AS [DateStrategy],
            [T_10_Filings].[ActualFilingDate] as [DateActual],
          [T_01_FilingStrategy].[AnnualizedPremium] AS [AnnualizedPremium],
          [T_01_FilingStrategy].[ExpectedPremiumIncr] AS [ExpectedPremiumIncr],
          [T_01_FilingStrategy].[NoofPolicies] AS [NoofPolicies]

      FROM [T_01_FilingStrategy]
        LEFT JOIN [T_10_Filings]
        ON [T_10_Filings].[FilingStrategyID] = [T_01_FilingStrategy].[FilingStrategyID]
      WHERE (((T_01_FilingStrategy.FilingPlanned)=True)AND((T_10_Filings.FilingType)="Initial"))