Issue with group by while using case with aggregate functions-CodePudding

I need your advise to add group by for below piece of code. I tried couple of things but it is giving not a group by function error to me.

Select rec_id,
Case when is_eff=1 and min(date_dt) > dt1 and min(date_dt) <= (dt1   90)
Then 'Yes'
Case when is_eff <> 1 and impl_dt is not null and min(impl_dt) > dt1 and min(impl_dt) <= (dt1 90)
Then 'Yes'
Else 'No'
End as column
From Table
Group by rec_id

Error: Not a group by expression

Any suggestions on this please.

CodePudding user response：

You type CASE WHEN THEN, CASE WHEN THEN,...

Syntax is different than what I found on oracle docs: https://www.oracletutorial.com/oracle-basics/oracle-case/

Being CASE WHEN THEN, WHEN THEN, WHEN THEN,...

Also, fields you are not using aggregate functions on in your CASE should also be included in your group by clause.

Select rec_id,
Case when is_eff=1 and min(date_dt) > dt1 and min(date_dt) <= (dt1   90)
Then 'Yes'
when is_eff <> 1 and impl_dt is not null and min(impl_dt) > dt1 and min(impl_dt) <= (dt1 90)
Then 'Yes'
Else 'No'
End as column
From Table
Group by rec_id,dt1,is_eff,impl_dt