Subquery returned more than 1 value. This is not permitted when

Question

I want to a statement which would return 1 if diference between two dates (MaturityDate, PaymentDate) is greater than 1 month. Maturity Date is a due date to which a client should made a payment.

I've tried:

CASE WHEN DATEDIFF(MONTH, 
            (SELECT Date FROM dim.RepaymentSchedule rs JOIN dim.Calendar cal ON cal.DateID = rs.MaturityDateID), 
            (SELECT Date FROM dim.RepaymentSchedule rs JOIN dim.Calendar cal ON cal.DateID = rs.PaymentDateID)) 
                BETWEEN 1 AND 2 THEN 1 ELSE 0 END AS OneMonthDelay

which is analogue to

CASE WHEN DATEDIFF(MONTH, 2017-06-30, 2017-08-01) BETWEEN 1 AND 2 THEN 1 ELSE 0 END AS OneMonthDelay

,unfortunatelly returning

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Any help would be appreciated.

EDIT: whole query

SELECT
    pt.ProductType
    ,sr.SalesRegionName
    ,ca.CreditAdvisorID
    ,cal.CalendarYearMonth
    ,CASE WHEN DATEDIFF(MONTH, 
        (SELECT Date FROM dim.RepaymentSchedule rs JOIN dim.Calendar cal ON cal.DateID = rs.MaturityDateID), 
        (SELECT Date FROM dim.RepaymentSchedule rs JOIN dim.Calendar cal ON cal.DateID = rs.PaymentDateID)) 
            BETWEEN 1 AND 2 THEN 1 ELSE 0 END AS OneMonthDelay -- >1 <2 not =>1 =<2
    ,CASE WHEN DATEDIFF(MONTH, 
        (SELECT Date FROM dim.RepaymentSchedule rs JOIN dim.Calendar cal ON cal.DateID = rs.MaturityDateID), 
        (SELECT Date FROM dim.RepaymentSchedule rs JOIN dim.Calendar cal ON cal.DateID = rs.PaymentDateID))
            BETWEEN 2 AND 3 THEN 1 ELSE 0 END AS TwoMonthsDelay
    ,RANK() OVER (PARTITION BY c.ApplicationID ORDER BY rs.RepaymentNumber, rs.Amount) AS RankID
INTO #Frauds
FROM    
    dim.Contract c
    JOIN dim.Application a          ON c.ApplicationID = a.ApplicationID
    JOIN dim.Calendar cal           ON a.ApplicationDateID = cal.DateId 
    JOIN dim.CreditAdvisor ca       ON a.OriginalCreditAdvisorID = ca.CreditAdvisorId   
    JOIN dim.SalesRegion sr         ON ca.SalesRegionID = sr.SalesRegionId  
    JOIN dim.ProductType pt         ON a.ProductTypeID  = pt.ProductTypeID  
    JOIN dim.RepaymentSchedule rs   ON  c.ContractID = rs.ContractID
WHERE
    ((cal.CalendarYear >= @YearId) AND (rs.MaturityDateID < @DateId)) -- Since given year to this date
    AND ((rs.PaymentDateID = 19000101) OR (rs.PaymentDateID > rs.MaturityDateID))
    AND rs.Amount   <> 0

Either "SELECT Date FROM dim.RepaymentSchedule rs JOIN dim.Calendar cal ON cal.DateID = rs.MaturityDateID" OR "SELECT Date FROM dim.RepaymentSchedule rs JOIN dim.Calendar cal ON cal.DateID = rs.PaymentDateID" returning more than one record — Sujith, Aug 14 '17 at 08:49
I think in your query's WHERE condition, "AND ((rs.PaymentDateID = 19000101) OR (rs.PaymentDateID > rs.MaturityDateID))" . It should/may be AND not OR . re-check once. Otherwise simply remove "OR (rs.PaymentDateID > rs.MaturityDateID)" and run the query, to check if that's the issue — Sujith, Aug 14 '17 at 08:52
Then the only way is to remove the Case from your query, just include those Ids in your select and run. See if you are getting multiple records with the mentioned join and where condition — Sujith, Aug 14 '17 at 08:58

HappieFeet · Answer 1 · 2017-08-14T13:47:43.197

Can you try removing the joins in your case statements and rather join on Calender table with all the other tables below?

SELECT
    pt.ProductType
    ,sr.SalesRegionName
    ,ca.CreditAdvisorID
    ,cal.CalendarYearMonth
    ,CASE WHEN DATEDIFF(MONTH, 
       cal.DATE,
        cal1.DATE)
            BETWEEN 1 AND 2 THEN 1 ELSE 0 END AS OneMonthDelay -- >1 <2 not =>1 =<2
    ,CASE WHEN DATEDIFF(MONTH, 
       CASE WHEN DATEDIFF(MONTH, 
       cal.DATE,
        cal1.DATE)
            BETWEEN 2 AND 3 THEN 1 ELSE 0 END AS TwoMonthsDelay
    ,RANK() OVER (PARTITION BY c.ApplicationID ORDER BY rs.RepaymentNumber, rs.Amount) AS RankID
INTO #Frauds
FROM    
    dim.Contract c
    JOIN dim.Application a          ON c.ApplicationID = a.ApplicationID
    JOIN dim.Calendar cal           ON a.ApplicationDateID = cal.DateId 
    JOIN dim.CreditAdvisor ca       ON a.OriginalCreditAdvisorID = ca.CreditAdvisorId   
    JOIN dim.SalesRegion sr         ON ca.SalesRegionID = sr.SalesRegionId  
    JOIN dim.ProductType pt         ON a.ProductTypeID  = pt.ProductTypeID  
    JOIN dim.RepaymentSchedule rs   ON  c.ContractID = rs.ContractID
    JOIN dim.Calender cal1 ON rs.MaturityDateID = cal1.DATEID
    JOIN dim.Calender cal2 ON rs.PaymentDateID = cal2.DATEID
WHERE
    ((cal.CalendarYear >= @YearId) AND (rs.MaturityDateID < @DateId)) -- Since given year to this date
    AND ((rs.PaymentDateID = 19000101) OR (rs.PaymentDateID > rs.MaturityDateID))
    AND rs.Amount   <> 0

score 0 · Answer 2 · answered Aug 14 '17 at 11:16

Although putting the calendar table in the FROM seems to make the most sense, your problem is the repetition of the rs table in the subqueries. You can try:

,CASE WHEN DATEDIFF(MONTH, 
    (SELECT Date FROM dim.Calendar cal WHERE cal.DateID = rs.MaturityDateID), 
    (SELECT Date FROM dim.Calendar cal WHERE cal.DateID = rs.PaymentDateID)) 
        BETWEEN 1 AND 2 THEN 1 ELSE 0 END AS OneMonthDelay -- >1 <2 not =>1 =<2
,CASE WHEN DATEDIFF(MONTH, 
    (SELECT Date FROM dim.Calendar cal WHERE cal.DateID = rs.MaturityDateID), 
    (SELECT Date FROM dim.Calendar cal WHERE cal.DateID = rs.PaymentDateID))
        BETWEEN 2 AND 3 THEN 1 ELSE 0 END AS TwoMonthsDelay

Subquery returned more than 1 value. This is not permitted when

2 Answers2