The syntax for
ROW_NUMBER() OVER (PARTITION BY a.member_id ORDER BY trunc(greatest(to_date(b.eff_dt), to_date(b2.eff_dt), to_date(c. eff_dt), to_date(g.plan_eff_dt)), 'MM')) grouping
works, but if I put it in a CASE, it has error. What is the correct syntax?
case
when a.member_id=lag(a.member_id) and a.date1>lag(a.date1)+1 then ROW_NUMBER() OVER (PARTITION BY a.member_id ORDER BY trunc(greatest(to_date(b.eff_dt), to_date(b2.eff_dt), to_date(c. eff_dt), to_date(g.plan_eff_dt)), 'MM'))
else 1
end grouping
The error is
ORA-30484: missing window specification for this function
30484. 00000 - "missing window specification for this function"
*Cause: All window functions should be followed by window specification,
like <function>(<argument list>) OVER (<window specification>)
Thanks.