Failed [5484 : HY000]No ordered analytical function allowed in search condition for a joined table

Analytics

Failed [5484 : HY000]No ordered analytical function allowed in search condition for a joined table

Hi

From the PHRM_TBL.EFF_DT,  i want to derive END_DT (next (Max (EFF_DT)-1) and check if CLM_TBL.FILLD_DT  is between EFF_DT and END_DT .

I want to derive END_DT as below and compare with Filld_dt from CLM_TBL.

EFF_DT             END_DT 

2015-02-10       2015-02-24

2015-02-25       2015-02-28

2015-03-01       9999-12-31

I am able to derive the END_DT but not able to compare with Filld_dt.

Used the following query to derive END_DT 

Sel EFF_DT, Max (EFF_DT) over (order by EFF_DT rows between current row and 1 following) -1 as END_DT from PHRM_TBL group by EFF_DT.

When i try to join and compare with CLM_TBL.FILLD_DT i get the error

SEL FILLD_DT FROM CLM_TBL C

INNER JOIN

PHRM_TBL P ON

C.FILLD_DT BETWEEN P.EFF_DT AND Max (P.EFF_DT ) over (order by P.EFF_DT rows between current row and 1 following )-1;

Can someone help me in query for retrieving EFF_DT  next row value minus with 1 and compare with Filld_dt.

Thanks

2 REPLIES
Senior Apprentice

Re: Failed [5484 : HY000]No ordered analytical function allowed in search condition for a joined table

OLAP-functions are calculated after where/join/group by/having, so you have move it to a Derifed Table:

SEL FILLD_DT FROM CLM_TBL C
INNER JOIN
(
Sel EFF_DT,
Max (EFF_DT)
over (order by EFF_DT
rows between current row and 1 following) -1 as END_DT
from PHRM_TBL group by EFF_DT
) P
ON C.FILLD_DT BETWEEN P.EFF_DT AND P.END_DT;

Re: Failed [5484 : HY000]No ordered analytical function allowed in search condition for a joined table

Thanks a lot..above query worked..:)