Subquery returning more than one result?

I run this query:

SELECT 
        CAST(PolicyNumber AS varchar(50)) POLICYNUMBER,
        CAST(EFFECTIVEDATE AS DATE) EFFECTIVEDATE ,
        MAX(CAST(EXPIRATIONDATE AS DATE)) EXPIRATIONDATE,
        CAST(ISSUINGCOMPANY AS varchar(4)) AS INSURER,
        CAST(INSUREDLEGALNAME AS varchar(250)) AS INSUREDNAME,
        PROGRAM,
        MAX(CAST(CANCELDATE AS DATE)) AS CANCELDATE,        
        CAST(INSUREDADDR1 AS varchar(500)) AS INSUREDADDR1,
        CAST(INSUREDADDR2 AS varchar(500)) AS INSUREDADDR2,
        CAST(INSUREDCITY AS varchar(500)) AS INSUREDCITY,
        CAST(INSUREDSTATE AS varchar(20)) AS INSUREDSTATE,
        CAST(INSUREDZIP AS varchar(20)) AS INSUREDZIP   ,
        (SELECT SourceId FROM STG.[Source] WHERE [SystemName] = 'CRU') AS SOURCESYSTEMID    
        ,(Select  ProgramInclude = CASE WHEN ProgramInClude = 'Y' THEN 1 Else 0 END  
         From STG.LPT L INNER Join SRC.Policy C ON C.Program = L.ProgramCode  ) AS LPT
     FROM SRC.Policy  

     GROUP BY POLICYNUMBER,
     EFFECTIVEDATE,
     ISSUINGCOMPANY,
     INSUREDLEGALNAME,
     PROGRAM,
     INSUREDADDR1,
     INSUREDADDR2,
     INSUREDCITY,
     INSUREDSTATE,
     INSUREDZIP

And get this result:

Msg 512, Level 16, State 1, Line 16 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.

Any ideas?

Your subqueries should return only one row, as they are part of your resultset. what you can do is:

• either make sure that WHERE clause makes sure that only one row is returned
• Add TOP 1 clause to make sure only one row is returned. When you add TOP clause, add ORDER BY clause to ensure that you are deterministically getting data, instead of random one. as I don't have your schema, I have not added ORDER BY for the subquery
• Also you don't need JOIN one more time in subquery. You can reference the outer Policy table inside the subquery

    SELECT 
            CAST(PolicyNumber AS varchar(50)) POLICYNUMBER,
            CAST(EFFECTIVEDATE AS DATE) EFFECTIVEDATE ,
            MAX(CAST(EXPIRATIONDATE AS DATE)) EXPIRATIONDATE,
            CAST(ISSUINGCOMPANY AS varchar(4)) AS INSURER,
            CAST(INSUREDLEGALNAME AS varchar(250)) AS INSUREDNAME,
            PROGRAM,
            MAX(CAST(CANCELDATE AS DATE)) AS CANCELDATE,        
            CAST(INSUREDADDR1 AS varchar(500)) AS INSUREDADDR1,
            CAST(INSUREDADDR2 AS varchar(500)) AS INSUREDADDR2,
            CAST(INSUREDCITY AS varchar(500)) AS INSUREDCITY,
            CAST(INSUREDSTATE AS varchar(20)) AS INSUREDSTATE,
            CAST(INSUREDZIP AS varchar(20)) AS INSUREDZIP   ,
            (SELECT TOP 1 SourceId FROM STG.[Source] WHERE [SystemName] = 'CRU') AS SOURCESYSTEMID    
            ,(Select TOP 1 ProgramInclude = CASE WHEN ProgramInClude = 'Y' THEN 1 Else 0 END  
             From STG.LPT L WHERE L.ProgramCode = op.Program  ) AS LPT
         FROM SRC.Policy  AS op

         GROUP BY POLICYNUMBER,
         EFFECTIVEDATE,
         ISSUINGCOMPANY,
         INSUREDLEGALNAME,
         PROGRAM,
         INSUREDADDR1,
         INSUREDADDR2,
         INSUREDCITY,
         INSUREDSTATE,
         INSUREDZIP

the above one, you have to make sure that your TOP clause returns the right one. Otherwise, I would suggest one more option of avoiding subqueries and using JOINS, as I have mentioned below:

DECLARE @SourceId INT 
-- Use either of the below approaches for populating @sourceId

SET @SourceId = (SELECT SourceId FROM STG.[Source] WHERE [SystemName] = 'CRU' ) -- Add additional WHERE clause to pickup right value or

SET @SourceId = (SELECT TOP 1 SourceId FROM STG.[Source] WHERE [SystemName] = 'CRU' ) -- Add additional ORDER BY clause. 

    SELECT 
            CAST(PolicyNumber AS varchar(50)) POLICYNUMBER,
            CAST(EFFECTIVEDATE AS DATE) EFFECTIVEDATE ,
            MAX(CAST(EXPIRATIONDATE AS DATE)) EXPIRATIONDATE,
            CAST(ISSUINGCOMPANY AS varchar(4)) AS INSURER,
            CAST(INSUREDLEGALNAME AS varchar(250)) AS INSUREDNAME,
            PROGRAM,
            MAX(CAST(CANCELDATE AS DATE)) AS CANCELDATE,        
            CAST(INSUREDADDR1 AS varchar(500)) AS INSUREDADDR1,
            CAST(INSUREDADDR2 AS varchar(500)) AS INSUREDADDR2,
            CAST(INSUREDCITY AS varchar(500)) AS INSUREDCITY,
            CAST(INSUREDSTATE AS varchar(20)) AS INSUREDSTATE,
            CAST(INSUREDZIP AS varchar(20)) AS INSUREDZIP   ,
            @SourceId AS SOURCESYSTEMID,    
            CASE WHEN ProgramInClude = 'Y' THEN 1 Else 0 END AS LPT
         FROM SRC.Policy  AS op
         INNER JOIN STG.LPT L ON 
         L.ProgramCode = op.Program
         GROUP BY POLICYNUMBER,
         EFFECTIVEDATE,
         ISSUINGCOMPANY,
         INSUREDLEGALNAME,
         PROGRAM,
         INSUREDADDR1,
         INSUREDADDR2,
         INSUREDCITY,
         INSUREDSTATE,
         INSUREDZIP,
         @SourceId AS SOURCESYSTEMID,
         CASE WHEN ProgramInClude = 'Y' THEN 1 Else 0 END