SQL Server Performance Forum – Threads Archive

Conditional statement(CASE or IIF)in Select clause

I am attempting to create a view and would like to use a conditional statement such as CASE in the select clause. However, I receive an error that the field alias ‘ActivityDate’ has to be in the group by but when I place the field name in the group by clause, I receive an error ‘invalid column name’. Please let me know if using CASE in a SELECT clause is possible or if I should instead create a stored procedure in order to use CASE. THANKS. SELECT STATIC_HEADER_DATA.assoc_tso_id, STATIC_HEADER_DATA.area_id,
STATIC_HEADER_DATA.mgr_tso_id, STATIC_HEADER_DATA.sup_tso_id, STATIC_HEADER_DATA.vp_tso_id,
STATIC_HEADER_DATA.dir_tso_id,STATIC_HEADER_DATA.qa_rep_tso_id, STATIC_HEADER_DATA.eval_id,
QUESTION_DATA.code,sum(cast([question_data].[answer_value] as bigint)) AS Response,
Sum(1) AS QuestionCnt,
CASE WHEN STATIC_HEADER_DATA.qa_date < (GetDate()-60+1) THEN Month(STATIC_HEADER_DATA.qa_date) + ‘/01/’ + Year(STATIC_HEADER_DATA.qa_date)
ELSE qa_date END AS ActivityDate
FROM QUESTION_DATA INNER JOIN STATIC_HEADER_DATA ON QUESTION_DATA.tx_id = STATIC_HEADER_DATA.tx_id
WHERE (((QUESTION_DATA.score_value_ind)=’s’) AND ((QUESTION_DATA.answer_value)<>’-1′) AND
((STATIC_HEADER_DATA.tx_status)=’s’))
GROUP BY STATIC_HEADER_DATA.assoc_tso_id, STATIC_HEADER_DATA.area_id,
STATIC_HEADER_DATA.mgr_tso_id, STATIC_HEADER_DATA.sup_tso_id, STATIC_HEADER_DATA.vp_tso_id,
STATIC_HEADER_DATA.dir_tso_id, STATIC_HEADER_DATA.qa_rep_tso_id, STATIC_HEADER_DATA.eval_id,
Question_Data.code, ActivityDate

---

Hi ya, You’d need to put the case into the group by clause, rather than the alias SELECT STATIC_HEADER_DATA.assoc_tso_id, STATIC_HEADER_DATA.area_id,
STATIC_HEADER_DATA.mgr_tso_id, STATIC_HEADER_DATA.sup_tso_id, STATIC_HEADER_DATA.vp_tso_id,
STATIC_HEADER_DATA.dir_tso_id,STATIC_HEADER_DATA.qa_rep_tso_id, STATIC_HEADER_DATA.eval_id,
QUESTION_DATA.code,sum(cast([question_data].[answer_value] as bigint)) AS Response,
Sum(1) AS QuestionCnt,
CASE WHEN STATIC_HEADER_DATA.qa_date < (GetDate()-60+1) THEN Month(STATIC_HEADER_DATA.qa_date) + ‘/01/’ + Year(STATIC_HEADER_DATA.qa_date)
ELSE qa_date END AS ActivityDate
FROM QUESTION_DATA INNER JOIN STATIC_HEADER_DATA ON QUESTION_DATA.tx_id = STATIC_HEADER_DATA.tx_id
WHERE (((QUESTION_DATA.score_value_ind)=’s’) AND ((QUESTION_DATA.answer_value)<>’-1′) AND
((STATIC_HEADER_DATA.tx_status)=’s’))
GROUP BY STATIC_HEADER_DATA.assoc_tso_id, STATIC_HEADER_DATA.area_id,
STATIC_HEADER_DATA.mgr_tso_id, STATIC_HEADER_DATA.sup_tso_id, STATIC_HEADER_DATA.vp_tso_id,
STATIC_HEADER_DATA.dir_tso_id, STATIC_HEADER_DATA.qa_rep_tso_id, STATIC_HEADER_DATA.eval_id,
Question_Data.code, CASE WHEN STATIC_HEADER_DATA.qa_date < (GetDate()-60+1) THEN Month(STATIC_HEADER_DATA.qa_date) + ‘/01/’ + Year(STATIC_HEADER_DATA.qa_date)
ELSE qa_date END Cheers
Twan

---

]]>