All Forums Analytics
vidya@s 4 posts Joined 03/15
16 Mar 2015
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

dnoeth 4628 posts Joined 11/04
16 Mar 2015

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;

 

Dieter

vidya@s 4 posts Joined 03/15
19 Mar 2015

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

You must sign in to leave a comment.