Can you have a double case in one line of code in SQL?

Question

I have a sql query that i need to know if I can do or if cant be done . when i run the code i get an error that Column 'tblUBMInsuranceShare.decInsuranceShare' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
SELECT
'Cost of Insurance' AS strType,

SUM(CASE WHEN dtReport = @EndDate THEN ROUND(mnyInsurance,2)  ELSE 0 END)  *(CASE WHEN decInsuranceShare IS NOT NULL THEN 1-decInsuranceShare ELSE 0.60 END) AS intDT,  
SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfMonth AND @EndDate THEN ROUND(mnyInsurance,2) ELSE 0 END) *(CASE WHEN decInsuranceShare IS NOT NULL THEN 1-decInsuranceShare ELSE 0.60 END) AS intMTD,  
SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfQuarter AND @EndDate THEN ROUND(mnyInsurance,2) ELSE 0 END) *(CASE WHEN decInsuranceShare IS NOT NULL THEN 1-decInsuranceShare ELSE 0.60 END) AS intQTD,  
SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfYear AND @EndDate THEN ROUND(mnyInsurance,2) ELSE 0 END) *(CASE WHEN decInsuranceShare IS NOT NULL THEN 1-decInsuranceShare ELSE 0.60 END)AS intYTD,  

Answer 1

This has nothing to do with your case statement. The problem is that you're referencing a column that is not in an aggregate function (SUM, for example) and you are using a GROUP BY clause and it isn't part of that either. Because it is not in either of these it cannot be used in your select statement.

You didn't post the entire query so we'd only be guessing at specifically where in your query things are going wrong. Again, not really related to the use of case statements at all here.

Answer 2

As the Cool dad from Texas says, we don't see your full query. But just guessing, maybe:

SUM(CASE WHEN dtReport = @EndDate THEN ROUND(mnyInsurance,2)  ELSE 0 END)  *(CASE WHEN decInsuranceShare IS NOT NULL THEN 1-decInsuranceShare ELSE 0.60 END) AS intDT,  

should be:

SUM(CASE WHEN dtReport = @EndDate   
         THEN ROUND(mnyInsurance,2)       
         ELSE 0  
    END  *  
    CASE WHEN decInsuranceShare IS NOT NULL   
         THEN 1-decInsuranceShare   
         ELSE 0.60   
    END) AS intDT,  

What I have done beside the reformatting is to change END) *(CASE to read END * CASE. That is, I have removed the left parenthesis which closed the argument list for the SUM() function. The I removed the right paren before CASE, because it is not needed.

Answer 3

Hi @Jannette Jones

i get an error t*hat Column 'tblUBMInsuranceShare.decInsuranceShare' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

The error message has already told you that the Column decInsuranceShare is not contained in either an Aggregate function or the GROUP BY clause.
In fact, if you examine your code closely, you will find that your SUM() only contains the first Case When statement, while the second Case When statement(which is using Column decInsuranceShare as return value) is outside. That's why the error message says Column decInsuranceShare is not contained in either an aggregate function or the GROUP BY clause.

Therefore, one solution is put the second Case When statement inside SUM(), because (3+4+5)*0.6 equals 3*0.6+4*0.6+5*0.6.
Change to SUM((CASE WHEN ... END) *(CASE WHEN ... END)) AS intDT

Ps: For Case When statements, we don't need to add '( )' , because CaseWhen statements always start with 'Case' and end with 'End' which works like '( )'

Best regards,
LiHong