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

Question

I am having this error message. I have tried many other ways but can't resolve the issue. I would be really grateful if someone could look at this :

;WITH progression
AS
(
SELECT
    w.CustomerID,
    CASE WHEN SUM(CASE WHEN w.workflowoutcomeid = 8 THEN 1 ELSE 0 END)  > 0 THEN 'Yes' ELSE 'No' END AS [PG01],
    CASE WHEN SUM(CASE WHEN w.workflowoutcomeid = 11 THEN 1 ELSE 0 END) > 0 THEN 'Yes' ELSE 'No' END AS [PG03],
    CASE WHEN SUM(CASE WHEN w.workflowoutcomeid = 14 THEN 1 ELSE 0 END) > 0 THEN 'Yes' ELSE 'No' END AS [PG04],
    CASE WHEN SUM(CASE WHEN w.workflowoutcomeid = 17 THEN 1 ELSE 0 END) > 0 THEN 'Yes' ELSE 'No' END AS [PG05],
    CASE WHEN SUM(CASE WHEN w.workflowoutcomeid = 9 THEN 1 ELSE 0 END)  > 0 THEN 'Yes' ELSE 'No' END AS [in_work_review]
FROM
    ABC.dbo.tblWorkflow w
WHERE
    w.Deleted IS NULL
    AND
    w.workflowqueueid = 4
    AND
    w.workflowoutcomeid IN (8, 11, 14, 17, 9)
GROUP BY
    w.CustomerID
),

contact_data
AS
(
SELECT
    CustomerID, [Email], [Mobile], [Home]
FROM
    (
    SELECT 
        CustomerID,  
        CASE
            WHEN ContactTypeID = 1 THEN 'Home'
            WHEN ContactTypeID = 2 THEN 'Mobile'
            WHEN ContactTypeID = 5 THEN 'Email' 
        END AS ContactTypeDescription,
        ContactValue
    FROM ABC.dbo.tblCustomerContact
    WHERE ContactTypeID IN (1,2,5)
    ) base
PIVOT   
    (
    MAX(ContactValue) FOR ContactTypeDescription IN ([Home],[Mobile],[Email])
    ) pvt
),

appointment
AS
(
SELECT 
    n.[Key] AS CustomerID,
    MAX(CASE WHEN n.AppointmentStatusID IN (2,4) THEN n.ActionDate ELSE NULL END) AS [Last_Completed_Attended_Appointment],
    MAX(CASE WHEN n.AppointmentStatusID = 1 THEN n.ActionDate ELSE NULL END) AS [Next Pending Appointment]
FROM ABC.dbo.tblNote n
WHERE n.AppointmentTypeID = 6 AND n.AppointmentStatusID IN (1,2,4)
GROUP BY n.[Key]
)

SELECT
    m.Firstname + ' ' + m.Lastname AS [Manager],
    u.Firstname + ' ' + u.Lastname AS [Adviser],
    c.CustomerID,
    c.GivenName + ' ' + c.FamilyName AS [Customer Name],
    g.Gender,
    e.EthnicityName AS Ethnicity,
    com.CompanyName,
    NULL AS DeliverySite,
    d.[Name] AS District,
    css.ServiceStatus,
    contact_data.Email,
    uad.Line1 AS AddressLine1,
    uad.Line2 AS AddressLine2,
    uad.Line3 AS AddressLine3,
    uad.Postcode,
    uad.AlternativePostcode,
    uad.Town,
    ISNULL(contact_data.mobile, contact_data.home) AS Phone,
    c.StartDate,

    
    (SELECT MIN(wh.MGCReviewerModifiedDate) AS [ST01_Approved_Date]
        FROM ABC.dbo.tblWorkflow w 
        INNER JOIN ABC.dbo.tblWorkflowHistory wh 
        ON w.WorkflowID = wh.WorkflowID 
        WHERE wh.WorkflowQueueID = 4 AND wh.MGCReviewerModifiedDate IS NOT NULL
        AND wh.Deleted IS NULL AND w.WorkflowOutcomeID = 1 AND w.WorkflowQueueID = 4
        AND w.Deleted IS NULL AND w.CustomerID = c.CustomerID
        GROUP BY w.WorkflowID, w.CustomerID) AS ST01_Approved_Date,

    qual.LearningAimTitle,
    qual.LearningAimCompletionStatus,
    qual.LearningAimOutcomeStatus,
    mwp.WorkExperienceTitle ,
    mwp.WorkExperienceStartDate,
    mwp.WorkExperienceEndDate,
    mwp.learningaimcompletionstatus as [Work_Ex_CompletionStatus],
    mwp.learningaimoutcomestatus as [Work_Ex_OutcomeStatus],
    p.PG01 AS [PG01_Start(Job Start Approved/Claimed ?)],
    p.in_work_review AS [PG01_In_work_review(Job Outcome Approved/Claimed ?)],
    p.PG03 AS [PG03 – Education Approved / Claimed?],
    p.PG04 AS [PG04 – Apprenticeship Approved / Claimed?],
    p.PG05 AS [PG05– Apprenticeship Approved / Claimed?],
    a.Last_Completed_Attended_Appointment,
    a.[Next Pending Appointment],
    FLOOR(DATEDIFF(day, c.DateOfBirth, GETDATE()) / 365.25) as CustomerAge
FROM ABC.dbo.tblCustomer c
LEFT JOIN ABC.dbo.tblUser u ON c.OwnerID = u.UserID
LEFT JOIN ABC.dbo.tblUser m ON u.ManagerID = m.UserID
LEFT JOIN ABC.dbo.tlkpGender g ON c.GenderID = g.GenderID
LEFT JOIN ABC.dbo.tlkpEthnicity e ON c.EthnicityID = e.EthnicityID
LEFT JOIN ABC.dbo.tblCompany com ON c.CompanyId = com.[CompanyID]
LEFT JOIN ABC.dbo.tblCustomerAddress cad ON c.CustomerID = cad.CustomerID AND cad.EffectiveTo IS NULL
LEFT JOIN ABC.dbo.tblUKAddress uad ON uad.UKaddressID = cad.UKaddressID
LEFT JOIN PostcodeESyNCS.dbo.tblPostcode po ON uad.Postcode = po.Postcode
LEFT JOIN PostcodeESyNCS.dbo.tlkpDistrict d ON po.DistrictId = d.DistrictId
LEFT JOIN ABC.dbo.tlkpCustomerServiceStatus css ON c.CustomerServiceStatusID = css.CustomerServiceStatusID
LEFT JOIN progression p ON c.CustomerID = p.CustomerID
LEFT JOIN contact_data ON c.CustomerID = contact_data.CustomerID

OUTER APPLY
    (
    SELECT TOP 1 
        l.learningaimid,
        aim.LearningAimTitle,
        aim.isfullqualification,
        st.LearningAimCompletionStatus,
        aos.LearningAimOutcomeStatus 
    FROM ABC.dbo.tbllearning l 
    LEFT JOIN ABC.dbo.tlkplearningaim aim on aim.learningaimid = l.learningaimid 
    LEFT JOIN ABC.dbo.tlkplearningaimcompletionstatus st on st.learningaimcompletionstatusid = l.learningaimcompletionstatusid
    LEFT JOIN ABC.dbo.tlkpLearningAimOutcomeStatus aos on aos.learningaimoutcomestatusid = l.learningaimoutcomestatusid
    WHERE aim.learningaimtypeid = 1 AND l.customerid = c.customerid 
    ORDER BY l.StartDate DESC
    ) qual

OUTER APPLY 
    ( 
    SELECT TOP 1 
        l.learningaimid,
        REPLACE(aim.LearningAimTitle,',',' ') as WorkExperienceTitle,
        aim.isfullqualification,
        st.LearningAimCompletionStatus,
        outcome.LearningAimOutcomeStatus,
        l.StartDate as WorkExperienceStartDate,
        l.ActualEndDate as WorkExperienceEndDate
        FROM ABC.dbo.tbllearning l 
        LEFT JOIN ABC.dbo.tlkplearningaim aim ON aim.learningaimid=l.learningaimid 
        LEFT JOIN ABC.dbo.tlkplearningaimcompletionstatus st ON st.learningaimcompletionstatusid=l.learningaimcompletionstatusid
        LEFT JOIN ABC.dbo.tlkpLearningAimOutcomeStatus outcome ON outcome.learningaimoutcomestatusid= l.learningaimoutcomestatusid
        WHERE aim.LearningAimID IN (SELECT la.LearningAimID
                                            FROM ABC.dbo.tlkpLearningAim la 
                                            WHERE la.LearningAimTypeID = 2 
                                            and la.LearningAimTitle like '%Work%')              
              AND l.customerid = c.customerid 
            ORDER BY StartDate DESC      
        ) AS mwp
LEFT JOIN appointment a ON c.CustomerID = a.CustomerID
WHERE
    c.Deleted IS NULL

I am getting the following error: Msg 512, Level 16, State 1, Line 2 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. Warning: Null value is eliminated by an aggregate or other SET operation.

Answer 1

The only place where I see that this could be happening is the definition of ST01_Approved_Date:

(SELECT MIN(wh.MGCReviewerModifiedDate) AS [ST01_Approved_Date]
 FROM ABC.dbo.tblWorkflow w JOIN
      ABC.dbo.tblWorkflowHistory wh 
      ON w.WorkflowID = wh.WorkflowID 
 WHERE wh.WorkflowQueueID = 4 AND wh.MGCReviewerModifiedDate IS NOT NULL AND
       wh.Deleted IS NULL AND w.WorkflowOutcomeID = 1 AND w.WorkflowQueueID = 4 AND
      w.Deleted IS NULL AND w.CustomerID = c.CustomerID
  GROUP BY w.WorkflowID, w.CustomerID
 ) AS ST01_Approved_Date,

A correlated subquery should not contain GROUP By for exactly this reason. I don't know what the logic would be, but there should probably be a condition on w.WorkFlowId to the outer query, something like:

(SELECT MIN(wh.MGCReviewerModifiedDate) AS [ST01_Approved_Date]
 FROM ABC.dbo.tblWorkflow w JOIN
      ABC.dbo.tblWorkflowHistory wh 
      ON w.WorkflowID = wh.WorkflowID 
 WHERE wh.WorkflowQueueID = 4 AND wh.MGCReviewerModifiedDate IS NOT NULL AND
       wh.Deleted IS NULL AND w.WorkflowOutcomeID = 1 AND w.WorkflowQueueID = 4 AND
       w.Deleted IS NULL AND w.CustomerID = c.CustomerID AND
       w.workflowID = <outer query reference>.WorkflowId
 ) AS ST01_Approved_Date,