none
Stored procedure works in management studio but not work in SSRS

    Question

  • I have part of stored procedure like following and it works but when i put in SSRS it gets error: Subquery returned more than 1 value

    My first question is why it works when i compile in management studio but not work in SSRS.

    I changed following first block to second block. My second question is why the second block gets much less records than the first block. The first block get the correct records.

    SELECT A, B,
      
     (select min(dc.StartDate)
      from [dbo].[vDimClaimStatus] dc join dbo.vDimClaimType dct
        on dc.ClaimFK=dct.ClaimFK
      where dc.[ClaimStatus] IN ('Open', 'Reopened')
       and dc.ClaimFK=cs.ClaimFK
      )   OpenedDate,

     (select dc.StartDate
      from [dbo].[vDimClaimStatus] dc
      where dc.[ClaimStatus]='Reopened'
       and dc.ClaimFK=cs.ClaimFK
      )   ClaimReopenDate

    From  ....

    -------------------------------------------------------------------------------------------
     ;with cteOpenedDate as
        (select dc.ClaimFK, min(dc.StartDate) OpenedDate
      from [dbo].[vDimClaimStatus] dc join dbo.vDimClaimType dct
        on dc.ClaimFK=dct.ClaimFK
      where dc.[ClaimStatus] IN ('Open', 'Reopened')
      group by dc.ClaimFK
       
      ),

        cteClaimReopenDate as
         (select ClaimFK, StartDate as ClaimReopenDate
      from [dbo].[vDimClaimStatus] dc
      where dc.[ClaimStatus]='Reopened'
      )  

     SELECT A, B,
      OpenedDate,
      ClaimReopenDate

     FROM ....
      INNER JOIN cteOpenedDate od on od.ClaimFK = cs.ClaimFK
      INNER JOIN cteClaimReopenDate rd on rd.ClaimFK=cs.ClaimFK

    Thank you in advance.

    Monday, March 20, 2017 4:51 PM

Answers

  • Thank you Jingyang. The report data source is correct for sure. 

    What is correct? I mean the query should run the same whether it is in SSMS or embedded in SSRS report if it is pointing to the same database tables.

    Anyway, you need to fix your subquery to get a single value. ( I don't know whether it is correct in yourcontext but it will not error out)

    , (select MIN(dc.StartDate)
      from [dbo].[vDimClaimStatus] dc
      where dc.[ClaimStatus]='Reopened' 
       and dc.ClaimFK=cs.ClaimFK
      )   ClaimReopenDate

    • Marked as answer by MDXLaotu Tuesday, March 21, 2017 3:35 PM
    Monday, March 20, 2017 7:28 PM
    Moderator

All replies

  • The first question:Check your report data source. It may not run against the table you tested from SSMS. You code needs to handle the case to make sure it returns only one row from your subquery for column: ClaimReopenDate.

    The second question: you have aggregated row in your first CTE, which may reduce the total number row for join.

    Monday, March 20, 2017 6:02 PM
    Moderator
  • Thank you Jingyang. The report data source is correct for sure. 
    Monday, March 20, 2017 6:53 PM
  • Can you post your exact query, please?

    I think in your case you may want

    select min(case when ClaimStatus = 'Opened' then StartDate end) as OpenedDate,

    min(case when ClaimStatus = 'ReOpen' then StartDate end) as ClaimReopenedDate 

    from ...

    where ClaimStatus in ('Open','ReOpen')


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, March 20, 2017 7:08 PM
    Moderator
  • Thank you Jingyang. The report data source is correct for sure. 

    What is correct? I mean the query should run the same whether it is in SSMS or embedded in SSRS report if it is pointing to the same database tables.

    Anyway, you need to fix your subquery to get a single value. ( I don't know whether it is correct in yourcontext but it will not error out)

    , (select MIN(dc.StartDate)
      from [dbo].[vDimClaimStatus] dc
      where dc.[ClaimStatus]='Reopened' 
       and dc.ClaimFK=cs.ClaimFK
      )   ClaimReopenDate

    • Marked as answer by MDXLaotu Tuesday, March 21, 2017 3:35 PM
    Monday, March 20, 2017 7:28 PM
    Moderator
  • Thank you Naomi and Jingyang again. 

    Here is the stored procedure. I delect some columns that calculate financial infomation.

    create PROC [dbo].[usp_OpenClaims]
     (@AsOfYearMonth int)

    AS
    BEGIN
      SET NOCOUNT ON;

      DECLARE @Year int,
              @Month int;

      SELECT
        @Year = [Year],
        @Month = [Month]
      FROM dbo.vDimDate
      WHERE MonthSort = @AsOfYearMonth;

      DECLARE @startdate datetime = (select distinct DATEADD(month, DATEDIFF(month, 0, sd.Date), 0) 
      FROM dbo.vFactClaimActivity f  
      INNER JOIN dbo.vDimDate sd on sd.DatePK = f.ClaimSnapshotDateFK 
      WHERE sd.MonthSort = @AsOfYearMonth)

      DECLARE @enddate datetime = (select distinct DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,sd.Date)+1,0))
      FROM dbo.vFactClaimActivity f  
      INNER JOIN dbo.vDimDate sd on sd.DatePK = f.ClaimSnapshotDateFK 
      WHERE sd.MonthSort = @AsOfYearMonth);


      SELECT
        Casefile,
        PolicyNumber,
        InsuredName,
        InsuredNameFrmt,
        InsuredClaimAddress1,
        InsuredClaimAddress2,
        InsuredCity,
        InsuredState,
        InsuredZip,
        InsuredDOB,
        ClaimantName,
        ClaimantClaimAddress1,
        ClaimantClaimAddress2,
        ClaimantCity,
        ClaimantState,
        ClaimantZip,
        ClaimantDOB,
        ClaimantGender,

        CurrentClaimStatus,
        ReportDate,
        LossDate,
        OpenedDate,
        ConsultantName,
        Region,
        CurrentClaimType,
        LossDescription,
        Policyholder,
        PolicyTermFromDate,
        PolicyTermToDate,
        ClaimLOB,
        PrimCovDescr,
    ClaimTypeChangeDate,
    ClaimReopenDate


       FROM (
      
       SELECT DISTINCT
        c.Casefile,
        dp.PolicyNumber,
        di.Insured InsuredName,
        CASE
          WHEN di.FirstName = 'Not Available' AND
            di.MiddleName = 'Not Available' THEN LTRIM(RTRIM(di.Insured))
          ELSE LTRIM(RTRIM(di.FIRSTNAME)) + ' ' + LTRIM(RTRIM(di.MiddleName)) + ' ' + LTRIM(RTRIM(di.LASTNAME))
        END InsuredNameFrmt,
        di.AddressLine1 InsuredClaimAddress1,
        di.AddressLine2 InsuredClaimAddress2,
        di.City InsuredCity,
        di.State InsuredState,
        di.Zipcode InsuredZip,
        di.DateOfBirth InsuredDOB,
        p.Claimant ClaimantName,
        p.AddressLine1 ClaimantClaimAddress1,
        p.AddressLine2 ClaimantClaimAddress2,
        p.City ClaimantCity,
        p.State ClaimantState,
        p.Zipcode ClaimantZip,
        p.DateOfBirth ClaimantDOB,
        p.Gender ClaimantGender,

        c.CurrentClaimStatus,
        c.ReportDate,
        c.LossDate,
    (select min(dc.StartDate)
    from [dbo].[vDimClaimStatus] dc join dbo.vDimClaimType dct
      on dc.ClaimFK=dct.ClaimFK
    where dc.[ClaimStatus] IN ('Open', 'Reopened') 
     and dc.ClaimFK=cs.ClaimFK
     and dct.ClaimType<>'abc'
    )   OpenedDate,
        Da.ClaimConsultant ConsultantName,
        Da.Region,
        c.CurrentClaimType,
        c.LossDescription,
        cc.CurrentTrialDate,
        dp.Policyholder,
        cc.CaseName,
        cc.VenueCity,
        cc.VenueState,
        cc.VenueCounty,
        CASE
    WHEN lob.TailOcc = 'TAIL' THEN cov.EffectiveFromDate
          ELSE ISNULL(ptfd.Date, '1/1/1900')
        END AS PolicyTermFromDate,
        CASE
          WHEN TailOcc = 'TAIL' THEN '1/1/3000'
          ELSE ISNULL(pttd.Date, '1/1/1900')
        END AS PolicyTermToDate,



        lob.ClaimLOB,
        lob.CoverageShortDescription PrimCovDescr,

    ct.StartDate  ClaimTypeChangeDate,

    (select dc.StartDate 
    from [dbo].[vDimClaimStatus] dc
    where dc.[ClaimStatus]='Reopened' 
     and dc.ClaimFK=cs.ClaimFK
    )   ClaimReopenDate

      FROM dbo.vFactClaimActivity f
      INNER JOIN dbo.vDimCompany cmp
        ON f.Companyfk = cmp.CompanyPK
      INNER JOIN dbo.vDimClaimType ct
        ON ct.ClaimTypePK = f.ClaimTypeFK
      INNER JOIN dbo.vDimClaim c
        ON c.ClaimPK = f.ClaimFK
      INNER JOIN dbo.vDimClaimStatus cs
        ON f.ClaimStatusFK = cs.ClaimStatusPK
      INNER JOIN dbo.vDimDate sd
        ON sd.DatePK = f.ClaimSnapshotDateFK
      INNER JOIN dbo.vDimClaimConsultant da
        ON da.ClaimFK = c.ClaimPK
        AND da.ClaimConsultant <> 'Not Available'
        AND (da.EffectiveToDate = '1/1/2999'
        OR da.EffectiveToDate = '1/1/3000')
        AND da.IsCurrent = 1
      LEFT JOIN dbo.vDimCourtcase cc
        ON cc.ClaimFK = c.ClaimPK
        AND cc.IsCurrent = 1
      INNER JOIN dbo.vDimInsured di
        ON di.InsuredPK = f.InsuredFK
        AND di.InsuredIsCurrent = 1

      INNER JOIN dbo.vDimPolicy dp
        ON f.PolicyFK = dp.PolicyPK
      INNER JOIN dbo.vDimCoverage cov 
    ON cov.CoveragePK=f.CoverageFK 
      INNER JOIN dbo.vDimClaimant p
        ON f.ClaimantFK = p.ClaimantPK

      INNER JOIN dbo.vDimDate ptfd
        ON ptfd.DatePK = f.PolicyTermFromDateFK
      INNER JOIN dbo.vDimDate pttd
        ON pttd.DatePK = f.PolicyTermToDateFK
      INNER JOIN dbo.vDimLOB lob
        ON f.LOBFK = lob.LOBPK
        AND lob.ClaimLOB <> 'General Liability'

     

      WHERE sd.MonthSort = @AsOfYearMonth
      AND cs.StartDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, sd.Date), 0) --@iFromDate
      AND cs.ClaimStatus IN ('Open', 'Reopened')
      AND ct.ClaimType IN ('a', 'c', 'b')
     ) x

      GROUP BY Casefile,
               PolicyNumber,
               InsuredName,
               InsuredNameFrmt,
               InsuredClaimAddress1,
               InsuredClaimAddress2,
               InsuredCity,
               InsuredState,
               InsuredZip,
               InsuredDOB,
               ClaimantName,
               ClaimantClaimAddress1,
               ClaimantClaimAddress2,
               ClaimantCity,
               ClaimantState,
               ClaimantZip,
               ClaimantDOB,
               ClaimantGender,

               CurrentClaimStatus,
               ReportDate,
               LossDate,
               OpenedDate,
               ConsultantName,
               Region,
               CurrentClaimType,
               LossDescription,
               Policyholder,
               PolicyTermFromDate,
               PolicyTermToDate,
               ClaimLOB,
               PrimCovDescr,
    ClaimTypeChangeDate,
    ClaimReopenDate

    union 
    (
    SELECT  
    Casefile,
    PolicyNumber,
    InsuredName,
    InsuredNameFrmt,
    InsuredClaimAddress1, 
    InsuredClaimAddress2, 
    InsuredCity, 
    InsuredState, 
    InsuredZip, 
    InsuredDOB,
    ClaimantName, 
    ClaimantClaimAddress1, 
    ClaimantClaimAddress2, 
    ClaimantCity, 
    ClaimantState, 
    ClaimantZip, 
    ClaimantDOB, 
    ClaimantGender,
    CurrentClaimStatus,
    Max(ReportDate) ReportDate,
    LossDate, 
    OpenedDate,
    ConsultantName,
    Region,
    CurrentClaimType,
    LossDescription,
    Policyholder,
    max(PolicyTermFromDate) PolicyTermFromDate,
    PolicyTermToDate,
    ClaimLOB, 
    PrimCovDescr,
    max(ClaimTypeChangeDate) ClaimTypeChangeDate,
     ClaimReopenDate


    from (

     SELECT distinct 
        c.Casefile,
    --ct.ClaimType,
        dp.PolicyNumber,
        di.Insured InsuredName,
        CASE
          WHEN di.FirstName = 'Not Available' AND
            di.MiddleName = 'Not Available' THEN LTRIM(RTRIM(di.Insured))
          ELSE LTRIM(RTRIM(di.FIRSTNAME)) + ' ' + LTRIM(RTRIM(di.MiddleName)) + ' ' + LTRIM(RTRIM(di.LASTNAME))
        END InsuredNameFrmt,
        di.AddressLine1 InsuredClaimAddress1,
        di.AddressLine2 InsuredClaimAddress2,
        di.City InsuredCity,
        di.State InsuredState,
        di.Zipcode InsuredZip,
        di.DateOfBirth InsuredDOB,
        p.Claimant ClaimantName,
        p.AddressLine1 ClaimantClaimAddress1,
        p.AddressLine2 ClaimantClaimAddress2,
        p.City ClaimantCity,
        p.State ClaimantState,
        p.Zipcode ClaimantZip,
        p.DateOfBirth ClaimantDOB,
        p.Gender ClaimantGender,
        c.CurrentClaimStatus,
        c.ReportDate,
        c.LossDate,
    (select min(dc.StartDate)
    from [dbo].[vDimClaimStatus] dc join dbo.vDimClaimType dct
      on dc.ClaimFK=dct.ClaimFK
    where dc.[ClaimStatus] IN ('Open', 'Reopened') 
     and dc.ClaimFK=cs.ClaimFK
     and dct.ClaimType<>'abc'
    )   OpenedDate,

        Da.ClaimConsultant ConsultantName,
        Da.Region,
        c.CurrentClaimType,
        c.LossDescription,
        cc.CurrentTrialDate,
        dp.Policyholder,
        cc.CaseName,
        cc.VenueCity,
        cc.VenueState,
        cc.VenueCounty,
        CASE
    --      WHEN lob.TailOcc = 'TAIL' THEN sd.Date
    WHEN lob.TailOcc = 'TAIL' THEN cov.EffectiveFromDate
          ELSE ISNULL(ptfd.Date, '1/1/1900')
    END AS PolicyTermFromDate,
        CASE
          WHEN lob.TailOcc = 'TAIL' THEN '1/1/3000'
          ELSE ISNULL(pttd.Date, '1/1/1900')
        END AS PolicyTermToDate,

       

        lob.ClaimLOB,
        lob.CoverageShortDescription PrimCovDescr,
        c.OasisClaimState,
    ct.EndDate  ClaimTypeChangeDate,

    (select dc.StartDate 
    from [dbo].[vDimClaimStatus] dc
    where dc.[ClaimStatus]='Reopened' 
     and dc.ClaimFK=cs.ClaimFK
    )   ClaimReopenDate

      FROM dbo.vFactClaimActivity f
      INNER JOIN dbo.vDimCompany cmp
        ON f.Companyfk = cmp.CompanyPK
      INNER JOIN dbo.vDimClaimType ct
        ON ct.ClaimTypePK = f.ClaimTypeFK
      INNER JOIN dbo.vDimClaim c
        ON c.ClaimPK = f.ClaimFK
      INNER JOIN dbo.vDimClaimStatus cs
        ON f.ClaimStatusFK = cs.ClaimStatusPK
      INNER JOIN dbo.vDimDate sd
        ON sd.DatePK = f.ClaimSnapshotDateFK
      INNER JOIN dbo.vDimClaimConsultant da
        ON da.ClaimFK = c.ClaimPK
        AND da.ClaimConsultant <> 'Not Available'
        AND (da.EffectiveToDate = '1/1/2999'
        OR da.EffectiveToDate = '1/1/3000')
        AND da.IsCurrent = 1
      LEFT JOIN dbo.vDimCourtcase cc
        ON cc.ClaimFK = c.ClaimPK
        AND cc.IsCurrent = 1
      INNER JOIN dbo.vDimInsured di
        ON di.InsuredPK = f.InsuredFK
        AND di.InsuredIsCurrent = 1

      INNER JOIN dbo.vDimPolicy dp
        ON f.PolicyFK = dp.PolicyPK
    INNER JOIN dbo.vDimCoverage cov 
      ON cov.CoveragePK=f.CoverageFK 
      INNER JOIN dbo.vDimClaimant p
        ON f.ClaimantFK = p.ClaimantPK

      INNER JOIN dbo.vDimDate ptfd
        ON ptfd.DatePK = f.PolicyTermFromDateFK
      INNER JOIN dbo.vDimDate pttd
        ON pttd.DatePK = f.PolicyTermToDateFK
      INNER JOIN dbo.vDimLOB lob
        ON f.LOBFK = lob.LOBPK
        AND lob.ClaimLOB <> 'General Liability'
      
    WHERE cs.ClaimStatus IN ('Open', 'Reopened')
       AND not (ct.ClaimType  like '%Claim%' or ct.ClaimType  like '%Suit%')
      AND (CurrentClaimType like '%Claim%' or CurrentClaimType like '%Suit%')
      AND ct.[EndDate] between @startdate and @enddate
      AND [ClaimType]<>[CurrentClaimType]

    )   x

    group by   
    Casefile,
    PolicyNumber,
    InsuredName,
    InsuredNameFrmt,
    OasisClaimState,
    InsuredClaimAddress1, 
    InsuredClaimAddress2, 
    InsuredCity, 
    InsuredState, 
    InsuredZip, 
    InsuredDOB,
    ClaimantName, 
    ClaimantClaimAddress1, 
    ClaimantClaimAddress2, 
    ClaimantCity, 
    ClaimantState, 
    ClaimantZip, 
    ClaimantDOB, 
    ClaimantGender,
           
    CurrentClaimStatus,
    ReportDate,
    LossDate,
    OpenedDate, 
    ConsultantName,
    Region,
    CurrentClaimType,
    LossDescription,
    Policyholder,
    --PolicyTermFromDate,
    PolicyTermToDate,
    ClaimLOB, 
    PrimCovDescr ,
    ClaimReopenDate

    )
      ORDER BY Casefile
      ;
    END


    Monday, March 20, 2017 8:25 PM
  • Hi,

    This is a mouthful of a query! It can be simplified, of course. I'd start with some obvious things such as

    DECLARE @startdate datetime = (select distinct DATEADD(month, DATEDIFF(month, 0, sd.Date), 0) 
      FROM dbo.vFactClaimActivity f  
      INNER JOIN dbo.vDimDate sd on sd.DatePK = f.ClaimSnapshotDateFK 
      WHERE sd.MonthSort = @AsOfYearMonth)
    
      DECLARE @enddate datetime = (select distinct DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,sd.Date)+1,0))
      FROM dbo.vFactClaimActivity f  
      INNER JOIN dbo.vDimDate sd on sd.DatePK = f.ClaimSnapshotDateFK 
      WHERE sd.MonthSort = @AsOfYearMonth);
    
    
    declare @StartDate datetime, @EndDate datetime;
    
    select @StartDate = dateadd(month, datediff(month, '19000101', sd.Date), '19000101')
    
    FROM dbo.vFactClaimActivity f  
      INNER JOIN dbo.vDimDate sd on sd.DatePK = f.ClaimSnapshotDateFK 
      WHERE sd.MonthSort = @AsOfYearMonth;
    
    set @EndDate = dateadd(month, 1, @StartDate);// better to not use dates 1 second before end of month, but rather use open dates query instead
    
    
    
    

    and other optimizations as I suggested. Using DISTINCT also looks suspicious and most likely not needed.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, March 20, 2017 8:42 PM
    Moderator
  •   DECLARE @startdate datetime = (select distinct DATEADD(month, DATEDIFF(month, 0, sd.Date), 0) 
      FROM dbo.vFactClaimActivity f  
      INNER JOIN dbo.vDimDate sd on sd.DatePK = f.ClaimSnapshotDateFK 
      WHERE sd.MonthSort = @AsOfYearMonth)


    This is already a subquery, which may be returns more then one result in some cases (I don't know your base data & parameter value) and causes the error. Depending on business logic you could use TOP 1 instead of a DISTINCT to ensure to get only one value.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Tuesday, March 21, 2017 4:25 AM